Dynamic SQL beginner!

[Puiu]

Ive 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.

Im guessing there is something wrong with the procedures code, but i couldnt 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. Its 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

 

[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 thats 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 youre 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 [Table] --
exec Test @s

The SQL youll 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:
exec (Select * from media where [description] in ( + REPLACE(@Description, , ) + ))

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 youll have to use the steps above:

Create proc Test
@Description varchar(100)
as 

exec sp_executesql 
	@statement = NSelect * 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. Im not sure how the engine handles that, but it knows about these arguments and does its thing!

-ner

 

[Puiu]

Thank you Nerseus, youve opened my eyes a little more

Ill probably come back soon with some new questions.