3

I've been working on the code below. I'm trying to insert results of a declared variable into a sql statement. I've highlighted the section below that I'm talking about. What am I doing wrong here? I've tried a few different things. Do I need to create a temp table and import from the temp table to the live table?

Thank you for you time and help.

DECLARE @counter INT
    ,@dimtablename NVARCHAR(50)
    ,@dimKey INT
    ,@processdatekey INT
    ,@DimCount INT
    ,@sqlstmt VARCHAR(8000)

WHILE @counter <= (
        SELECT MAX(DimKey)
        FROM dbo.audit_HostDimensionName(NOLOCK)
        )
BEGIN
    SET @dimtablename = (
            SELECT DimName
            FROM dbo.audit_HostDimensionName WITH (NOLOCK)
            WHERE DimKey = @counter
            );
    SET @dimkey = (
            SELECT DimKey
            FROM dbo.audit_JHostDimensionName WITH (NOLOCK)
            WHERE DimKey = @counter
            );
    SET @processdatekey = (
            SELECT cast(convert(VARCHAR, dateadd(d, - 1, getdate()), 112) AS INT) AS datekey
            );
    SET @DimCount = '(SELECT COUNT(*) from''' + @dimtablename + ''' with(nolock)) ';
    SET @sqlstmt = 'INSERT INTO dbo.Audit_HostDimensionTracking(DateKey, DimKey, Row_Count) VALUES (''' + @processdatekey + ',' + @dimkey + ',' + @DimCount + ')'''

    PRINT @sqlstmt

    EXEC (@sqlstmt)

    SET @counter = @counter + 1
END

closed as off-topic by Paul White, RolandoMySQLDBA, Mark Storey-Smith, Shawn Melton, Max Vernon Sep 24 '14 at 15:40

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Paul White, RolandoMySQLDBA, Mark Storey-Smith, Shawn Melton, Max Vernon
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    Is that your actual code? I don't see any initialisation of @counter – Martin Smith Sep 23 '14 at 19:09
  • You're right. I did leave that out. (Had it during initial testing beforehand) I'm going to add it and test the query. Thanks! – Joshua Wells Sep 23 '14 at 19:52
  • 1
    After the fix, I recived an error when converting from nvarchar to int with the select count, but after that was corrected all went well! Thanks again. – Joshua Wells Sep 23 '14 at 19:57
  • So you fixed it? – Max Vernon Sep 23 '14 at 19:59
  • 3
    @MickyT QUOTENAME() is far safer than manually adding square brackets yourself. – Aaron Bertrand Sep 23 '14 at 20:25
4

From the comments it sounds like you already have this fixed but I thought I would put in my 2 cents anyway.

You have a handful of problems based on the code you have listed.

  • @DimCount is declared as an int where you are trying to use it as a string.
  • You are putting quotes around @dimtablename. I understand what you are trying to do there but you should be using quotename() with the default []s instead.
  • In the line where you set @sqlstmt you have ''' at the end of the INSERT part of the string. This is going to put in an extra ' that you don't want. The same is true at the end of the string.
  • Last but not least if you are going to do it this way you will need to convert the int variables to varchar explicitly otherwise it tries to convert the whole thing to an int and fails.

Here is the corrected bit of code

DECLARE @counter INT = 1
    ,@dimtablename NVARCHAR(50) = 'dimtablename'
    ,@dimKey INT = 1
    ,@processdatekey INT = 1
    ,@DimCount VARCHAR(8000)
    ,@sqlstmt VARCHAR(8000)

SET @DimCount = '(SELECT COUNT(*) from ' + quotename(@dimtablename) + ' with(nolock)) ';
SET @sqlstmt = 'INSERT INTO dbo.Audit_HostDimensionTracking(DateKey, DimKey, Row_Count) VALUES (' + cast(@processdatekey as varchar(30)) + ',' + cast(@dimkey  as varchar(30)) + ',' + @DimCount + ')'

print @sqlstmt

All of that said you probably should look into using sp_executesql instead of just EXEC. This will let you parameterize your queries. Here is the same code using parameters.

DECLARE @counter INT = 1
    ,@dimtablename NVARCHAR(50) = 'dimtablename'
    ,@dimKey INT = 1
    ,@processdatekey INT = 1
    ,@DimCount NVARCHAR(4000)
    ,@sqlstmt NVARCHAR(4000)

    SET @DimCount = N'(SELECT COUNT(*) from ' + quotename(@dimtablename) + N' with(nolock)) ';
    SET @sqlstmt = N'INSERT INTO dbo.Audit_HostDimensionTracking(DateKey, DimKey, Row_Count) VALUES (@processdatekey,@dimkey,' + @DimCount + N')'

PRINT @sqlstmt

EXEC sp_executesql @sqlstmt, N'@dimKey INT, @Processdatekey INT', @dimKey, @processdatekey

This is particularly important when using character parameters to help avoid SQL Injection but it's also a good practice to get into in general.

Not the answer you're looking for? Browse other questions tagged or ask your own question.