stored procedures

[paulw]

You can't do it like that. It will not do symbolic substitution like that.

You need touse something like:

CREATE PROCEDURE Test 
@field varchar(100), 
@condition varchar(100) 
AS
DECLARE @sqlstr varchar(100)
BEGIN
SET @sqlstr = "SELECT * FROM Job WHERE " + rtrim(@field) + "='" + rtrim(@condition) + "'"
EXEC (@sqlstr)
END

Note the ' ' round the LIKE clause - you'd missed that out. Also note the brackets in EXEC(@sqlstr) - that tells it it is a character string to be interpreted.

Cheers,

P.

[DaveR]

i know where your coming from , it still doesnt work though.
i'm trying the following line , but it still aint doing anything.Its returning an empty recordset.

any ideas ?

SET @sqlstr = "SELECT * FROM Job
WHERE " + @field + " LIKE ' " + @condition + " ' "

+

i've tried

SET @sqlstr = "SELECT * FROM Job
WHERE ' " + @field + " ' LIKE ' " + @condition + " ' "

also with no luck

[Bigley]

2 things DaveR, firstly I am wondering if you have forgotten your '%' in your latest attempt and secondly it might be worth while printing the string before you execute it to see what it looks like.

Simple syntax :
print @strSQL

It just might point out a glaring error. Or at least you could copy it into a query analyser window and see if it returns anything in there.

HTH

[paulw]

Oops - I put = didn't I?

Still, you shouldn't cut and paste.

Bigley is absolutely right (as always)

P.

[DaveR]

thanks for the help guys,
just one thing , when I try the

print @strSQL

line in the SP , I get an error in my calling program , 'operation is not allowed when object is closed'

Where is the SQL supposed to be printed?

[Bigley]

I was really only suggesting that you use it for debugging purposes.

So if you ran the SP in query analyser you would get a return value in the results pain. Once the SP is working fine there is no need for it anymore.

[paulw]

You could also use

SELECT @strSQL

My original code was:

CREATE PROCEDURE Test 
@field varchar(100), 
@condition varchar(100) 
AS
DECLARE @sqlstr varchar(100)
BEGIN
SET @sqlstr = "SELECT * FROM Job WHERE " + rtrim(@field) + "='" + rtrim(@condition) + "'"
SELECT @sqlstr
/*EXEC (@sqlstr)*/
END

That way I could check the string returned for syntax.

Cheers,

P.