Solved

how to call stored procedure from select script?

Posted on 2004-04-27
5
3,080 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:young_nacs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 6

Expert Comment

by:jchopde
ID: 10934555
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
 

Author Comment

by:young_nacs
ID: 10934715
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
 
LVL 4

Accepted Solution

by:
BulZeyE earned 500 total points
ID: 10935183
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
 

Author Comment

by:young_nacs
ID: 10939016
It works beautifully.. Many thanks.!!
0
 

Expert Comment

by:isacas69
ID: 11093941
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

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question