current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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
share|improve this question
1  
Is that your actual code? I don't see any initialisation of @counter –  Martin Smith 7 hours ago
    
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 7 hours ago
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 7 hours ago
    
So you fixed it? –  Max Vernon 6 hours ago
3  
@MickyT QUOTENAME() is far safer than manually adding square brackets yourself. –  Aaron Bertrand♦ 6 hours ago

1 Answer 1

up vote 3 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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