exec to Variable - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

exec to Variable

Author

Topic

lane0618
Posting Yak Master

134 Posts

Posted - 2003-06-02 : 20:59:48

How do I put the executed value of @latestMTXID into a variable I can use later in my stored procedure? I also need to make sure it is of int data type.

declare @latestMTXID varchar(200)
select @latestMTXID = '1'
declare @latestMTXIDINT int

select @latestMTXID = 'select max(MTXID)+1 from mtx_MasterMtx'
exec(@latestMTXID)

select @latestMTXIDINT = exec(@latestMTXID)

Thanks!
Lane

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-06-02 : 21:44:30

Have a look at nigel's example

http://www.nigelrivett.net/sp_executeSQL.html

lane0618
Posting Yak Master

134 Posts

Posted - 2003-06-03 : 11:29:33

I tried this:

declare @i varchar(2)
exec sp_executesql
N'select max(MTXID)-1 as next from mtx_MasterMtx',
N'@i int output',
@i output

declare @sql varchar(1000)
select @sql = 'select * from mtx_MasterMtx where mtxID='
select @sql = @sql + @i
print(@sql)

results:
next
23

...but it seems to ignore the second(@sql) statement. What am I doing wrong?

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-03 : 13:44:28

declare @i int
exec sp_executesql
N'select @i = max(MTXID)-1 from mtx_MasterMtx',
N'@i int output',
@i output

declare @sql varchar(1000)
select @sql = 'select * from mtx_MasterMtx where mtxID='
select @sql = @sql + convert(varchar(2),@i)
print @sql
exec (@sql)

but you could
select * from mtx_MasterMtx where mtxID=(select max(MTXID)-1 from mtx_MasterMtx)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources