Inserting variables into another variable 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 &lt;= (
        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
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.