1

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]
    (@ID VARCHAR(20))
AS 
    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?

Many thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
L.Nelson
  • 21
  • 3
  • 1
    Are you modifying the stored procedure or not? If not, then put the results into a temp table and join that temp table with whatever you want. – SS_DBA Dec 07 '17 at 18:57
  • Check the answers here, they're relevant to what you're trying to do: https://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table – digital.aaron Dec 07 '17 at 19:22
  • Hi Marc - could you provide a sample? I can change whatever is necessary. – L.Nelson Dec 07 '17 at 20:01

0 Answers0