Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Return fields from a select to a proc

Author  Topic 

stevenandler
Starting Member

42 Posts

Posted - 2013-01-30 : 14:32:04
I am trying to figure out how to return fields from a select statement performed in one Stored Procedure to another

This is a snippet from the calling Stored Prcedure:
EXECUTE OGEN.VALIDATE_PATIENT @VALIDATED OUTPUT, @LMRN, @LASTNAME, @FIRSTNAME, @SSN, @DOB
PRINT @VALIDATED


This is the complete code from the called Stored Precedure:
ALTER PROCEDURE [OGEN].[VALIDATE_PATIENT]
(@VALIDATED INT OUTPUT, @MRN CHAR(10), @LAST_NAME VARCHAR(30), @FIRST_NAME VARCHAR(30),@SSN CHAR(9), @DOB datetime)
AS
BEGIN

SELECT PAT_NUMBER FROM OGEN.GEN_M_PATIENT_MAST WHERE ((@MRN=MRN AND UPPER(@LAST_NAME) = LAST_NAME) OR (@MRN=MRN AND @DOB = BIRTH_DATE) OR
(@MRN=MRN AND UPPER(@FIRST_NAME) = FIRST_NAME))
SET @VALIDATED = @@ROWCOUNT

END


I would like to return the values of PATIENT_ID and FACILITY_KEY

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-01-30 : 14:42:21
Are you able to modify the called stored procedure as the two columns you want to return aren't in it yet?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

stevenandler
Starting Member

42 Posts

Posted - 2013-01-30 : 15:30:49
I added the following code to the stored procedure
ALTER PROCEDURE [OGEN].[VALIDATE_PATIENT]
(@VALIDATED INT OUTPUT, @MRN CHAR(10), @LAST_NAME VARCHAR(30), @FIRST_NAME VARCHAR(30),@SSN CHAR(9), @DOB datetime)
AS
BEGIN
DECLARE @PATIENT_ID INT
DECLARE @FACILITY_KEY CHAR(4)
-- 1st attempt
SELECT * FROM OGEN.GEN_M_PATIENT_MAST WHERE ((@MRN=MRN AND UPPER(@LAST_NAME) = LAST_NAME) OR (@MRN=MRN AND @DOB = BIRTH_DATE) OR
(@MRN=MRN AND UPPER(@FIRST_NAME) = FIRST_NAME))
SET @VALIDATED = @@ROWCOUNT
SET @PATIENT_ID = PATIENT_ID
SET @FACILITY_KEY = FACILITY_KEY
END

when I attempt to alter the procedure, it fails with the following messages:
Msg 207, Level 16, State 1, Procedure VALIDATE_PATIENT, Line 17
Invalid column name 'PATIENT_ID'.
Msg 207, Level 16, State 1, Procedure VALIDATE_PATIENT, Line 18
Invalid column name 'FACILITY_KEY'.

I am positive that these two fields are defined in the table.



Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-01-30 : 16:50:34
SELECT @PATIENT_ID = PATIENT_ID, @FACILITY_KEY = FACILITY_KEY
FROM OGEN.GEN_M_PATIENT_MAST
WHERE ((@MRN=MRN AND UPPER(@LAST_NAME) = LAST_NAME) OR (@MRN=MRN AND @DOB = BIRTH_DATE) OR
(@MRN=MRN AND UPPER(@FIRST_NAME) = FIRST_NAME))

SET @VALIDATED = @@ROWCOUNT


Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-01-30 : 17:19:23
You need to be aware that if @@rowcount > 1 then more than one row satisfied the condition in your WHERE clause. BUT @facility_key and @patient_id will be for just one of those rows.

Be One with the Optimizer
TG
Go to Top of Page

stevenandler
Starting Member

42 Posts

Posted - 2013-01-30 : 17:52:05
Thank you Tara and TG. It works fine. I will probably add the max aggregate to ensure I get only one row of data
Go to Top of Page
   

- Advertisement -