Passing Parameters into 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 2000 Forums

Transact-SQL (2000)

Passing Parameters into Openquery

Author

Topic

sahu74
Posting Yak Master

100 Posts

Posted - 2003-05-12 : 14:03:12

I want to pass a parameter into an openquery. Could anyone suggest how to get this done? I want the results into a temporary table.


DECLARE @YrNow VARCHAR(50)

SET @YrNow = '2003'

SELECT FISC_YEAR,FUND,ORGANIZATION,ACCOUNT_NBR,ENTITY,ORG_DEPARTMENT
INTO #ORGN
FROM openquery(SERVER1, 'SELECT * 
			FROM TECHFIM.DBO.TFORGCTB
			WHERE FISC_YEAR=''@YrNow''
			AND ACCOUNT_NBR>''6000''
			AND ENTITY IN ( ''H'','' '')') AS T1

Thanks,
Sahu.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-12 : 14:15:15

I don't think you can ... I don't believe OPENQUERY() allows for expressions as arguments, only string constants.

Am I wrong? (I hope so, actually -- it would be nice to call stored procs using OPENQUERY and pass paramaters, and also to be able to call it from a UDF to do some dynamic-SQL from within a UDF!)

- Jeff

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-12 : 14:25:22

something like this.

DECLARE @YrNow VARCHAR(50)
SET @YrNow = '2003'

declare @sql varchar(1000)
select @sql = insert #ORGN select FISC_YEAR,FUND,ORGANIZATION,ACCOUNT_NBR,ENTITY,ORG_DEPARTMENT
FROM openquery(SERVER1, ''SELECT *
FROM TECHFIM.DBO.TFORGCTB
WHERE FISC_YEAR=''' + @YrNow + '''
AND ACCOUNT_NBR>''''6000''''
AND ENTITY IN ( ''''H'''','''' '''')'') AS T1'

SELECT FISC_YEAR,FUND,ORGANIZATION,ACCOUNT_NBR,ENTITY,ORG_DEPARTMENT
INTO #ORGN
FROM openquery(SERVER1, 'SELECT *
FROM TECHFIM.DBO.TFORGCTB
WHERE 1 = 0') AS T1

exec (@sql)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-12 : 14:46:37

Nigel -- you lost me on that one. Can you clarify?

Thanks!

- Jeff

sahu74
Posting Yak Master

100 Posts

Posted - 2003-05-12 : 14:55:14

Thank you so much. That works fine. Just wanted to know if you could explain the logic. Am I asking too much?

Thank you.
PKS.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-12 : 16:12:50

You are using openquery to create a table with the structure returned from a query.

To use the variable in the query you will have to use dynamic sql to run the whole query.

This of course means that you can't create the temp table in the dynamic sql statement as it will be dropped whenm the batch ends.
So create the temp table first (with 1=0 as the where clause to just create the structure).
Then run the openquery insert in dynamic sql.

You might also consider whether using an openquery to create the temp table is a good idea in the first place. If you creater the temp table yourself it becomes much easier.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sahu74
Posting Yak Master

100 Posts

Posted - 2003-05-12 : 16:39:15

Thank you so much for explaining. Makes sense to me now. Actually I tried and got it working using your second option of creating the temp table first. But I think your first option looks cool to me although it went a little over my head the first time.

One last question: How does the logic of 1=0 work?

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-12 : 16:45:29

select * from tbl where 1=0 returns no rows (as 1 = 0 is always false.)
So it can be used to copy the table structure without the data.

select * into newtbl from oldtbl where 1 = 0

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sahu74
Posting Yak Master

100 Posts

Posted - 2003-05-12 : 17:12:47

Thanks a ton. Appreciate ur help.
PKS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources