How to return value from Dynamic query

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)

How to return value from Dynamic query

Author

Topic

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2006-04-18 : 05:07:03

I want to get the count of rows in the table which match the status. I am writing dynamic query for it..

Create Procedure Dyn_Get_CountByStatus
(
@TableName varchar(200),
@Status int
)
as
Begin
Declare @strQuery varchar(500)
Declare @count int
set @strQuery = 'select count(*) from '+@TableName + 'where status=' + @Status
set @count =exec(@strQuery)
return @count
End
GO

This query is not working. How can get the desired result using dynamic query

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-18 : 05:17:47

Refer this
http://www.nigelrivett.net/SQLTsql/sp_executesql.html

Madhivanan

Failing to plan is Planning to fail

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2006-04-18 : 06:01:56

Thanks, but i have some problem
when i am writing following code it works fine

Declare @strQuery nvarchar(4000)
Declare @count int
set @strQuery = N'select @count=count(*) from '+ @TableName + ' where status=1'
EXEC sp_executesql @strQuery, N'@count int OUTPUT', @count OUTPUT
return @count

When I provide status as parameter , it gives error.
Message "Syntax error converting the nvarchar value 'select @count=count(*) from Forums_Categories where status=' to a column of data type int." String

Declare @strQuery nvarchar(4000)
Declare @count int
set @strQuery = N'select @count=count(*) from '+ @TableName + ' where status='+@Status
EXEC sp_executesql @strQuery, N'@count int OUTPUT', @count OUTPUT
return @count

I have tried to debug it, but could not fix it.Why is Status field not taking its value.pls help

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-18 : 06:48:43

Just try this ..

set @strQuery = N'select @count=count(*) from '+ @TableName + ' where Convert(varchar(10),status)='+Convert(varchar(10),@Status)

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-18 : 07:13:17

or pass @Status in as a parameter

Declare @strQuery nvarchar(4000)
Declare @count int
set @strQuery = N'select @count=count(*) from '+ @TableName + ' where status=@Status'
EXEC sp_executesql @strQuery, N'@count int OUTPUT, @Status int', @count OUTPUT, @Status
return @count

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2006-04-18 : 07:14:20

Thanks,its working now

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources