Dynamic SQL error converting nvarchar to int
Solution 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!
Solution 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.
Related videos on Youtube
Peter Mortensen
Experienced application developer. Software Engineer. M.Sc.E.E. C++ (10 years), software engineering, .NET/C#/VB.NET (12 years), usability testing, Perl, scientific computing, Python, Windows/Macintosh/Linux, Z80 assembly, CAN bus/CANopen. Contact I can be contacted through this reCAPTCHA (requires JavaScript to be allowed from google.com and possibly other(s)). Make sure to make the subject specific (I said: specific. Repeat: specific subject required). I can not stress this enough - 90% of you can not compose a specific subject, but instead use some generic subject. Use a specific subject, damn it! You still don't get it. It can't be that difficult to provide a specific subject to an email instead of a generic one. For example, including meta content like "quick question" is unhelpful. Concentrate on the actual subject. Did I say specific? I think I did. Let me repeat it just in case: use a specific subject in your email (otherwise it will no be opened at all). Selected questions, etc.: End-of-line identifier in VB.NET? How can I determine if a .NET assembly was built for x86 or x64? C++ reference - sample memmove The difference between + and & for joining strings in VB.NET Some of my other accounts: Careers. [/]. Super User (SU). [/]. Other My 15 minutes of fame on Super User My 15 minutes of fame in Denmark Blog. Sample: Jump the shark. LinkedIn @PeterMortensen (Twitter) Quora GitHub Full jump page (Last updated 2021-11-25)
Updated on June 04, 2022Comments
-
Peter Mortensen almost 2 years
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?
-
DForck42 almost 13 years@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.
-
-
Admin almost 13 years@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
-
Will A almost 13 years@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.
-
Admin almost 13 years@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?
-
Will A almost 13 years@Sam -
COALESCE(@mightbenull, 'string to use if value is null')