Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

how to call stored procedure from select script?

Posted on 2004-04-27
5
Medium Priority
?
3,087 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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

722 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