Using Union All in a stored 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 2005 Forums

Transact-SQL (2005)

Using Union All in a stored proc

Author

Topic

rbemiller
Starting Member

4 Posts

Posted - 2010-05-28 : 15:16:09

I have the following Stored Proc that works...basically it provides a dummy
record for me to work with. I want to ad another record to this. I thought
I could do something like a UNION ALL to make two records but it doesn't
work. What's the easiest way to do this? See original SP, then edited SP
below that... Thanks!

-- Begin Original SP --
USE [CODS]
GO
/****** Object: StoredProcedure [dbo].[AHIS_GET_PATIENT_INFORMATION]
Script Date: 05/28/2010 13:35:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[AHIS_GET_PATIENT_INFORMATION]
@MRNID varchar(30)=null, --MRN #
@patientFirstName varchar(50)=null,
@patientLastName varchar(50)=null,
@patientMiddleInitial varchar(2)=null,
@companyID varchar(50)=null,
@birthDate varchar(12)=null,
@gender varchar(50)=null,
@admitDate varchar(12) =null,
@facilityID varchar(45)=null

AS
BEGIN
SET NOCOUNT ON;
--DEMO Purposes
SELECT '1' AS MRN,
'Male' AS PatientSex,
'Sample' AS PatientLastName,
'Patient' AS PatientFirstName,
'W' AS PatientMiddleInitial,
'(123)123-1231' AS PatientPhoneNumber,
'sample@client.com' AS PatientEmailAddress,
'1234 Test Patient Lane' AS PatientAddress,
'Patientville' AS PatientCity,
'Indiana' AS PatientState,
'12345' AS PatientZip,
'USA' AS PatientCountryRegion,
'2010-01-19' as ADMITDATE,
'2009-11-20' AS PatientBirthdate --[YYYY-MM-DD]
DECLARE @sql VARCHAR(4000)
DECLARE @newFacility VARCHAR(10)

END
-- End Original SP --

-- Begin Edited SP --
USE [CODS]
GO
/****** Object: StoredProcedure [dbo].[AHIS_GET_PATIENT_INFORMATION]
Script Date: 05/28/2010 13:35:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[AHIS_GET_PATIENT_INFORMATION]
@MRNID varchar(30)=null, --MRN #
@patientFirstName varchar(50)=null,
@patientLastName varchar(50)=null,
@patientMiddleInitial varchar(2)=null,
@companyID varchar(50)=null,
@birthDate varchar(12)=null,
@gender varchar(50)=null,
@admitDate varchar(12) =null,
@facilityID varchar(45)=null

AS
BEGIN
SET NOCOUNT ON;
--DEMO Purposes
SELECT * FROM (SELECT '1' AS MRN,
'Male' AS PatientSex,
'Sample' AS PatientLastName,
'Patient' AS PatientFirstName,
'W' AS PatientMiddleInitial,
'(123)123-1231' AS PatientPhoneNumber,
'sample@client.com' AS PatientEmailAddress,
'1234 Test Patient Lane' AS PatientAddress,
'Patientville' AS PatientCity,
'Indiana' AS PatientState,
'12345' AS PatientZip,
'USA' AS PatientCountryRegion,
'2010-01-19' as ADMITDATE,
'2009-11-20' AS PatientBirthdate --[YYYY-MM-DD]
DECLARE @sql VARCHAR(4000)
DECLARE @newFacility VARCHAR(10)

UNION ALL

SELECT '2' AS MRN,
'Female' AS PatientSex,
'Smith' AS PatientLastName,
'Sarah' AS PatientFirstName,
'J' AS PatientMiddleInitial,
'(123)55555-1231' AS PatientPhoneNumber,
'sarah@client.com' AS PatientEmailAddress,
'432 Tester Dr.' AS PatientAddress,
'Liberty City' AS PatientCity,
'Michigan' AS PatientState,
'12543' AS PatientZip,
'USA' AS PatientCountryRegion,
'2010-03-14' as ADMITDATE,
'2000-09-26' AS PatientBirthdate --[YYYY-MM-DD]
DECLARE @sql VARCHAR(4000)
DECLARE @newFacility VARCHAR(10)
)

END
-- End Edited SP --

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-28 : 15:26:25

The two look the same to me. I don't see where you tried UNION ALL.

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

Subscribe to my blog

rbemiller
Starting Member

4 Posts

Posted - 2010-05-28 : 15:33:47

You're right. My bad, pasted the wrong thing. I have edited it with the correct code now. Thanks for pointing that out!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-28 : 15:38:08

I don't understand the purpose of @sql and @newfacility, but they shouldn't be inside the select query.

Here you go:
SELECT '1' AS MRN,
'Male' AS PatientSex,
'Sample' AS PatientLastName,
'Patient' AS PatientFirstName,
'W' AS PatientMiddleInitial,
'(123)123-1231' AS PatientPhoneNumber,
'sample@client.com' AS PatientEmailAddress,
'1234 Test Patient Lane' AS PatientAddress,
'Patientville' AS PatientCity,
'Indiana' AS PatientState,
'12345' AS PatientZip,
'USA' AS PatientCountryRegion,
'2010-01-19' as ADMITDATE,
'2009-11-20' AS PatientBirthdate --[YYYY-MM-DD]

UNION ALL

SELECT '2' AS MRN,
'Female' AS PatientSex,
'Smith' AS PatientLastName,
'Sarah' AS PatientFirstName,
'J' AS PatientMiddleInitial,
'(123)55555-1231' AS PatientPhoneNumber,
'sarah@client.com' AS PatientEmailAddress,
'432 Tester Dr.' AS PatientAddress,
'Liberty City' AS PatientCity,
'Michigan' AS PatientState,
'12543' AS PatientZip,
'USA' AS PatientCountryRegion,
'2010-03-14' as ADMITDATE,
'2000-09-26' AS PatientBirthdate --[YYYY-MM-DD]

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

Subscribe to my blog

rbemiller
Starting Member

4 Posts

Posted - 2010-05-28 : 15:57:54

I don't understand them either, I didn't design this SP, just have to modify it. So I took them out of the select statement and put them at the bottom, still get error. The I commented them out, as you see here, and I get error "incorrect syntax near END". Thoughts?

ALTER PROCEDURE [dbo].[AHIS_GET_PATIENT_INFORMATION]
@MRNID varchar(30)=null, --MRN #
@patientFirstName varchar(50)=null,
@patientLastName varchar(50)=null,
@patientMiddleInitial varchar(2)=null,
@companyID varchar(50)=null,
@birthDate varchar(12)=null,
@gender varchar(50)=null,
@admitDate varchar(12) =null,
@facilityID varchar(45)=null

AS
BEGIN
SET NOCOUNT ON;
--DEMO Purposes
SELECT * FROM (SELECT '1' AS MRN,
'Male' AS PatientSex,
'Sample' AS PatientLastName,
'Patient' AS PatientFirstName,
'W' AS PatientMiddleInitial,
'(123)123-1231' AS PatientPhoneNumber,
'sample@client.com' AS PatientEmailAddress,
'1234 Test Patient Lane' AS PatientAddress,
'Patientville' AS PatientCity,
'Indiana' AS PatientState,
'12345' AS PatientZip,
'USA' AS PatientCountryRegion,
'2010-01-19' as ADMITDATE,
'2009-11-20' AS PatientBirthdate --[YYYY-MM-DD]

UNION ALL

SELECT '2' AS MRN,
'Female' AS PatientSex,
'Smith' AS PatientLastName,
'Sarah' AS PatientFirstName,
'J' AS PatientMiddleInitial,
'(123)55555-1231' AS PatientPhoneNumber,
'sarah@client.com' AS PatientEmailAddress,
'432 Tester Dr.' AS PatientAddress,
'Liberty City' AS PatientCity,
'Michigan' AS PatientState,
'12543' AS PatientZip,
'USA' AS PatientCountryRegion,
'2010-03-14' as ADMITDATE,
'2000-09-26' AS PatientBirthdate --[YYYY-MM-DD]

)
--DECLARE @sql VARCHAR(4000)
--DECLARE @newFacility VARCHAR(10)

END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-28 : 16:17:02

ALTER PROCEDURE [dbo].[AHIS_GET_PATIENT_INFORMATION]
@MRNID varchar(30)=null, --MRN #
@patientFirstName varchar(50)=null,
@patientLastName varchar(50)=null,
@patientMiddleInitial varchar(2)=null,
@companyID varchar(50)=null,
@birthDate varchar(12)=null,
@gender varchar(50)=null,
@admitDate varchar(12) =null,
@facilityID varchar(45)=null

AS

SET NOCOUNT ON;

SELECT '1' AS MRN,
'Male' AS PatientSex,
'Sample' AS PatientLastName,
'Patient' AS PatientFirstName,
'W' AS PatientMiddleInitial,
'(123)123-1231' AS PatientPhoneNumber,
'sample@client.com' AS PatientEmailAddress,
'1234 Test Patient Lane' AS PatientAddress,
'Patientville' AS PatientCity,
'Indiana' AS PatientState,
'12345' AS PatientZip,
'USA' AS PatientCountryRegion,
'2010-01-19' as ADMITDATE,
'2009-11-20' AS PatientBirthdate --[YYYY-MM-DD]

UNION ALL

SELECT '2' AS MRN,
'Female' AS PatientSex,
'Smith' AS PatientLastName,
'Sarah' AS PatientFirstName,
'J' AS PatientMiddleInitial,
'(123)55555-1231' AS PatientPhoneNumber,
'sarah@client.com' AS PatientEmailAddress,
'432 Tester Dr.' AS PatientAddress,
'Liberty City' AS PatientCity,
'Michigan' AS PatientState,
'12543' AS PatientZip,
'USA' AS PatientCountryRegion,
'2010-03-14' as ADMITDATE,
'2000-09-26' AS PatientBirthdate --[YYYY-MM-DD]
GO

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

Subscribe to my blog

Kristen
Test

22859 Posts

Posted - 2010-05-29 : 01:31:49

Tara's will work just fine, although there might be a reason for having the UNION as a sub-select (as you have written it) - e.g. to sort the "whole" (indicated in Blue)

If you do that you MUST provide an Alias name for the results "table" (indicted in Red) from the sub-select thus:


SELECT * FROM
(
    SELECT '1' AS MRN,
           'Male' AS PatientSex,
           ...
    UNION ALL
    SELECT '2' AS MRN,
           'Female' AS PatientSex,
           ...
) AS X
ORDER BY MRN DESC

rbemiller
Starting Member

4 Posts

Posted - 2010-06-01 : 08:40:31

I appreciate all of the help. I went with Tara's solution and it's working well. Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-01 : 12:37:58

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

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources