dynamic query with variables in store procedure

[Hank Lee]

Hi!
Here is a snap form my code

declare @max_id int
declare @the_db varchar (30)
select @the_db = 'mydb'
exec ("select """ @max_id """ = max(id) from " + @the_db + "..mytable&#34

I got syntex error for the @max_id. The script is writen based on the sample given in MSSQL6.5 Book Online, chapter "Transaction-SQL Reference 6.0" section "C"->"Control-Flow Lang."->"Control-Flow Examples"

Can someone help me on how to assign a value to a local variable from a dynamic query.

Thank for any help in advance
Hank Lee

[Rosta]

Hi

I found this way to pass local variables into and from exec('...&#39.

create procedure _TestLocVar
as
declare
@title varchar(6),
@qty int,
@MessageFromExec varchar(30)
exec('
create procedure #_OutFromExec
@title varchar(6) OUT,
@qty int OUT,
@MessageFromExec varchar(30) OUT
as
SELECT
@qty = SUM(qty),
@title = MAX(title_id)
FROM pubs.dbo.sales
--
select @MessageFromExec = ''It works. Written by Rosta.''
return (0)
&#39
exec #_OutFromExec
@title OUT,
@qty OUT,
@MessageFromExec OUT
select
@title,
@qty,
@MessageFromExec
drop procedure #_OutFromExec
return (0)




Rosta