Solved

how to call stored procedure from select script?

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now