You are here:

MS SQL/combining multiple select statements for a stored procedure

Advertisement


Question
Hello I am writing a stored procedure that should produce a series of count variables that I can then use for an if/else statement

Here is the stored procedure as it is:

/****** Object: StoredProcedure [dbo].[patientIdCheckBaselinePsoriasis] Script Date: 03/06/2014 15:24:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[patientIdCheckBaselinePsoriasis]
-- Add the parameters for the stored procedure here
@patientId INTEGER
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT count(baseline_psoriasis.patientId) as patientidcheck_baseline
FROM baseline_psoriasis
WHERE patientId = @patientId

SELECT count(biologics_psoriasis.patientid) as patientidcheck_biologics
FROM biologics_psoriasis
WHERE patientId = @patientId

SELECT count(bstop_sample_check.patientid) as patientidcheck_bstopsamplecheck
FROM bstop_sample_check
WHERE patientId = @patientId

SELECT count(core_sample_check.patientid) as patientidcheck_coresamplecheck
FROM core_sample_check
WHERE patientId = @patientId

SELECT count(comorbidities_psoriasis.patientid) as patientidcheck_comorb
FROM comorbidities_psoriasis
WHERE patientId = @patientId

SELECT count(conventional_psoriasis.patientid) as patientidcheck_conventional
FROM conventional_psoriasis
WHERE patientId = @patientId

SELECT count(therapy_psoriasis.patientid) as patientidcheck_therapy
FROM therapy_psoriasis
WHERE patientId = @patientId
END

This doesn't work as only the first select statement is processed and accurately gives me a value of '1'. The others are completely blank.
I've tried using UNION, which didn't work and I then tried UNION ALL, which gave the right results but instead of several columns with one row of data I got one column with several rows of 1's and 0's.
I'm sure there is a way of combining these select statements into one stored procedure using a JOIN but I'm not sure how and so far my attempts have been unsuccessful. The alternative is obviously using several stored procedures, but as I am going to have to do this on many occasions I would rather they all be executed by one stored procedure. If it helps the @patientid is an integer.

Any help would be much appreciated

Regards,

Tejus

Answer
Hi Tejus

I guess you want to add count for all the queries, if that's true then there are multiple ways to do the same.

1. Declare a local variable and use that in every query
DECLARE @cnt INT = 0
SELECT @cnt = @cnt + COUNT(1) FROM Query1
SELECT @cnt = @cnt + COUNT(1) FROM Query2
SELECT @cnt = @cnt + COUNT(1) FROM Query3

2. Use UNION ALL and get SUM on top

SELECT SUM (Cnt)
FROM
(
SELECT Count (1) AS Cnt FROM Query1
UNION ALL
SELECT Count (1) AS Cnt FROM Query2
UNION ALL
SELECT Count (1) AS Cnt FROM Query3
) as A

Thanks  

MS SQL

All Answers


Answers by Expert:


Ask Experts

Volunteer


Mohit Nayyar

Expertise

Microsoft SQL Server: T-SQL development (stored procedures, triggers, functions), Database optimization, performance tuning, high availability (Clustering, Log Shipping, Mirroring, Replication), scalability, SQL Server migration (Sybase/Oracle to SQL Server), SSIS/DTS, Data Warehousing (Kimball Methodology), ETL (SSIS), Cube (SSAS), Reporting Services (SSRS), database modeling, database administration, Security implementation and typically anything related to Microsoft SQL Server.

Experience

With over 10 years of experience in Database Administration, Development, Business Intelligence solutions and managing enterprise level database solutions based on Microsoft SQL Server.
Publications
IT Magazine: ASP, SQL

Publications
IT Magazine: ASP, SQL
SQLServerCentral.com

Education/Credentials
MCA (Masters)

Awards and Honors
Brainbench Certified: RDBMS
MCTS: DBA/Developer/BI for SQL Server 2005/SQL Server 2008
MCITP: DBA/Developer/BI for SQL Server 2005/SQL Server 2008
MCDBA: SQL Server 2000
MCP: SQL Server 6.5

©2016 About.com. All rights reserved.