Dynamic query

Question

Post reply

Dynamic query

peacesells

SSCrazy

Points: 2882
More actions
December 17, 2012 at 4:09 pm

#286233

I am writing a stored procedure with dynamic query to find the record counts from two tables. The table names will be passed as parameters from SSIS -hence the table names will be dynamic. I need to capture the record count into a variable and insert them into a table as well as also pass them back to SSIS for comparison. I can't really get to work as i am not a dynamic sql expert. Any insights will be appreciated. thanx.
CREATE PROCEDURE [dbo].[spDmValidationCheck](
@DVTableName NVARCHAR(101)
,@DataMartTableName NVARCHAR(101)
,@DVTableRecordCount BIGINT OUTPUT
,@DataMartTableRecordCount BIGINT OUTPUT
)
AS
BEGIN
DECLARE @CurrentDateTime DATETIME=GETDATE()
DECLARE @sql NVARCHAR(MAX)='SELECT COUNT (*) FROM '
DECLARE @DmSQL NVARCHAR(MAX)=''
DECLARE @DVSQL NVARCHAR(MAX)=''
SET @DmSQL=@SQL+@DataMartTableName
EXEC @DVTableRecordCount = sp_executesql @DmSQL OUTPUT ---??
INSERT INTO Test.dbo.DmValidation(DVTableName,DataMartTableName,DVTableRecordCount,DataMartTableRecordCount ,LogDateTime, RemarksAction)
VALUES
(@DVTableName,@DataMartTableName,@DVTableRecordCount,@DataMartTableRecordCount,@CurrentDateTime,NULL)
SELECT ISNULL(@DataMartTableRecordCount,0)AS DataMartTableRecordCount,ISNULL(@DVTableRecordCount,0) AS DVTableRecordCount
END

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

toddasd SSCertifiable Points: 6298 More actions · Answer 1

To make what you have work, take a look a this:

CREATE PROCEDURE [dbo].[spDmValidationCheck](

@DVTableName NVARCHAR(101)

,@DataMartTableName NVARCHAR(101)

,@DVTableRecordCount BIGINT OUTPUT

,@DataMartTableRecordCount BIGINT OUTPUT

)

AS

BEGIN

DECLARE @CurrentDateTime DATETIME=GETDATE()

DECLARE @SQL1 NVARCHAR(MAX)=N'SELECT @DVTableRecordCount = COUNT(*) FROM '

DECLARE @SQL2 NVARCHAR(MAX)=N'SELECT @DataMartTableRecordCount = COUNT(*) FROM '

DECLARE @DmSQL NVARCHAR(MAX)=''

DECLARE @DVSQL NVARCHAR(MAX)=''

SET @DmSQL=@SQL1+@DVTableName

EXEC sp_executesql @DmSQL, N'@DVTableRecordCount BIGINT OUTPUT', @DVTableRecordCount OUTPUT

SET @DmSQL=@SQL2+@DataMartTableName

EXEC sp_executesql @DmSQL, N'@DataMartTableRecordCount BIGINT OUTPUT', @DataMartTableRecordCount OUTPUT

INSERT INTO DmValidation(DVTableName, DataMartTableName, DVTableRecordCount, DataMartTableRecordCount, LogDateTime, RemarksAction)

VALUES (@DVTableName,@DataMartTableName,@DVTableRecordCount,@DataMartTableRecordCount,@CurrentDateTime,NULL)

SELECT ISNULL(@DataMartTableRecordCount,0)AS DataMartTableRecordCount,ISNULL(@DVTableRecordCount,0) AS DVTableRecordCount

END

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

toddasd SSCertifiable Points: 6298 More actions · Answer 2

Of course, I'd do this task like below. Be warned though, that I don't have to worry about sql injection in my closed environment.

CREATE PROCEDURE [dbo].[spDmValidationCheckTODD](

@DVTableName NVARCHAR(101)

,@DataMartTableName NVARCHAR(101)

,@debug bit = 0)

AS

DECLARE @sql VARCHAR(MAX)

SET @sql = '

INSERT INTO DmValidation(DVTableName, DataMartTableName, DVTableRecordCount, DataMartTableRecordCount, LogDateTime, RemarksAction)

SELECT

' + '''' + @DVTableName + '''' + ', ' + '''' + @DataMartTableName + '''' + ',

(SELECT COUNT(*) FROM ' + @DVTableName + '),

(SELECT COUNT(*) FROM ' + @DataMartTableName + '),

GETDATE(), ''''

'

IF @debug = 1 PRINT @sql

EXEC(@SQL)

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

Jeff Moden SSC Guru Points: 1004419 More actions · Answer 3

toddasd (12/18/2012)
Of course, I'd do this task like below. Be warned though, that I don't have to worry about sql injection in my closed environment.

Famous last words. 😉

You should always be prepared for SQL Injection because 1) You never know when your "closed environment" is going to be opened up, 2) you never know just how clever hackers are at getting into supposed "closed environments", and 3) a closed environment will not protect you from someone on the inside with a score to settle.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

toddasd SSCertifiable Points: 6298 More actions · Answer 4

Jeff Moden (12/18/2012)
toddasd (12/18/2012)
Of course, I'd do this task like below. Be warned though, that I don't have to worry about sql injection in my closed environment.
Famous last words. 😉
You should always be prepared for SQL Injection because 1) You never know when your "closed environment" is going to be opened up, 2) you never know just how clever hackers are at getting into supposed "closed environments", and 3) a closed environment will not protect you from someone on the inside with a score to settle.

True on all counts. I regretted posting that a minute after clicking submit. Trouble is my entire system is built like this. I guess it's time to go retrofitting.

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.