Dynamic query

  • 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

  • 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.

  • 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.

  • 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)

  • 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.

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

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