I am trying to select from a PostgreSQL database to SQL Server 2008 over a linked server. I have created a stored procedure (dynamic using
OpenQuery) which returns what I am wanting. Now, though, I need to join the results to another table and keep working. Is there a better way?
CREATE PROCEDURE [dbo].[p_NB_Signup_Details]
SET NOCOUNT ON
DECLARE @stmt NVARCHAR(MAX)
SET @stmt = 'select * from OPENQUERY( nb_Remote, ''select s.Id , s.External_Id
, s.First_Name , s.Middle_Name , s.Last_Name
, s.Phone , s.Email_Opt_In , s.do_not_call , s.do_not_contact , s.is_deceased
from schema.table s
where s.id = cast(' + @ID + ' as integer) '')'
EXEC sp_executesql @stmt
I can call it using
exec PCIV_Nov2017.dbo.p_NB_Signup_Details '3308698'
How can I select from it and join to another table?