Passing a Variable to an OpenQuery

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Passing a Variable to an OpenQuery

Author

Topic

mramey64
Starting Member

7 Posts

Posted - 2011-08-30 : 08:39:07

Hi guys,

I have a stored procedure I'm using to import data from a Progress Database into my SQL database. I'm trying to pass a variable into the where section of the OpenQuery. Basically its a Job#. Ultimately the variable is coming from an ADO connection within an Access application. In the following example I'm declaring the variable and setting the value for test purposes.

Here's my code:

DECLARE @Job VARCHAR(8)
SET NOCOUNT ON;

SET @Job = '1000'
IF(SELECT COUNT (*) FROM Job WHERE [Job-ID] = @Job)<1

BEGIN

INSERT INTO Job([Job-ID], [Job-Desc], [Cust-ID-Ordered-By], [Cust-ID-Bill-To], [Date-Promised], [Sales-Rep-ID], [CSR-ID], [Number-of-SubJobs], [Record-Active], [PO-Number],
[Update-Date], [Created-By], [Update-By], [Sub-Job-ID], [Created-Date], [Update-Time], [Job-Desc-Sort], [Last-Estimate-ID], [Date-Ship-By], [Job-Type], [Quantity-Ordered],
[Job-Complete], [Job-Title], [Postage-Requested], [Postage-Received], [Created-Time])
SELECT * FROM OPENQUERY([Progress], 'SELECT TOP 1 "Job-ID","Job-Desc","Cust-ID-Ordered-by","Cust-ID-Bill-to","Date-Promised", "Sales-Rep-ID", "CSR-ID", "Number-of-Subjobs",
"Record-Active", "PO-Number", "Update-date", "Created-By", "Update-by", "Sub-Job-ID", "Created-Date", "Update-Time", "Job-Desc-Sort",
"Last-Estimate-ID", "Date-Ship-By", "Job-Type", "Quantity-Ordered", "Job-Complete", "Job-Title", "Postage-Requested", "Postage-Received", "Created-Time"
FROM PUB.Job WHERE "Job-ID" = ''' + @Job + ''')

END

My variable works fine in the IF statement but not within the OpenQuery. I've googled the crap out of this subject and found some posts but in trying their solutions nothing works. Can anyone offer some wisdom here?

thanks!
Mike

Michael

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-30 : 09:31:10

you need dynamic sql for this.


DECLARE @Sql varchar(50)
SET @Sql='SELECT * FROM OPENQUERY([Progress], ''SELECT TOP 1 "Job-ID","Job-Desc","Cust-ID-Ordered-by","Cust-ID-Bill-to","Date-Promised", "Sales-Rep-ID", "CSR-ID", "Number-of-Subjobs",
"Record-Active", "PO-Number", "Update-date", "Created-By", "Update-by", "Sub-Job-ID", "Created-Date", "Update-Time", "Job-Desc-Sort",
"Last-Estimate-ID", "Date-Ship-By", "Job-Type", "Quantity-Ordered", "Job-Complete", "Job-Title", "Postage-Requested", "Postage-Received", "Created-Time"
FROM PUB.Job WHERE "Job-ID" = '''' + @Job + '''')'

EXEC (@Sql)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mramey64
Starting Member

7 Posts

Posted - 2011-08-30 : 09:49:35

I'm sorry I'm not sure how that helps with the "@Job" variable not working? Also this is an insert procedure? I am not sure how I would execute the (@Sql) as part of the insert?

Thanks
Mike

Michael

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-30 : 09:51:38

you can use INSERT table ...EXEC
OPEN QUERY cant recieve a variable inside like this. thats why I suggested to form a dynamic string using OPENQUERY and variable and executing it after

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mramey64
Starting Member

7 Posts

Posted - 2011-08-30 : 10:05:24

Thank you for your help. Have a great day!

Mike

Michael

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-30 : 10:10:22

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources