Dynamic SQL beginner!

[Puiu]

I've created a simple Stored Procedure:

 

Create   proc Test
@Description varchar(100)
as 

exec ('Select * from media where description in (' + @Description + ')')

 

When i try to execute the procedure like this:

Test ('forest')

or

 

exec Test ('forest')

 

i get the following error message:

 

Server: Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near 'forest'.

 

I'm guessing there is something wrong with the procedures' code, but i couldn't find out what....

Thanx

[MadMaxx]

Change it to the following

 

Create   proc Test
@Description varchar(100)
as 
Select * from media where description in (@Description)

 

Then execute it like this

 

exec Test 'somedescription'

[samsmithnz]

And even better, take out that in. It's unnecessary and expensive.

 

Create   proc Test
@Description varchar(100)
as 
Select * from media where description = @Description

[Puiu]

What i meant was that i was trying to learn dynamic SQL. I know that query was not perfect, but i was just wondering why i got that error!!

 

What was wrong with that syntax ?

 

Even if call it like this:

 

exec test 'somedescription'

 

I get the error: Invalid column name 'somedescription'

 

Sorry for the delay

Edited September 15, 2005 by Puiu

[Nerseus]

Technically you need only put single quotes around your SQL:

Create   proc Test
@Description varchar(100)
as 

exec ('Select * from media where description in (''' + @Description + ''')')

 

If I might make a few suggestions?

1. Put brackets around description since that's a reserved word:

exec ('Select * from media where [description] in (''' + @Description + ''')')

 

2. Use "=" instead of "IN" if you really only have one value. If this is truly a list of items, then you're doing fine.

 

3. Make sure you replace single quotes in @Description with doubled-up single quotes to prevent SQL injection OR use step 4.

To see SQL injection, try this to see SQL injection at work:

declare @sql varchar(255)
set @sql = 'value''); DELETE FROM 

 
The SQL you'll be running will look like:
Select * from media where description in ('value'); DELETE FROM 
 
You just allowed someone to delete from one of your tables!
 
To double up single quotes, use this:
[b]exec ('Select * from media where [description] in (''' + REPLACE(@Description, '''', '''''') + ''')')[/b]
 
4. Instead of custom, dynamic SQL, try using sp_executesql. This will work if you need to compare to one value with an "=". If you need to use IN because @Description is a list of values, then you'll have to use the steps above:
[code]
Create proc Test
@Description varchar(100)
as 

exec sp_executesql 
@statement = N'Select * from media where [description] = @DescriptionVal',
@params = N'@DescriptionVal varchar(100)',
@DescriptionVal = @Description

 

They key bits above:

A. The SQL used in sp_executesql is easy to read - it uses "where [description] = @DescriptionVal" which just reads nice. ie, easy to debug.

B. You have to define the params used in the dynamic sql - those params are strings (@DescriptionVal above).

C. The @statement and @params are nvarchar, hence the N' syntax on the strings.

D. I generally add "Val" to the end of my dynamic SQL params, but otherwise keeping them the same name. So when the param to the proc is @Description then the param in the dynamic SQL is @DescriptionVal. The last line of the code above assigns the value to the dynamic SQL. It looks as if the proc sp_executesql has 3 params: @statement, @params, @DescriptonVal. I'm not sure how the engine handles that, but it knows about these arguments and does its thing!

 

-ner

[Puiu]

Thank you Nerseus, you've opened my eyes a little more :)

 

I'll probably come back soon with some new questions.