how to return a recordset execute by SP within the SP

  • I am using Exec SP B within a SP A. The Exec SP return a recordset.

    If i execute SP A its not returning the recordset of SP B?

    How can i acheive it?

    Any help will greatly help me.

    ====== SQL Text =====

    Alter PROCEDURE dbo.sp_local_kpi_p2010_get_tm_data

    --declare

    @header_id int

    AS

    Declare

    @StartTime datetime,

    @asset_type nvarchar(20),

    @mill_id nvarchar(12),

    @machine_line_id nvarchar(100),

    @machine_line_type_id nvarchar(100)

    Set ANSI_WARNINGS OFF

    set ANSI_Padding ON

    set ANSI_Warnings ON

    set quoted_identifier ON

    set ANSI_Null_Dflt_On ON

    Set NOCOUNT ON

    SET @StartTime = '2008-01-01 07:00:00'

    SET @header_id =10653

    SET @asset_type = 'TM'

    SET @mill_id = 'BAR'

    SET @machine_line_type_id = 'LDC'

    SET @machine_line_id = '#1'

    --IF (select count(*) from kpi_form_detail_tissue where header_id=@header_id)=0

    --Begin

    Declare @PIMSPU_ID int, @DataSource varchar(20), @dbname varchar(20), @UserName varchar(20), @Password varchar(20)

    Declare @spname varchar(50)

    Select @PIMSPU_ID=Pimspu_id,@DataSource=Datasource,@dbname=dbname,@UserName=Username,@Password=[Password],@spname=Spname

    from kpi_Map_PIMS_Machineline where assest_type_id=@asset_type and mill_id=@mill_id and machine_line_type_id=@machine_line_type_id and Machine_line_id=@machine_line_id

    Declare @ServerName Varchar(1000)

    Declare @Sql Varchar(1000)

    Select @ServerName = 'OPENDATASOURCE('''

    Select @ServerName = @ServerName + 'SQLOLEDB''' + ',''' + 'Data Source=' + rtrim(@datasource) +

    + ';User ID=' + rtrim(@UserName) + ';Password=' + rtrim(@Password)

    Select @ServerName = @ServerName + ''')'

    select @Sql ='Exec ' + @ServerName +'.'+ @dbname + '.dbo.'+ @spname +' ''' + convert(varchar(20), @StartTime) + ''',' + convert(varchar,@PIMSPU_ID) +','+'''ENG'''

    execute (@Sql)

    ======

    I am executing another SP inside the above SP which fetches the data from another server.

    When a compile the SP and execute it the recorset retuned by the SP used within is not retrived in the mail SP.

    Could u please help me out.

  • can you post your query details,so we will come to know where you went wrong?

    Rajesh

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

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