Returning Value from Dynamic query

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

Try using the @.@.rowcount instead....

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

set @.count =@.@.rowcount
return @.count
End
GO

|||

You can do something like what I show below, BUT.... before you do, you should know that using dynamic SQL in the way you are using it is a VERY BAD IDEA. Suppose someone names a table something like [(select 1 i) T delete clients --] Then if you run this procedure, all rows from the table called [clients] will be deleted! If anyone has permission to create tables in your database on which this procedure will be executed, they can create a maliciously-named table name and wait for someone with elevated permission to run this procedure. You can protect yourself from most dangers fairly well by using QUOTENAME, though there are some truncation questions that can still allow security risks even when QUOTENAME is used. Valuable reading: http://www.sommarskog.se/dynamic_sql.html http://mvp.unixwiz.net/techtips/sql-injection.html -- MODIFY AT YOUR OWN RISK create table clients (i int) insert into clients values (10) insert into clients values (13) go declare @.tn sysname set @.tn = 'clients' declare @.TotalRecords int declare @.sql nvarchar(600) set @.sql = N' select @.TotalRecords = count(*) from '+ quotename(@.tn) + ' where i > @.param' exec sp_executesql @.sql, N'@.param int, @.TotalRecords int OUTPUT', @.TotalRecords = @.TotalRecords OUTPUT, @.param = 11 select @.TotalRecords go -- Steve Kass -- Drew University ashwin_k_s@.discussions.microsoft.com wrote:
> 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
>
>
>