passing params to SP using 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

General SQL Server Forums

New to SQL Server Programming

passing params to SP using OPENQUERY

Author

Topic

gvmk27
Starting Member

44 Posts

Posted - 2014-10-08 : 11:03:13

How to pass parameter values to Stored Procedure using Openquery ?

DECLARE @CenterNumber nvarchar(8)
DECLARE @CenterName nvarchar(100)

DECLARE @tblLeads table(
LeadCount int)

SET @CenterNumber = '98454152'
SET @CenterName = 'neck'

INSERT INTO @tblLeads
SELECT * FROM OPENQUERY([DEV1],'[Leads].dbo.usp_SearchLeads_ForCenterScheduler @CenterID ='' + @CenterNumber + '', @Center ='' + @CenterName + ''')

SELECT * FROM @tblLeads

Thanks for your help/
MOhan

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-10-08 : 11:45:37

Your approach seems basically right but in need of a small tweak. If this isn't right, could you give details of what you are experiencing (e.g., Syntax error, no results, wrong results, run-time error, etc.):

SELECT * FROM OPENQUERY([DEV1],'[Leads].dbo.usp_SearchLeads_ForCenterScheduler @CenterID =''' + @CenterNumber + ''', @Center =''' + @CenterName + ''')

No amount of belief makes something a fact. -James Randi

gvmk27
Starting Member

44 Posts

Posted - 2014-10-08 : 11:56:16

Thanks for your reply.

Now I'm getting below error after I modified as you recommended.

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '+'. (this is at first + sign)

if I just pass the values as below, I can see the results
SELECT * FROM OPENQUERY([DEV1],'[Leads].dbo.usp_SearchLeads_ForCenterScheduler @CenterID =''98454152'', @Center =''neck''')

If I run like this, I don't see any results
SELECT * FROM OPENQUERY([DEV1],'[Leads].dbo.usp_SearchLeads_ForCenterScheduler @CenterID ='' + @CenterNumber + '', @Center ='' + @CenterName + ''')

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-10-08 : 19:12:53

1) I don't make use of OPENQUERY enough to know but perhaps you can't dynamically build the command string. The syntax seems to indicate that it is a single constant string.
2) What is the data type of @CenterID? If it is an integer, you don't need the single quotes around the number being passed in.
3) For issues like these, I try to get the string right, print it and see if it will run in stand-alone mode. It sounds like that is what you are doing already

No amount of belief makes something a fact. -James Randi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources