dynamic sql

Question

dynamic sql

sql_fan

SSC-Addicted

Points: 462
More actions
April 27, 2006 at 2:41 pm

#172302

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.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

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)

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

sql_fan SSC-Addicted Points: 462 More actions · Answer 2

sql_fan

SSC-Addicted

Points: 462

April 27, 2006 at 3:14 pm

#634706

thanks.

David Webb-CDS SSCoach Points: 17398 More actions · Answer 3

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

[KH] SSCommitted Points: 1908 More actions · Answer 4

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

And may I ask why you are using dynamic sql for this???

Jaime Díaz Díaz Newbie Points: 1 More actions · Answer 6

Try this

SELECT @sql='SELECT * FROM emp WHERE id = ' + Convert(Varchar,@id) + ' ORDER BY id'

EXEC (@sql)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

My questions was aimed at him avoiding the use of dynamic sql.. not encourage him :-).

sushila SSC-Dedicated Points: 35293 More actions · Answer 8

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 !!!**

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Ninja's_RGR'us

SSC Guru

Points: 294069

May 2, 2006 at 12:06 pm

#635420

Well it's his job at risk (or not), not mine :-).