asked on

how to call stored procedure from select script?

I have a stored procedure and I like to call this from select script.
This stored procedure returns a record with one column wichi is a recordset by select command inside.
How to call a stored procedure like below:
select ID, (exec sp_return_name_recordset 1000) as name
from nameTable
where nameTable.ID='1000'

Stored procedure accepts ID and returns a records of name value.
how to combine select and stored procedure record in one record?

Young

jchopde

Is converting the stored proc into a user-defined function an option ? If you are running SQL 2000, you can call UDFs in SELECT statements.

young_nacs

ASKER

I did not think UDF yet. here is my sp. you may take a look and tell me how to convet to UDF if I have to.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

-- =============================================
-- Create procedure basic template
-- =============================================
-- creating the store procedure
IF EXISTS (SELECT name
       FROM sysobjects
       WHERE name = N'sp_merge_PhysicianName'
       AND        type = 'P')
DROP PROCEDURE sp_merge_PhysicianName
GO

CREATE Procedure sp_merge_PhysicianName
(@PatientID BIGINT, @Spliter varchar(10))
AS

DECLARE my_cursor CURSOR READ_ONLY
      FOR SELECT DISTINCT patientid, physicianid
      FROM PATIENT_PhysicianTbl
      WHERE patientid = @PatientID

DECLARE @name nvarchar(4000),
@list nvarchar(4000),
@pid bigint,
@did bigint

set @list = ''

OPEN my_cursor

FETCH NEXT FROM my_cursor INTO @PID, @DID
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN

SET @name = (SELECT firstname + ' ' + lastname FROM Physiciantbl
WHERE physicianid = @did)

IF(@list = '')
      SET @list = @name
ELSE
      SET @list = @list + @Spliter + @name
END
FETCH NEXT FROM my_cursor INTO @PID, @DID
END

-- PRINT @PID, @list
-- SELECT @PID as pid, @list as docName FROM Patient_physicianTbl where PatientID = @PatientID
--      Group by patientid

      SELECT @list as docName
-- RETURN @list

CLOSE my_cursor
DEALLOCATE my_cursor

GO

ASKER CERTIFIED SOLUTION

BulZeyE

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

young_nacs

ASKER

It works beautifully.. Many thanks.!!

isacas69

I have a same problem.

I have many store procedure but inside of main procedure I call other procedure with a variable.

select @A = name_procedure from tables_names_procedures
where code_procedure = 1

exec @A

I need get the answerof @A procedure. How I can do it?

@B = exec @A?

Thank You.