October 24, 2006 at 3:37 pm
SQL Server 2000
Hello all, I'm trying to figure out a way to set the contents of a dynamic query to a variable. I have the following segment of code to illustrate:
SET @statement= ' SELECT * FROM OPENQUERY(LINKNAME, 'SELECT Ticketnum FROM TABLE1 WHERE RowID='''''+ @identifier + ''''' )'
EXEC (@statement)
** The above works fine, the below does not.. but I need to set the results of the EXEC(@statement) to a variable somehow.
SET @statement= ' SELECT * FROM OPENQUERY(LINKNAME, 'SELECT Ticketnum FROM TABLE1 WHERE RowID='''''+ @identifier + ''''' )'
SET @Ticketvalue= EXEC (@statement)
**This example is simplified, but a few constraints exist
1. I MUST use the OPENQUERY command. We're querying a remote DB2 database and the DB link does not support 4 part naming.
2. I MUST build the query dynamically into a variable in order to include my @identifier variable. The OPENQUERY command does not support just placing the variable in the statement any other way.
Any ideas would be greatly appreciated. Thanks. Nate
October 24, 2006 at 4:09 pm
You could use Sp_ExecuteSQL but then you'd still end up with a table variable that cannot be made to use statistics...
If it were me, I'd use a temp table instead of a table variable which will be in scope for the dynamic SQL...
--Jeff Moden
October 24, 2006 at 4:13 pm
Also, have you looked into using sp_AddLinkedServer... it, in fact, supports links to DB2. See Books Online,
--Jeff Moden
October 25, 2006 at 12:58 am
Hi Nathan,
Aside from the more experienced responses above, your published statement string produces formatting errors.
Each time you pass a variable into another variable for execution you create another level of "scope" and for each level of scope inner apostrophies must be doubled, hence...
DECLARE @Statement varchar(1000), @Ticketvalue varchar(2000),@Identifier varchar(100)
SET @Identifier = 'MyIdentifierString'
SET @statement= '''SELECT * FROM OPENQUERY(LINKNAME, ''''SELECT Ticketnum FROM TABLE1 WHERE RowID=''''''''' + @identifier + ''''''''''''')'''
--PRINT @Statement
SET @Ticketvalue = 'EXEC (' + @Statement + ')'
--PRINT @Ticketvalue
EXEC (@Ticketvalue)
Hope this helps?
October 25, 2006 at 8:20 am
If the result from the query is a value (No a table) you can use something like this.
DECLARE
@SQL
nvarchar(2000),
@result
int,
@ParmDefinition
nvarchar(500),
@ConflictCount
int,
SET
@ParmDefinition = N'@ConflictCountOut int OUTPUT'
SET @SQL = N'SELECT @ConflictCountOut = count(*) FROM tbl_conflicts'
EXEC @result = sp_executesql
@SQL, @ParmDefinition,@ConflictCountOut=@ConflictCount OUTPUT
PRINT @ConflictCount
October 25, 2006 at 11:48 am
Maybe I'm missing something, but why can't you do something like this:
DECLARE @db2query varchar(8000)
, @statement varchar(8000)
, @identifier varchar(10)
, @ticketNum ???data type???
SET @identifier = '...'
SET @db2query = 'SELECT Ticketnum FROM TABLE1 WHERE RowID='''+ @identifier + ''''
--PRINT @db2query
SELECT @ticketNum = Ticketnum
FROM OPENQUERY(LINKNAME, @db2Query)
October 25, 2006 at 11:49 am
Sorry my first reply got hung up on formatting errors.
I think Pedro is right, I was (later) working on the same thing.
With this method, dynamic OPENQUERY(with your variable identifier) worked great using one of my own linked servers so, for what it's worth...
(If your identifier is an integer just change nvarchar(500) throughout.)
DECLARE @Ticketvalue nvarchar(500)
SET @Ticketvalue = 'your_identifier_string'
EXEC sp_executesql N'select @Ticketvalue = Ticketnum FROM
OPENQUERY(LINKEDSERVER,''SELECT Ticketnum,RowIDVarchar as
MYID
from TABLE1'')t1 where MYID = @Ticketvalue', N'@Ticketvalue
nvarchar(500)
output', @Ticketvalue output
--unfortunately @Ticketvalue remains unchanged when no match is found
IF IsNumeric(@Ticketvalue) = 1
BEGIN
SELECT @Ticketvalue
END
ELSE
BEGIN
SELECT 'NO MATCH FOUND'
END
October 25, 2006 at 12:07 pm
Hi mkeast,
According to BOL SQL Server 2000 does not accept variables in OPENQUERY.
Your suggested statement produced the following error:
Server: Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near '@db2Query'.
I do think Pedro has come up with the best solution.
October 25, 2006 at 12:11 pm
Thanks for all the help everyone, my client is having network issues currently, but as soon as they get them resolved I'll give the sp_executesql solution a try!
October 25, 2006 at 2:00 pm
Nathan,
Hope this helps...I had to do something similar this week. A snippet from the code I wrote that concerns writing the statment to a temp table and executing the statments.
Kim
--create a temp table that will store the insert statments
--drop table #Stmt
CREATE TABLE #Stmt (descr varchar(1000) NOT NULL )
set @sql = 'insert into #Stmt
'
set @sql = @sql+'select ''insert openquery('+@LinkedServer
set @sql = @sql+','
set @sql = @sql+' ''''select '
set @sql = @sql+@list1
set @sql = @sql+' from '
set @sql = @sql+@RemoteTableName
set @sql = @sql+' where 1=0'''' '
set @sql = @sql+') VALUES(''+ '
set @sql = @sql+@list2
set @sql = @sql+'+'
set @sql = @sql+''')'
set @sql = @sql+'''
from '
set @sql = @sql+@LocalTableName
set @sql = @sql+' Where 1=1 '
set @sql = @sql + @LocalWhereClause
EXEC(@sql)
--This is an example statment that is stored in @Stmt.descr
--insert openquery(INTERFLOW02,'select transmissionid,description,sortorder,active from Inventory.dbo.Transmissions where 1=0') VALUES(0,'None',0,1)
--Now, execute the statements stored in the temp table
--using my own version of master.dbo.sp_execresultset
--the code is exactly the same, but I had to create it with
--SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON
--for linked server shouldn't modify the master.dbo.sp_execresultset or it violate support agreement
declare @retcode bit,@proc nvarchar(4000)
select @proc = quotename('Inventory') + N'.dbo.IFexecresultset'
exec @retcode = @proc 'select descr from #Stmt', 0
October 25, 2006 at 4:50 pm
Great solution, but do you really think that list of "set @sql = @sql + ..." statements is readable? This isn't Visual Basic, you can use multiple lines in one SET statement:
set
@sql = 'insert into #Stmt
select ''insert openquery(' + @LinkedServer + ','
+ ' ''''select ' + @list1 + ' from ' + @RemoteTableName
+ ' where 1=0'''' ) VALUES(''+ ' + @list2 + ' + '')''
from ' + @LocalTableName + ' Where 1=1 ' + @LocalWhereClause
This is the way I would write it to make it even more readable IMHO:
set
@sql = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
'insert into #Stmt
select ''insert openquery(<LinkedServer>, ''''select <list1> from <RemoteTableName> where 1=0'''' ) VALUES(<list2> )''
from <LocalTableName> Where 1=1 <LocalWhereClause>',
'<LinkedServer>', @LinkedServer),
'<list1>', @list1),
'<list2>', @list2),
'<RemoteTableName>', @RemoteTableName),
'<LocalTableName>', @LocalTableName),
'<LocalWhereClause>', @LocalWhereClause)
October 26, 2006 at 2:55 am
Hi All,
Another tested variation which works.
DECLARE
@Ticketvalue integer,
@Identification varchar(100)
SET @Identification = 'identity string'
SELECT @Ticketvalue = Ticketnum
FROM
(select * from
openquery(LINKSERVER,'select Ticketnum,RowID from TABLE1')t1)t2
WHERE RowID = @Identification
select @Ticketvalue
October 26, 2006 at 8:07 am
Using the sp_executesql stored proc got me past my issues. Thank you so much to everyone who responded. Nate.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply