Use Parameters with OPENQUERY in SQL

April 23, 2008 15:00 by John M

Unfortunately, in the Microsoft SQL world, sometimes you have to deal with using OPENQUERY.  This is difficult to use sometimes because you don't have as much flexibility with variables and parameters as you would using normal T-SQL.  Below is an example of how you can use OPENQUERY and still utilize a variable or parameter.

DECLARE @variable VARCHAR(10)
DECLARE @sqlQuery VARCHAR(8000)
DECLARE @finalQuery VARCHAR(8000)

SET @variable = 'yourStringValue'

SET @sqlQuery = 'SELECT * FROM tableName WHERE strValue = ' + '''' + '''' + @variable + '''' + ''''

SET @finalQuery = 'SELECT * FROM OPENQUERY(LINKEDSERVER,' + '''' + @sqlQuery + '''' + ')'

--used for debugging
SELECT @finalQuery

EXEC(@finalQuery)

NOTE: It may be hard to see but the '''' is 4 single quotes

I recommend commenting out EXEC(@finalQuery) until you see @finalQuery correctly in the results.  Running SELECT @finalQuery is very helpful because you can see what single quotes are missing if need be. 

I also recommend getting your OPENQUERY statement to work correctly before trying to make it "dynamic" so to say using the above method.

Anyway, I found this very useful to use and hope that it can help some others out there.


Be the first to rate this post

  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5

Tags:
Categories: Microsoft SQL
Actions: E-mail | Permalink | Comments (5) | Comment RSSRSS comment feed

Related posts

Comments

April 23. 2008 15:20

trackback

Trackback from DotNetKicks.com

Parameters in OPENQUERY

DotNetKicks.com

April 25. 2008 03:40

Gravatar

Thanks .you saved my lots of time.

i was

November 14. 2008 08:21

Gravatar

Thank you so much!!!!!

Shanker

December 9. 2008 14:45

Gravatar

Thank you for your contribution.

John

December 31. 2008 01:14

Gravatar

Thanks a lot, nice query

Bhoju

Add comment


(Will show your Gravatar icon)  

  Country flag




Live preview

January 6. 2009 13:38

Gravatar