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

About dynamic URL link

I want the URL link to be generated dynamically by a function written in a
custom assembly. However instead of generating the link each time the link is
clicked. The function only called once when the report is generated.
Therefore that link always return the same value. How can i implement this?
Thank you veru much.You can put per-report initialization in the OnInit() event in the Code
property of the report.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"mcngan" <mcngan@.discussions.microsoft.com> wrote in message
news:16560A52-A252-4796-AAA3-CE8755473631@.microsoft.com...
> I want the URL link to be generated dynamically by a function written in a
> custom assembly. However instead of generating the link each time the link
is
> clicked. The function only called once when the report is generated.
> Therefore that link always return the same value. How can i implement
this?
> Thank you veru much.