OPENROWSET with Stored Proc pass in parameters

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)

OPENROWSET with Stored Proc pass in parameters

Author

Topic

SoftFox
Starting Member

42 Posts

Posted - 2008-02-13 : 03:57:44

I am using OPENROWSET to select from the result set of a stored proc as below:

SELECT Field1, Field2
FROM
OPENROWSET('SQLOLEDB','Data Source=LGS-SQL2005;Trusted_Connection=yes;
Integrated Security=SSPI','execute [headway land Charges].dbo.LCGetAllQuestions 1')

The stored proc (LCGetAllQuestions) requires one input parameter which above is passed as the value 1. I need to be able to pass in the value of the parameter dynamically, which i have tried to do below by concatenating the value of the parameter to the query string as below:

SELECT Field1, Field2
FROM
OPENROWSET('SQLOLEDB','Data Source=LGS-SQL2005;Trusted_Connection=yes;
Integrated Security=SSPI','execute [headway land Charges].dbo.LCGetAllQuestions' + '1')

however this gives an incorrect syntax error. I have also tried passing in the query as a string as below:

DECLARE @ExecStr NVARCHAR(300)
SET @ExecStr = 'execute [headway land Charges].dbo.LCGetAllQuestions 1'

SELECT Field1, Field2
FROM
OPENROWSET('SQLOLEDB','Data Source=LGS-SQL2005;Trusted_Connection=yes;
Integrated Security=SSPI',@ExecStr)

but again this raises a syntax error.

does anyone know a way round this?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-13 : 05:00:58

Make your entire SELECT statement dynamic.

DECLARE @ExecStr NVARCHAR(2000)

set @ExecStr = 'SELECT Field1, Field2
FROM
OPENROWSET(''SQLOLEDB'',''Data Source=LGS-SQL2005;Trusted_Connection=yes;
Integrated Security=SSPI,''execute [headway land Charges].dbo.LCGetAllQuestions ' + '1' + ')'

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources