Return fields from a select to a proc

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

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.

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

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources