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
young_nacsAsked:
Who is Participating?
 
BulZeyECommented:
Something like this would work:

CREATE function fn_merge_PhysicianName
(
  @PatientID BIGINT,
  @Spliter varchar(10)
) returns nvarchar(4000)
AS
BEGIN

  DECLARE @list nvarchar(4000)
     
  SELECT @list = COALESCE(@list + @Spliter,'') + Firstname + ' ' + Lastname
    FROM Physiciantbl pt WITH (NOLOCK)
   INNER JOIN PATIENT_PhysicianTbl ppt WITH (NOLOCK)
      ON pt.physicianid = ppt.physicianid
   WHERE ppt.patientid = @Patientid
 
  return @list

END

then do

select ID, fn_merge_PhysicianName(nameTable.ID) as name
from nameTable
where nameTable.ID='1000'
0
 
jchopdeCommented:
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.
0
 
young_nacsAuthor Commented:
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
0
 
young_nacsAuthor Commented:
It works beautifully.. Many thanks.!!
0
 
isacas69Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.