Solved

how to call stored procedure from select script?

Posted on 2004-04-27
5
3,078 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

734 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