Dynamic SQL - get count - aug 5th

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

Transact-SQL (2000)

Dynamic SQL - get count - aug 5th

Author

Topic

rajsequel
Starting Member

8 Posts

Posted - 2003-08-05 : 04:09:13

Hi all,
the below is working fine, but the thing is i want to get the output to a variable,i.e. step 1 is over. want step 2 to complete.

step 1
======
drop proc sp_GetId
go
create proc sp_GetId
as
declare @msql varchar(100)
begin
set @msql = 'select count(*) from emp'
exec(@msql)
end

step 2
======
drop proc sp_GetId
go
create proc sp_GetId
as
declare @msql varchar(100)
declare @mid int
begin
set @msql = 'select count(*) from emp'
set @mid = exec(@msql)
end

rajesh

vganesh76
Yak Posting Veteran

64 Posts

Posted - 2003-08-05 : 05:04:12

Probably try this

drop proc sp_GetId
go
create proc sp_GetId
as
declare @msql varchar(100)
declare @mid int

Create table #temptbl(reccount int)
set @msql = 'Insert into #temptbl SELECT count(*) from status '
exec(@msql)
select @mid = reccount From #temptbl
drop table #temptbl
select @mid

V.Ganesh
Net Asset Management.Com
vganesh76@rediffmail.com

Enjoy working

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-08-05 : 05:21:50

[code]
drop proc sp_GetId
go
create proc sp_GetId
as
declare @msql varchar(100)
declare @mid int
begin
set @msql = 'select count(*) from emp'
set @mid = exec(@msql)
end
[/code]

You can not do the bit in red. Use sp_executesql instead.

Something like this would work :

[code]
declare @msql nvarchar(1000)
declare @mid int

select @msql = 'select @mid = count(*) from emp'

exec sp_executesql @msql, N'@mid int output', @mid output
select @mid
[/code]

----------------
Shadow to Light

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources