passing parameter to 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 2012 Forums

Transact-SQL (2012)

passing parameter to openquery

Author

Topic

kowalsky
Starting Member

29 Posts

Posted - 2013-09-03 : 11:49:51

hi all,
quick question on openquery and parameters; the query below behaves correctly for today's date. How do I modify the query to substitute the 09/02/2013 date with a variable?

insert into sqlTable(ID, val1, val2, val3, val4, FLAG)
(
SELECT * , GETDATE(), 'Y'
FROM OPENQUERY([myOraLinkedServer],'SELECT
OraTbl1.val1,
OraTbl1.val2,
OraTbl1.val3,
OraTbl1.val4,
FROM OraTbl1 t1, OraTbl2 t2
WHERE
t1.tbl1ID = t2.tbl2ID
AND t1.val2 in (74679, 74687)
AND t2.EFFECTIVE_DATE = TO_DATE(''09/02/2013'',''MM/DD/YYYY'')')
)

Thanks,
kowalsky

kowalsky

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2013-09-03 : 14:05:23

There might be a better way, but you could put the whole thing into a string and execute it as dynamic SQL:

declare @SQL varchar(max);
declare @number varchar(max);
set @number = 1;

SELECT @sql 
= 'SELECT * , GETDATE(), ''Y''
FROM OPENQUERY([mySQLServerLinkedServer],
''SELECT	top 10 Number
FROM		master.dbo.spt_values
WHERE		Type = ''''P''''
AND			Number=' + @number + '''
) as z'

EXEC(@sql);

kowalsky
Starting Member

29 Posts

Posted - 2013-09-03 : 14:33:37

Thanks lazerath,
I'll try your suggestion, if I only nail the quotes ...

Thanks,

kowalsky

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources