EXECuting a string to run a SELECT?

[markSaunders]

Is is possible to have a stored proc that will run against a table that is specified as a parameter - such as....

CREATE PROCEDURE usp_items	@iItem INT=0,
				@vItem varchar(50)
AS

DECLARE @string varchar(255)
SET @string = 'SELECT * FROM ' + @vItem + ' WHERE (' + @vItem + 'ID=' + cast(@iItem as varchar) + ') or (@i' + @vItem + '=0)   ORDER BY title'

execute @string
GO

where Item represents the table

This would allow me to use one stored procedure against 5 almost identical tables rather than 5 stored procs.

The error message I receive is

Server: Msg 2812, Level 16, State 62, Line 17
Could not find stored procedure 'SELECT * FROM skill WHERE (skillID=0) or (@iskill=0)   ORDER BY title'.

as it's obviously trying to execute a stored proc and not the actual command line I have defined.

Any ideas?

mark Mark Saunders

 

[sunila7]

Hi Mark

Try this out..... i have put a bracket around @String after execute....
CREATE PROCEDURE usp_items @iItem INT=0,
@vItem varchar(50)
AS

DECLARE @string varchar(255)
SET @string = 'SELECT * FROM ' + @vItem + ' WHERE (' + @vItem + 'ID=' + cast(@iItem as varchar) + ') or (@i' + @vItem + '=0) ORDER BY title'

execute(@string)
GO

regards

sunil

 

[clapag22]

change:
execute @string

to:
execute(@string)

 

[bperry]

I think an issue is going to be that when the Select runs, it will not have any idea what the variable @iskill is.

WHERE (skillID=0) or (@iskill=0)

 

[RobertT687]

Assuming your @string is a good SQL statement, all you should need to do is: Execute (@string) rather than Execute @string.

 

[markSaunders]

The brackets worked - cheers.

Also, i had put the iskill=0 incorrectly as it should have read 0=0

Cheers for the help!

M Mark Saunders