Dynamic Stored Procedures

Hi everyone,

My question is how can i set a var with a table name to use it in a SELECT statement for example.

EX:

Declare @.table varchar
Set @.table = 'mytable'

Select * From @.table

I've got 1 stored procedure wich i want to use to get and update 2 tables. For that reason i want to know how to do this because having 2 stored procedures when the only difference are table names its not a good solution.

Thanks :)

Declare @.sql varchar
Declare @.table varchar
Set @.table = 'mytable'

set @.sql = 'Select * From '+ @.table

execute(@.sql)

visit this link for more info

http://www.sommarskog.se/dynamic_sql.html

|||

Tiago:

You can do what you ask with something like:

exec ( 'select * from ' + @.table )

A safer method is something that does not potentially incur problems from hacking is something like:

if @.table = 'A'
select * from A
else
select * from B

The other comment I have has to do with the "select *" syntax; is this what you are really planning on doing? Because this syntax leaves "land mines." What I mean is that if you include this syntax in a stored procedure and later make column changes to either table A or table B these "select *" statements are likely to surprise you.

The reason is that what "select *" means in a stored procedure has to do with what "select *" meant at COMPILE time and NOT what "select *" should mean at RUN time! If in fact what you are trying to do is to write a generic stored procedure that returns all columns for an unspecified table you need to realize that this is a potentially dangerous stored procedure in a number of different ways. Also, you should tend to explicitly list all columns instead of using "select *" syntax when dealing with a permanent table. This is not so bad with temp tables, but I am assuming here that your target tables are not temp tables.

If you are wanting a stored procedure to list all columns of permanent tables that are not isomorphic -- that is, the forms of the tables differernt -- in my opinion you are better off writing separate stored procedures.

Dave

|||

Thanks for quick answers.

I've already put my query correctly, but i've got another problem.

The "dynamic query" its used in a CURSOR and i've getting error with that because Exec(@.query)

My Code:

set @.query = 'SELECT CodigoConta,AnoOrcamento FROM ' + @.t +
' WHERE AnoOrcamento = ' + CAST(@.Ano as varchar) +
' AND CodigoConta like ''' + cast(@.Classe as varchar) +
'%'' ORDER BY CAST(CodigoConta as varchar)'

DECLARE Orcamento_Cursor CURSOR FOR
execute(@.query).

when i try to save the SP, i'm getting this error:

Incorrect syntax near the keyword 'execute'.

Can you help me with this problem ?

|||

Tiago:

Would you mind posting the rest of your process that is using this cursor? (I just marched my army right off a cliff; trying not to repeat it.)

Dave

|||

Mugambo,

I've clear the code because i don't have more time now to get arround with this, so i'm using something like this:

IF @.Classe = '6'
BEGIN
DECLARE Orcamento_Cursor CURSOR FOR
SELECT CodigoConta
FROM OrcamentoCustosPerdas
WHERE AnoOrcamento = @.Ano
AND CodigoConta like @.Classe + '%'
ORDER BY CAST(CodigoConta AS VARCHAR)
END

ELSE

BEGIN
DECLARE Orcamento_Cursor CURSOR FOR
SELECT CodigoConta
FROM OrcamentoProveitosGanhos
WHERE AnoOrcamento = @.Ano
AND CodigoConta like @.Classe + '%'
ORDER BY CAST(CodigoConta AS VARCHAR)
END

Maybe in future i change this code to a dynamic one. Btw, thanks a lot for u'r help. :)

|||

Tiago:

I think this adjustment will work; you are welcome.

Dave

|||create a temp table,...store the result of execute in that...then in cursor..use select * from #temp, ..though better options may exist...|||( HELP! I am really concerned that I have badly screwed this up. )