Dynamic SQL error converting nvarchar to int

Question

I created a procedure in dynamic SQL that has a select statement, and the code looks like this:

ALTER PROCEDURE cagroup ( @DataID INT , @days INT , @GName VARCHAR(50) , @T_ID INT , @Act BIT , @Key VARBINARY(16) ) AS BEGIN DECLARE @SQL NVARCHAR(MAX) DECLARE @SchemaName SYSNAME DECLARE @TableName SYSNAME DECLARE @DatabaseName SYSNAME DECLARE @BR CHAR(2) SET @BR = CHAR(13) + CHAR(10) SELECT @SchemaName = Source_Schema , @TableName = Source_Table , @DatabaseName = Source_Database FROM Source WHERE ID = @DataID SET @SQL = 'SELECT ' + @GName + ' AS GrName ,' + @BR + @T_ID + ' AS To_ID ,' + @BR + @DataID + ' AS DataSoID ,' + @BR + @Act + ' AS Active ,' + @BR + Key + ' AS key' + @BR + 'R_ID AS S_R_ID' + @BR + 'FROM' + @DatabaseName + '.' + @SchemaName + '.' + @TableName + ' t' + @BR + 'LEFT OUTER JOIN Gro g ON g.GName = ' + @GName + @BR + 'AND g.Data_ID] =' + @DataID + @BR + 't.[I_DATE] > GETDATE() -' + @days + @BR + 'g.GName IS NULL AND ' + @GName + ' IS NOT NULL AND t.[Act] = 1' + @BR PRINT (@SQL) END 

When I execute this procedure using this statement:

 Exec dbo.cagroup 1,10,'[Gro]',1,1,NULL 

I get the following error.

Msg 245, Level 16, State 1, Procedure Group, Line 33 Conversion error when converting nvarchar 'SELECT [Gro] AS GName,' value to int data type.

Where am I doing wrong?

Answer 1

In concatenation, you must perform CAST all numbers in nvarchar.

Converting a VBA style to a string is not implied in a string. In the previous SQL Server data type, the value of ints is greater than nvarchar: therefore, the entire row tries to be CAST for int.

 SET @SQL = 'SELECT ' + @GName + ' AS GrName ,' + @BR + CAST(@T_ID AS nvarchar(10)) + ' AS To_ID ,' ... 

Edit: will A be a good point: see NULL!

Answer 2

If you need to build such dynamic SQL, it is better to get column information from metadata than to pass it.

 Select * from Information_Schema.Columns Where Table_name=@TableName 

You need to write an ugly cursor to create SQL. Expect performance issues. I do a lot of this during development to write code for me, but I dare not run it during production.