Dynamic SQL error converting nvarchar to int

Question

I have created a procedure in dynamic SQL which has a select statement and the code looks like:

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 am executing this procedure with this statement:

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

I am getting the following error.

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

Where am I doing wrong?

Adlai

That code either doesn't work or it's been modified from working code as to make it unworking. In particular, @GName is not declared as far as I can tell and in any case, this bit of code: AND' + @GName + 'IS NOT NULL will not work unless @GName has whitespace on front and back. – Pahari

@Cade Roux I edited the code those were just typo's – Adlai

what's the need for the dynamicness? – Ralfston

@Dforck the table is going to change depending on the @DataID – Adlai

@Sam, why not just create a script that generates your crud procedures and then just execute those? having code generated sp's will be a lot easier to maintain than dynamically created inserts and selects. – Ralfston

DForck42 is right. Better to have a couple hundred sp's that you can rebuild from a script. SQL will pre-compile and optimize the sp's and your performance will be much better. – Freehanded

Answer 1

You need to CAST all numbers to nvarchar in the concatenation.

There is no implicit VBA style conversion to string. In SQL Server data type precedence means ints are higher then nvarchar: so the whole string is trying to be CAST to int.

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

Edit: Will A has a good point: watch for NULLs!

Facsimile

@GBN Thank You so much. I added cast to all numbers and when I execute it just says Query Executed successfully it doesn't print the sql statement. Is there anything that I need to modify – Adlai

@Sam - check that none of the values you're using in your concatenation are NULL - try to concatenate a NULL and you'll end up with a NULL. – Variegated

@Will A Ya you are right one of my value is null in concatenation.The parameter @Key i am passing null to it How do I deal with it? – Adlai

@Sam - COALESCE(@mightbenull, 'string to use if value is null') – Variegated

Answer 2

If you have to build this kind of dynamic SQL, it is better to get the column information from the meta-data than to pass it around.

Select * from Information_Schema.Columns Where Table_name=@TableName

The you have to write an ugly cursor to build the SQL. Expect performance problems. I do lots of this during development to write code for me, but I don't dare run it in production.

Freehanded