Home SQL Server stored procedure using OpenQuery - select from as table
Reply: 0

SQL Server stored procedure using OpenQuery - select from as table

L.Nelson
1#
L.Nelson Published in 2017-12-07 18:21:49Z

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

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.301241 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO