Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 529
  • Last Modified:

Embed OPENQUERY syntax into stored proc

Hi,

I've been testing a Linked Server and querying the Linked Server via OPENQUERY with some success up to now, but now I need to embed the OPENQUERY code inside a stored proc ....

This is the code I've been running manually in Query Analyzer (note that this code works fine at this point and LS is the name of my Linked Server) :
<CODE SNIP START>
SET QUOTED_IDENTIFIER Off
GO

declare @InVal char(8)
declare @sql varchar(4000)

set @InVal = '02710185'

set @sql = "SELECT * FROM OPENQUERY(LS,'SELECT col1, col2 FROM mytab WHERE
col2 =''"  + @InVal + "''')"

PRINT @SQL
<CODE SNIP END>

Inside QA, this works as expected, as @SQL contains :
 SELECT * FROM OPENQUERY(LS,'SELECT col1, col2 FROM mytab WHERE
col2 =''02710185''')

So far, so good.

But now I'd like to put this code inside a stored procedure (eg sp_testLinkedServer) so that the code is actually executed (rather than my above example of PRINT @SQL). Initially, I'd be happy to leave @InVal hardcoded as '02710185' as in the above example, but I'd then like to refine the stoored procedure so that it cam accept an input parameter and place that parameter in @InVal.

Can anyone help with this, please ?
0
raymurphy
Asked:
raymurphy
  • 4
  • 3
  • 2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
exec (@sql)

will run the code
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
SET @SQl = ' SELECT * FROM OPENQUERY(LS,''SELECT col1, col2 FROM mytab WHERE col2 =''''02710185'''''')'

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
CREATE Proc uspOpenQuery
@InVal char(8)
AS
BEGIN
declare @sql nvarchar(4000)
SET @SQl = ' SELECT * FROM OPENQUERY(LS,''SELECT col1, col2 FROM mytab WHERE col2 =''''02710185'''''')'
exec sp_executeSQL @SQl
END
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
raymurphyAuthor Commented:
Thanks for the quick replies, and now getting closer ...

angelIII - Changing the print @sql to exec (@sql) sort of worked, but only when I ssued the CREATE PROC inside QA, ie using the following code :

CREATE PROCEDURE [dbo].[uspTestLS]
AS
SET QUOTED_IDENTIFIER Off
GO

declare @sql varchar(4000)
declare @InVal char(8)
set @InVal='02710185'
set @sql="SELECT * FROM OPENQUERY(LS,'SELECT col1, col2 FROM mytab WHERE col2 =''"  + @InVal + "''')"

exec (@sql)

did actually display the expected data in the  QA results pane immediately after executing the CREATE PROCEDURE, but then subsequently issuing EXEC uspTestLS in QA just returned "The command completed successfully" without showing the expected data.

aneeshattingal - using your posted code worked fine, ie :

CREATE Proc uspOpenQuery
@InVal char(8)
AS
BEGIN
declare @sql nvarchar(4000)
SET @SQl = ' SELECT * FROM OPENQUERY(LS,''SELECT col1, col2 FROM mytab WHERE col2 =''''02710185'''''')'
exec sp_executeSQL @SQl
END

and then subsequently issuing EXEC uspOpenQuery 02710185 did run OK and display the required data - so that's getting close to what I need. So, how would I amend the code in uspOpenQuery to pass the parameter in InVal down into the OPENQUERY statement, please ?

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this will work better:

SET QUOTED_IDENTIFIER Off
GO
CREATE PROCEDURE [dbo].[uspTestLS]
AS

declare @sql varchar(4000)
declare @InVal char(8)
set @InVal='02710185'
set @sql="SELECT * FROM OPENQUERY(LS,'SELECT col1, col2 FROM mytab WHERE col2 =''"  + @InVal + "''')"

exec (@sql)

go
exec [dbo].[uspTestLS]
0
 
raymurphyAuthor Commented:
Ahah, now got a working version ...

Have used angelIII's last code post, modified to accept a parameter and that works fine, ie :

SET QUOTED_IDENTIFIER Off
GO
CREATE PROCEDURE [dbo].[uspTestLS]
@InVal char(8)
AS

declare @sql varchar(4000)
set @sql="SELECT * FROM OPENQUERY(LS,'SELECT col1, col2 FROM mytab WHERE col2 =''"  + @InVal + "''')"

exec (@sql)

go

Then issuing EXEC uspTestLS '02710185' (or any other valid number) brings back the required results ....

So, I'm now happy with the solution so thanks to both angelIII and aneeshattingal, but unsure as to whether to award the points solely to angelIII or split between angelII and aneeshattingal - any preferences between you two helpful chaps, as I'd like to do whatever is fair ...
0
 
raymurphyAuthor Commented:
How do you prefer the points be awarded, then ?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Its your wish ............
0
 
raymurphyAuthor Commented:
Thanks for the help, much appreciated ...
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now