dynamic sql
-
DECLARE @id int
SET @id=2
DECLARE @sql varchar(400)
SELECT @sql='SELECT * FROM emp WHERE id='+ @id+ 'ORDER BY id'
EXEC (@sql)
I am getting the following error when I execute the above code.
Syntax error converting the varchar value 'SELECT * FROM emp WHERE id=' to a column of data type int.
What should be the reason and how to fix it. Any help would be highly appreciated.
Thanks.
-
The parser is trying to cast the first portion of you @sql to int implicitly. Instead, you need to explicitly cast @id to varchar.
DECLARE @id int
SET @id=2
DECLARE @sql varchar(400)
SELECT @sql='SELECT * FROM emp WHERE id='+ cast(@id as varchar(30)) + 'ORDER BY id'
EXEC (@sql)
-
thanks.
-
You might want to add a space in front of the ORDER BY to make sure it doesn't run into the id.
And then again, I might be wrong ...
David Webb -
DECLARE @id int
SET @id=2
DECLARE @sql varchar(400)
SELECT @sql='SELECT * FROM emp WHERE id=@id ORDER BY id'
exec sp_executesql @sql, N'@id int', @id
-
And may I ask why you are using dynamic sql for this???
-
Try this
SELECT @sql='SELECT * FROM emp WHERE id = ' + Convert(Varchar,@id) + ' ORDER BY id'
EXEC (@sql)
-
My questions was aimed at him avoiding the use of dynamic sql.. not encourage him :-).
-
remi - your question was not clear enough...you should've said - "in the example posted, it is not clear why dynamic sql is being used - try & avoid dynamic sql as much as you would a cursor...if you explain the requirements someone may be able to come up with a solution that eliminates the use of dynamic sql!"
**ASCII stupid question, get a stupid ANSI !!!** -
Well it's his job at risk (or not), not mine :-).
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply