Post reply

In sql 2008 - One Stored procedure return data (select statement) into another stored procedure

  • November 14, 2014 at 6:51 am

    #295102

    HI,

    I am new to work on Sql server,

    I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.

    Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temparay table in another procedure or some thing.

    Please help me..

    Thanks advance..

  • November 14, 2014 at 7:50 am

    #1758599

    how can another procedure consume the data of a dynamic procedure, if it doesn't know what columns would be included?

    typically, you create a temp table and insert into it, ie:

    CREATE TABLE [dbo].[#results] (

    [ic_recno] INT NOT NULL,

    [ic_file_type] INT NOT NULL,

    [ic_file_name] VARCHAR(200) NOT NULL,

    [ic_last_retrieved] DATETIME NOT NULL,

    [ic_last_executed] DATETIME NOT NULL,

    [ic_file_size] INT NOT NULL,

    [ic_file_dt] DATETIME NOT NULL,

    [ic_include] INT NOT NULL,

    [ic_imported] INT NOT NULL,

    [ic_interval] INT NOT NULL,

    [ic_file_key] VARCHAR(50) NULL)

    INSERT INTO #Results

    EXEC MyProcedure

    so it would really require you to know the columns/shape oif the data in advance.

    you can do it a little more dynamically with a linked serer / loopback server, but i doubt that's what you really need.

    --note that the . will map to teh current default instance on a server, so it might be .\SQL2008R2, for example

    EXEC master.dbo.sp_addlinkedserver @server = N'LOOPBACK',@srvproduct = N'', @datasrc = N'.', @provider = N'SQLOLEDB';

    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LOOPBACK',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL;

    SELECT theColumnsYouNeed into #tmp

    FROM OPENQUERY(LOOPBACK, 'EXEC dbo.usp_GetAnalysis @mode = ''mode'' , @group = ''group'' ')

    SELECT * into #tmp2

    FROM OPENROWSET('SQLOLEDB','Server=.;Trusted_Connection=Yes;Database=Master',

    'Set FmtOnly OFF; EXEC dbo.sp_Who')

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply