SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Return fields from a select to a proc
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stevenandler
Starting Member

USA
42 Posts

Posted - 01/30/2013 :  14:32:04  Show Profile  Reply with Quote
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

USA
37134 Posts

Posted - 01/30/2013 :  14:42:21  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
42 Posts

Posted - 01/30/2013 :  15:30:49  Show Profile  Reply with Quote
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

USA
37134 Posts

Posted - 01/30/2013 :  16:50:34  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 01/30/2013 :  17:19:23  Show Profile  Reply with Quote
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

USA
42 Posts

Posted - 01/30/2013 :  17:52:05  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000