how to return a recordset execute by SP within the SP

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » how to return a recordset execute by SP...

how to return a recordset execute by SP within the SP

Rate Topic

Display Mode

Topic Options

Author

Message

selvalingam1978

Posted Friday, May 23, 2008 4:04 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, April 08, 2009 1:07 AM
Points: 1, Visits: 6

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.

Post #505723

raja_saminathan

Posted Friday, May 23, 2008 4:15 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 25, 2010 10:52 PM
Points: 89, Visits: 273

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

Rajesh

Post #505733

« Prev Topic | Next Topic »

Permissions