Showing posts with label northwindgodeclare. Show all posts
Showing posts with label northwindgodeclare. Show all posts

Saturday, February 11, 2012

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

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
Hi Thomas,
If dynamic SQL is a requirement, you could us a temp table to hold the EXEC
value. For example:
************
use msdb
GO
declare @.SelectString varchar(80)
,@.QueryString varchar(80)
,@.sQuote varchar(1)
CREATE TABLE #RecordCount
(RecordCount int)
set @.sQuote=''''
set @.QueryString='name like '+@.sQuote+'%'+'sys'+'%'+@.sQuote
set @.SelectString='select count(*) from dbo.sysobjects where
'+@.QueryString
INSERT #RecordCount (RecordCount)
exec(@.SelectString)
SELECT RecordCount
FROM #RecordCount
DROP TABLE #RecordCount
************
Otherwise - if dynamic SQL isn't necessary, you could encapsulate this code
in a stored procedure and return the count value as an output parameter.
Best Regards,
Joe Sack
Author of "SQL Server 2000 Fast Answers..."
http://www.JoeSack.com
"Thomas McFarlane" wrote:

> 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
>
>
|||Hi Joe,
I'm looking for the same solution.
I tried your suggestion.
Unfortunately it doesn't work. I got this Error-Message:
The operation could not performed, because OLE DB-Provider 'MSDAORA' could not start a distributed transaction.
OLE DB-Fehlertrace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b].
(Original:
Die Operation konnte nicht ausgef?hrt werden, da der OLE DB-Provider 'MSDAORA' keine verteilte Transaktion beginnen konnte.
OLE DB-Fehlertrace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b].)
I can execute the statement without the Temporary-Table.
Any ideas to this problem.......
Stefan
Message posted via http://www.sqlmonster.com
|||Hi Stefan,
Are you running this using a distributed query? Or against a local database?
The error you are getting relates to distributed query issues (MSDTC and the
lot).
Best Regards,
Joe Sack
Author of "SQL Server 2000 Fast Answers..."
http://www.JoeSack.com
"Stefan Hensinger via SQLMonster.com" wrote:

> Hi Joe,
> I'm looking for the same solution.
> I tried your suggestion.
> Unfortunately it doesn't work. I got this Error-Message:
> The operation could not performed, because OLE DB-Provider 'MSDAORA' could not start a distributed transaction.
> OLE DB-Fehlertrace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b].
> (Original:
> Die Operation konnte nicht ausgef?hrt werden, da der OLE DB-Provider 'MSDAORA' keine verteilte Transaktion beginnen konnte.
> OLE DB-Fehlertrace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b].)
> I can execute the statement without the Temporary-Table.
> Any ideas to this problem.......
> Stefan
> --
> Message posted via http://www.sqlmonster.com
>
|||Hi Joe,
You are right. I running this using a distributed query?
I will check the MSDTC.
Stefan
Message posted via http://www.sqlmonster.com