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