About EXEC dynamic query ?

Hello SQL Scripters,
I have the following MS/SQL server 2000 Script:
*******************
use NorthWind
GO
declare @.RecordCount int
,@.SelectString varchar(80)
,@.QueryString varchar(80)
,@.sQuote varchar(1)
set @.sQuote=''''
set @.QueryString='LastName like '+@.sQuote+'%'+'Kos'+'%'+@.sQuote
set @.SelectString='select count(*) from dbo.Employees where
'+@.QueryString
set @.RecordCount=0
exec(@.SelectString)
GO
*******************
How can I extract the result to the local variable @.RecordCount ?
In my case, I have to use the EXEC method to perform dynamic query.
TIA
Thomas
I got it. I use @.@.ROWCOUNT
Thanks
Thomas
|||No actually that is not the correct way. First off there is no need to use
dynamic sql at all here as the SARG can be a variable.
DECLARE @.x VARCHAR(50), @.y INT
SET @.x = @.sQuote+'%'+'Kos'+'%'+@.sQuote
select count(*) from dbo.Employees where
LastName like @.x
If you want the count in a variable you can do this:
select @.y = count(*) from dbo.Employees where
LastName like @.x
And if you did have the need to use dynamic sql and wanted to return a value
you should use sp_executesql instead. See here for more details:
http://www.support.microsoft.com/?id=262499 Using OutPut Params &
sp_executeSql
http://www.sommarskog.se/dynamic_sql.html Dynamic SQL
Andrew J. Kelly SQL MVP
"Thomas McFarlane" <TM@.HotMeal.Com> wrote in message
news:OrH$yzr3EHA.1188@.tk2msftngp13.phx.gbl...
>I got it. I use @.@.ROWCOUNT
> Thanks
> Thomas
>
|||Andrew,
Thanks for your response. The problem with my requirement is that I have to
construct the dynamic SQL string. This is a "Procedure" that allows the user
to submit fields s/he wishes to query against a selected Table and selected
list of fields within a selected table at runtime.
Therefore, your instruction "select count(*) from dbo.Employees where
LastName like @.x" will not work for me because the field "LastName" is also
dynamic (unknown at design time).
Here is my Procedure in real life: (@.QryString can be as "LastName like
'Woo%' and Occupation='Golf' order by LastName,WinningScore; @.TableName can
be "GolfTournament"; @.RV=Return Value that holds the number of records; and
finally, the returned dataset (not shown here))
****************************
Create Procedure tQuery
@.QryString varchar(2048)
, @.Tablename varchar(60)
, @.RV int out as
Declare @.s varchar(2048)
set @.s='select * from '+@.TableName+' Where '+@.QryString
EXEC(@.s)
set @.RV=@.@.ROWCOUNT --This always gives me the accurate number of
records returned by the @.QryString
Return @.RV
Go
*****************************
As a result, I had to use the @.@.ROWCOUNT immediately after the EXEC(@.s). My
goal is to count 1st to make sure there is data for a submitted QryString,
but I just cannot simply say:
set @.RV=(select count(*) from @.Tablename where @.QryString
The above instreuction just does not compile.
Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23umX%23Ks3EHA.1976@.TK2MSFTNGP09.phx.gbl...
> No actually that is not the correct way. First off there is no need to
> use dynamic sql at all here as the SARG can be a variable.
>
> DECLARE @.x VARCHAR(50), @.y INT
> SET @.x = @.sQuote+'%'+'Kos'+'%'+@.sQuote
> select count(*) from dbo.Employees where
> LastName like @.x
>
> If you want the count in a variable you can do this:
>
> select @.y = count(*) from dbo.Employees where
> LastName like @.x
>
> And if you did have the need to use dynamic sql and wanted to return a
> value you should use sp_executesql instead. See here for more details:
>
> http://www.support.microsoft.com/?id=262499 Using OutPut Params &
> sp_executeSql
> http://www.sommarskog.se/dynamic_sql.html Dynamic SQL
>
> --
> Andrew J. Kelly SQL MVP
>
> "Thomas McFarlane" <TM@.HotMeal.Com> wrote in message
> news:OrH$yzr3EHA.1188@.tk2msftngp13.phx.gbl...
>
|||If you simply want to find out if there are any rows that match the query
you should use EXISTS instead of count(*) as it can be much more efficient.
But in any case the links I sent should be helpful in working with dynamic
sql.
Andrew J. Kelly SQL MVP
"Thomas McFarlane" <TM@.HotMeal.Com> wrote in message
news:efgZqjs3EHA.4004@.tk2msftngp13.phx.gbl...
> Andrew,
> Thanks for your response. The problem with my requirement is that I have
> to construct the dynamic SQL string. This is a "Procedure" that allows the
> user to submit fields s/he wishes to query against a selected Table and
> selected list of fields within a selected table at runtime.
> Therefore, your instruction "select count(*) from dbo.Employees where
> LastName like @.x" will not work for me because the field "LastName" is
> also dynamic (unknown at design time).
> Here is my Procedure in real life: (@.QryString can be as "LastName like
> 'Woo%' and Occupation='Golf' order by LastName,WinningScore; @.TableName
> can be "GolfTournament"; @.RV=Return Value that holds the number of
> records; and finally, the returned dataset (not shown here))
> ****************************
> Create Procedure tQuery
> @.QryString varchar(2048)
> , @.Tablename varchar(60)
> , @.RV int out as
> Declare @.s varchar(2048)
> set @.s='select * from '+@.TableName+' Where '+@.QryString
> EXEC(@.s)
> set @.RV=@.@.ROWCOUNT --This always gives me the accurate number of
> records returned by the @.QryString
> Return @.RV
> Go
> *****************************
> As a result, I had to use the @.@.ROWCOUNT immediately after the EXEC(@.s).
> My goal is to count 1st to make sure there is data for a submitted
> QryString, but I just cannot simply say:
> set @.RV=(select count(*) from @.Tablename where @.QryString
> The above instreuction just does not compile.
> Thomas
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23umX%23Ks3EHA.1976@.TK2MSFTNGP09.phx.gbl...
>
|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23ZRd5tt3EHA.2804@.TK2MSFTNGP15.phx.gbl...
> If you simply want to find out if there are any rows that match the query
> you should use EXISTS instead of count(*) as it can be much more
> efficient. But in any case the links I sent should be helpful in working
> with dynamic sql.
>
Thanks Andrew. I need the RecordCount for calculation purposes. But the
EXISTS function is definitely good. I am exploring more about the links you
gave. Very good stuffs.
Take care!
Thomas