stored procedure helpppppp

[Mostafa Nabil]

how can i write a stored procedure in which the table can be a variable?
(i mean something like "FROM @table", but this doesn't work).

help will be greatly appreciated.

[vass0922]

you are right, you can not write the table name as a variable

SO one option is to build a string and exec the string

DECLARE @blah VARCHAR(255)
DECLARE @execstring VARCHAR(255)

SET @execstring = 'SELECT * FROM ' + @tablename + ' WHERE column = 4'

exec @execstring

if its a lengthy SP it can be very ugly and complicated, but functional

been there done that, it was very messy

[Mostafa Nabil]

i did it but i have a problem
when i execute it , this exception appears
(Could not find stored procedure 'SELECT Compound
FROM H1 WHERE reactant =na'.)

this is my stored procedure

ALTER PROCEDURE dbo.StoredProcedure2

(
@reactant varchar(5),
@tbl varchar(10)


)

AS
DECLARE @blah VARCHAR(255)
DECLARE @execstring VARCHAR(255)

SET @execstring = 'SELECT Compound FROM ' + @tbl + ' WHERE reactant =' + @reactant

exec @execstring
RETURN


i have a table called H1 and there is an element in reactants row called na

what's wrong?

[amtrac24]

try putting it in brackets

EXEC (@execstring)

[vass0922]

the problem will be your single quotes around what I'm assuming is a string

SO what you're doing right now is something like

select compound from tablename where reactant = somereactant

which doesn't bode well for sql

So how do you do that? well with single quotes of course!
select compound from tablename where reactant = 'somereactant'
AH but you're embedding single quotes within single quotes which breaks the string etc etc

SO the fun begins!
You want to escape all of your single quotes for the embedded string (NOT the string you're building to execute

select compound from tablename where reactant = \'somereactant\'

which should bring you some fugly looking string like

SET @execstring = 'SELECT Compound FROM ' + @tbl + ' WHERE reactant = \' ' + @reactant + '\''

simple
clear as mud?

Because these are so horrible to debug (they're worse than triggers to debug I swear... ) I'd suggest putting in some debug code

SET @execstring = 'SELECT Compound FROM ' + @tbl + ' WHERE reactant = \' ' + @reactant + '\''

PRINT @execstring
exec @execstring

so in the query analyzer you can always see exactly what you're attempting to execute.. which may not look so good until you clean it up.