Dowt in a procedure - 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)

Dowt in a procedure

Author

Topic

iswan
Starting Member

28 Posts

Posted - 2007-06-07 : 03:00:13

The below is correctly executing
exec('declare @NumCount int select @NumCount=count(*) from '+@Projectname)

But I want to get the output outside the exec. Using this output I process some other function. I don't know how to store output in new variable

I need like this, But It is not coming correctly

create proc trial
(
@Projectname varchar(512)
)
as
declare @NumCount int
set @NumCount=exec(select count(*) from '+@Projectname)
if(@NumCount>1)
begin
//....(more process)
end
end

bpgupta
Yak Posting Veteran

75 Posts

Posted - 2007-06-07 : 03:30:14

Can clarify what is variable @projectname

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-07 : 04:22:29

I donno why u want dynamic sql.....but
this will help you

declare @count int
exec('select count(*) from '+ @Projectname)
Set @count = @@rowcount

@@rowcount will give the no of rows affected by the last statement
here you want count, so take that into some variable after the exec statement.

--------------------------------------------------
S.Ahamed

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-07 : 04:31:10

quote:
Originally posted by pbguy

I donno why u want dynamic sql.....but
this will help you

declare @count int
exec('select count(*) from '+ @Projectname)
Set @count = @@rowcount

@@rowcount will give the no of rows affected by the last statement
here you want count, so take that into some variable after the exec statement.

--------------------------------------------------
S.Ahamed

You will always get 1 for that
The correct method is using sp_executesql
http://www.nigelrivett.net/SQLTsql/sp_executeSQL.html

Madhivanan

Failing to plan is Planning to fail

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-07 : 04:40:00

Sorry man,
i did not notice that in the message tab

Thanks for pointing

--------------------------------------------------
S.Ahamed

iswan
Starting Member

28 Posts

Posted - 2007-06-08 : 02:26:37

Thanks, I have used the procedure. Now it is working fine

Iswan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-08 : 03:24:03

But why do you need to pass table name as parameter?
More on dynamic sql
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources