October 3, 2014 at 11:05 am
I would like to call a Stored Procedure with @variable from within a Stored Procedure. It works perfectly with-out the @variable as you might guess. The problem is I have to send a @variable. Here's what I have so far.
This first example uses a hard-coded contact.id number and the procedure runs perfectly!
I would like to use the contact.id instead. Help...
Select
contacts.id,
contacts.name,
(Select * FROM OPENQUERY(MyServer,'EXEC [MyDatabase].[dbo].[GetContactsRoles] 123456')) as [ContactRoles]
From
Contacts
....
October 3, 2014 at 12:35 pm
Based on the limited info you've provided...hopefully this helps...
--Build a temp table to hold the contact ID
Select
--1 as ID
contacts.id
into #TempContactID
From Contacts;
--Declare our variable to hold the dynamic SQL statement that we will build
Declare @sql varchar (max)
--Declare our variable to hold the contact id we want to pass into the [MyDatabase].[dbo].[GetContactsRoles] function
Declare @ContactID varchar(15)
--Set the contact id variable to the value from our temp table
Set@ContactID = (select Id from #TempContactID) --This assumes a single row in #TempContactID
--Build our dynamic SQL string
Set@sql = '
Select
contacts.id,
contacts.name,
(Select * FROM OPENQUERY(MyServer,''EXEC [MyDatabase].[dbo].[GetContactsRoles] ' +@ContactID +' '')) as [ContactRoles]
From Contacts
'
--Print our string for de bugging
Print (@SQL)
--Execute the SQL
EXEC (@SQL)
October 3, 2014 at 6:17 pm
Nice code! I have a very large SP and don't wont to make it a dynamic sql though. Maybe I could run the 1st SP and insert it into a table then run the 2nd SP and update the one column. What do ya think?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply