April 8, 2011 at 12:50 pm
USE [Clinical_Edu_2006]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[CalChestSum]
@DateFrom = N'1/6/2011 ',
@DateToIn = N'2/11/2011',
@cc = N'all'
SELECT 'Return Value' = @return_value
GO
Here is my sp:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[CalChestSum]
@DateFrom datetime,
@DateToIn datetime,
@cc varchar(220)
with recompile
AS
SET NOCOUNT ON
begin
declare @DateTo datetime
select @DateTo = @DateToIn+1
if @cc='All'
begin
delete from [Clinical_Edu_2006].[dbo].[tblSChestTubeFeedback]
INSERT INTO [Clinical_Edu_2006].[dbo].[tblSChestTubeFeedback] ( [Indication] ) VALUES ('ExcellentlyMet')
INSERT INTO [Clinical_Edu_2006].[dbo].[tblSChestTubeFeedback] ( [Indication] ) VALUES ('SatisfactorilyMet')
INSERT INTO [Clinical_Edu_2006].[dbo].[tblSChestTubeFeedback] ( [Indication] ) VALUES ('WasnotMet')
INSERT INTO [Clinical_Edu_2006].[dbo].[tblSChestTubeFeedback] ( [Indication] ) VALUES ('WellMet')
UPDATE [Clinical_Edu_2006].[dbo].[tblSChestTubeFeedback]
SET [IndicationC] =
(SELECT
Count([The learner is able to understand chest tubes and indications for a chest insertion]) as FCount
FROM Annual_Edu_2006.dbo.HREMP INNER JOIN
dbo.NursingFeedback ON Annual_Edu_2006.dbo.HREMP.[FULL NAME] = dbo.NursingFeedback.[Employee Name] AND
Annual_Edu_2006.dbo.HREMP.DOB = dbo.NursingFeedback.DOB WHERE [Submit Time] >= @DateFrom and [Submit Time]<@DateTo and [The learner is able to understand chest tubes and indications for a chest insertion]='ExcellentlyMet'
group by [The learner is able to understand chest tubes and indications for a chest insertion] ) where [tblSChestTubeFeedback].Indication='ExcellentlyMet'
UPDATE [Clinical_Edu_2006].[dbo].[tblSChestTubeFeedback]
SET [IndicationC] =
(SELECT
Count([The learner is able to understand chest tubes and indications for a chest insertion]) as FCount
FROM Annual_Edu_2006.dbo.HREMP INNER JOIN
dbo.NursingFeedback ON Annual_Edu_2006.dbo.HREMP.[FULL NAME] = dbo.NursingFeedback.[Employee Name] AND
Annual_Edu_2006.dbo.HREMP.DOB = dbo.NursingFeedback.DOB WHERE [Submit Time] >= @DateFrom and [Submit Time]<@DateTo and [The learner is able to understand chest tubes and indications for a chest insertion]='SatisfactorilyMet'
group by [The learner is able to understand chest tubes and indications for a chest insertion] ) where [tblSChestTubeFeedback].Indication='SatisfactorilyMet'
UPDATE [Clinical_Edu_2006].[dbo].[tblSChestTubeFeedback]
SET [IndicationC] =
(SELECT
Count([The learner is able to understand chest tubes and indications for a chest insertion]) as FCount
FROM Annual_Edu_2006.dbo.HREMP INNER JOIN
dbo.NursingFeedback ON Annual_Edu_2006.dbo.HREMP.[FULL NAME] = dbo.NursingFeedback.[Employee Name] AND
Annual_Edu_2006.dbo.HREMP.DOB = dbo.NursingFeedback.DOB WHERE [Submit Time] >= @DateFrom and [Submit Time]<@DateTo and [The learner is able to understand chest tubes and indications for a chest insertion]='WasnotMet'
group by [The learner is able to understand chest tubes and indications for a chest insertion] ) where [tblSChestTubeFeedback].Indication='WasnotMet'
UPDATE [Clinical_Edu_2006].[dbo].[tblSChestTubeFeedback]
SET [IndicationC] =
(SELECT
Count([The learner is able to understand chest tubes and indications for a chest insertion]) as FCount
FROM Annual_Edu_2006.dbo.HREMP INNER JOIN
dbo.NursingFeedback ON Annual_Edu_2006.dbo.HREMP.[FULL NAME] = dbo.NursingFeedback.[Employee Name] AND
Annual_Edu_2006.dbo.HREMP.DOB = dbo.NursingFeedback.DOB WHERE [Submit Time] >= @DateFrom and [Submit Time]<@DateTo and [The learner is able to understand chest tubes and indications for a chest insertion]='WellMet'
group by [The learner is able to understand chest tubes and indications for a chest insertion] ) where [tblSChestTubeFeedback].Indication='WellMet'
UPDATE [Clinical_Edu_2006].[dbo].[tblSChestTubeFeedback]
SET [Bedside Procedure & Interventions] =
(SELECT
Count([The learner understands the bedside procedure and nursing responsibilities when the chest tube is inserted at the bedside]) as FCount
FROM Annual_Edu_2006.dbo.HREMP INNER JOIN
dbo.NursingFeedback ON Annual_Edu_2006.dbo.HREMP.[FULL NAME] = dbo.NursingFeedback.[Employee Name] AND
Annual_Edu_2006.dbo.HREMP.DOB = dbo.NursingFeedback.DOB WHERE [Submit Time] >= @DateFrom and [Submit Time]<@DateTo and [The learner understands the bedside procedure and nursing responsibilities when the chest tube is inserted at the bedside]='ExcellentlyMet'
group by [The learner is able to understand chest tubes and indications for a chest insertion] ) where [tblSChestTubeFeedback].Indication='ExcellentlyMet'
UPDATE [Clinical_Edu_2006].[dbo].[tblSChestTubeFeedback]
SET [Bedside Procedure & Interventions] =
(SELECT
Count([The learner understands the bedside procedure and nursing responsibilities when the chest tube is inserted at the bedside]) as FCount
FROM Annual_Edu_2006.dbo.HREMP INNER JOIN
dbo.NursingFeedback ON Annual_Edu_2006.dbo.HREMP.[FULL NAME] = dbo.NursingFeedback.[Employee Name] AND
Annual_Edu_2006.dbo.HREMP.DOB = dbo.NursingFeedback.DOB WHERE [Submit Time] >= @DateFrom and [Submit Time]<@DateTo and [The learner understands the bedside procedure and nursing responsibilities when the chest tube is inserted at the bedside]='SatisfactorilyMet'
group by [The learner is able to understand chest tubes and indications for a chest insertion] ) where [tblSChestTubeFeedback].Indication='SatisfactorilyMet'
UPDATE [Clinical_Edu_2006].[dbo].[tblSChestTubeFeedback]
SET [Bedside Procedure & Interventions] =
(SELECT
Count([The learner understands the bedside procedure and nursing responsibilities when the chest tube is inserted at the bedside]) as FCount
FROM Annual_Edu_2006.dbo.HREMP INNER JOIN
dbo.NursingFeedback ON Annual_Edu_2006.dbo.HREMP.[FULL NAME] = dbo.NursingFeedback.[Employee Name] AND
Annual_Edu_2006.dbo.HREMP.DOB = dbo.NursingFeedback.DOB WHERE [Submit Time] >= @DateFrom and [Submit Time]<@DateTo and [The learner understands the bedside procedure and nursing responsibilities when the chest tube is inserted at the bedside]='WasnotMet'
group by [The learner is able to understand chest tubes and indications for a chest insertion] ) where [tblSChestTubeFeedback].Indication='WasnotMet'
UPDATE [Clinical_Edu_2006].[dbo].[tblSChestTubeFeedback]
SET [Bedside Procedure & Interventions] =
(SELECT
Count([The learner understands the bedside procedure and nursing responsibilities when the chest tube is inserted at the bedside]) as FCount
FROM Annual_Edu_2006.dbo.HREMP INNER JOIN
dbo.NursingFeedback ON Annual_Edu_2006.dbo.HREMP.[FULL NAME] = dbo.NursingFeedback.[Employee Name] AND
Annual_Edu_2006.dbo.HREMP.DOB = dbo.NursingFeedback.DOB WHERE [Submit Time] >= @DateFrom and [Submit Time]<@DateTo and [The learner understands the bedside procedure and nursing responsibilities when the chest tube is inserted at the bedside]='WellMet'
group by [The learner is able to understand chest tubes and indications for a chest insertion] ) where [tblSChestTubeFeedback].Indication='WellMet'
UPDATE [Clinical_Edu_2006].[dbo].[tblSChestTubeFeedback]
SET [Nursing Care] =
(SELECT
Count([Understands the nursing care responsibilities with chest tubes and the pleurvac drainage system]) as FCount
FROM Annual_Edu_2006.dbo.HREMP INNER JOIN
dbo.NursingFeedback ON Annual_Edu_2006.dbo.HREMP.[FULL NAME] = dbo.NursingFeedback.[Employee Name] AND
Annual_Edu_2006.dbo.HREMP.DOB = dbo.NursingFeedback.DOB WHERE [Submit Time] >= @DateFrom and [Submit Time]<@DateTo and [Understands the nursing care responsibilities with chest tubes and the pleurvac drainage system]='ExcellentlyMet'
group by [Understands the nursing care responsibilities with chest tubes and the pleurvac drainage system] ) where [tblSChestTubeFeedback].Indication='ExcellentlyMet'
UPDATE [Clinical_Edu_2006].[dbo].[tblSChestTubeFeedback]
SET [Nursing Care] =
(SELECT
Count([Understands the nursing care responsibilities with chest tubes and the pleurvac drainage system]) as FCount
FROM Annual_Edu_2006.dbo.HREMP INNER JOIN
dbo.NursingFeedback ON Annual_Edu_2006.dbo.HREMP.[FULL NAME] = dbo.NursingFeedback.[Employee Name] AND
Annual_Edu_2006.dbo.HREMP.DOB = dbo.NursingFeedback.DOB WHERE [Submit Time] >= @DateFrom and [Submit Time]<@DateTo and [Understands the nursing care responsibilities with chest tubes and the pleurvac drainage system]='SatisfactorilyMet'
group by [Understands the nursing care responsibilities with chest tubes and the pleurvac drainage system] ) where [tblSChestTubeFeedback].Indication='SatisfactorilyMet'
UPDATE [Clinical_Edu_2006].[dbo].[tblSChestTubeFeedback]
SET [Nursing Care] =
(SELECT
Count([Understands the nursing care responsibilities with chest tubes and the pleurvac drainage system]) as FCount
FROM Annual_Edu_2006.dbo.HREMP INNER JOIN
dbo.NursingFeedback ON Annual_Edu_2006.dbo.HREMP.[FULL NAME] = dbo.NursingFeedback.[Employee Name] AND
Annual_Edu_2006.dbo.HREMP.DOB = dbo.NursingFeedback.DOB WHERE [Submit Time] >= @DateFrom and [Submit Time]<@DateTo and [Understands the nursing care responsibilities with chest tubes and the pleurvac drainage system]='WasnotMet'
group by [Understands the nursing care responsibilities with chest tubes and the pleurvac drainage system] ) where [tblSChestTubeFeedback].Indication='WasnotMet'
UPDATE [Clinical_Edu_2006].[dbo].[tblSChestTubeFeedback]
SET [Nursing Care] =
(SELECT
Count([Understands the nursing care responsibilities with chest tubes and the pleurvac drainage system]) as FCount
FROM Annual_Edu_2006.dbo.HREMP INNER JOIN
dbo.NursingFeedback ON Annual_Edu_2006.dbo.HREMP.[FULL NAME] = dbo.NursingFeedback.[Employee Name] AND
Annual_Edu_2006.dbo.HREMP.DOB = dbo.NursingFeedback.DOB WHERE [Submit Time] >= @DateFrom and [Submit Time]<@DateTo and [Understands the nursing care responsibilities with chest tubes and the pleurvac drainage system]='WellMet'
group by [Understands the nursing care responsibilities with chest tubes and the pleurvac drainage system] ) where [tblSChestTubeFeedback].Indication='WellMet'
UPDATE [Clinical_Edu_2006].[dbo].[tblSChestTubeFeedback]
SET [Complications] =
(SELECT
Count([Aware of the contra-indications and complications when caring for a patient with a chest tube and drainage system]) as FCount
FROM Annual_Edu_2006.dbo.HREMP INNER JOIN
dbo.NursingFeedback ON Annual_Edu_2006.dbo.HREMP.[FULL NAME] = dbo.NursingFeedback.[Employee Name] AND
Annual_Edu_2006.dbo.HREMP.DOB = dbo.NursingFeedback.DOB WHERE [Submit Time] >= @DateFrom and [Submit Time]<@DateTo and [Aware of the contra-indications and complications when caring for a patient with a chest tube and drainage system]='ExcellentlyMet'
group by [Aware of the contra-indications and complications when caring for a patient with a chest tube and drainage system] ) where [tblSChestTubeFeedback].Indication='ExcellentlyMet'
UPDATE [Clinical_Edu_2006].[dbo].[tblSChestTubeFeedback]
SET [Complications] =
(SELECT
Count([Aware of the contra-indications and complications when caring for a patient with a chest tube and drainage system]) as FCount
FROM Annual_Edu_2006.dbo.HREMP INNER JOIN
dbo.NursingFeedback ON Annual_Edu_2006.dbo.HREMP.[FULL NAME] = dbo.NursingFeedback.[Employee Name] AND
Annual_Edu_2006.dbo.HREMP.DOB = dbo.NursingFeedback.DOB WHERE [Submit Time] >= @DateFrom and [Submit Time]<@DateTo and [Aware of the contra-indications and complications when caring for a patient with a chest tube and drainage system]='SatisfactorilyMet'
group by [Aware of the contra-indications and complications when caring for a patient with a chest tube and drainage system] ) where [tblSChestTubeFeedback].Indication='SatisfactorilyMet'
UPDATE [Clinical_Edu_2006].[dbo].[tblSChestTubeFeedback]
SET [Complications] =
(SELECT
Count([Aware of the contra-indications and complications when caring for a patient with a chest tube and drainage system]) as FCount
FROM Annual_Edu_2006.dbo.HREMP INNER JOIN
dbo.NursingFeedback ON Annual_Edu_2006.dbo.HREMP.[FULL NAME] = dbo.NursingFeedback.[Employee Name] AND
Annual_Edu_2006.dbo.HREMP.DOB = dbo.NursingFeedback.DOB WHERE [Submit Time] >= @DateFrom and [Submit Time]<@DateTo and [Aware of the contra-indications and complications when caring for a patient with a chest tube and drainage system]='WasnotMet'
group by [Aware of the contra-indications and complications when caring for a patient with a chest tube and drainage system] ) where [tblSChestTubeFeedback].Indication='WasnotMet'
UPDATE [Clinical_Edu_2006].[dbo].[tblSChestTubeFeedback]
SET [Complications] =
(SELECT
Count([Aware of the contra-indications and complications when caring for a patient with a chest tube and drainage system]) as FCount
FROM Annual_Edu_2006.dbo.HREMP INNER JOIN
dbo.NursingFeedback ON Annual_Edu_2006.dbo.HREMP.[FULL NAME] = dbo.NursingFeedback.[Employee Name] AND
Annual_Edu_2006.dbo.HREMP.DOB = dbo.NursingFeedback.DOB WHERE [Submit Time] >= @DateFrom and [Submit Time]<@DateTo and [Aware of the contra-indications and complications when caring for a patient with a chest tube and drainage system]='WellMet'
group by [The learner is able to understand chest tubes and indications for a chest insertion] ) where [tblSChestTubeFeedback].Indication='WellMet'
UPDATE [Clinical_Edu_2006].[dbo].[tblSChestTubeFeedback]
SET [Post Test] =
(SELECT
Count([Aware of the contra-indications and complications when caring for a patient with a chest tube and drainage system]) as FCount
FROM Annual_Edu_2006.dbo.HREMP INNER JOIN
dbo.NursingFeedback ON Annual_Edu_2006.dbo.HREMP.[FULL NAME] = dbo.NursingFeedback.[Employee Name] AND
Annual_Edu_2006.dbo.HREMP.DOB = dbo.NursingFeedback.DOB WHERE [Submit Time] >= @DateFrom and [Submit Time]<@DateTo and [Aware of the contra-indications and complications when caring for a patient with a chest tube and drainage system]='ExcellentlyMet'
group by [Aware of the contra-indications and complications when caring for a patient with a chest tube and drainage system] ) where [tblSChestTubeFeedback].Indication='ExcellentlyMet'
UPDATE [Clinical_Edu_2006].[dbo].[tblSChestTubeFeedback]
SET [Post Test] =
(SELECT
Count([Aware of the contra-indications and complications when caring for a patient with a chest tube and drainage system]) as FCount
FROM Annual_Edu_2006.dbo.HREMP INNER JOIN
dbo.NursingFeedback ON Annual_Edu_2006.dbo.HREMP.[FULL NAME] = dbo.NursingFeedback.[Employee Name] AND
Annual_Edu_2006.dbo.HREMP.DOB = dbo.NursingFeedback.DOB WHERE [Submit Time] >= @DateFrom and [Submit Time]<@DateTo and [Aware of the contra-indications and complications when caring for a patient with a chest tube and drainage system]='SatisfactorilyMet'
group by [Aware of the contra-indications and complications when caring for a patient with a chest tube and drainage system] ) where [tblSChestTubeFeedback].Indication='SatisfactorilyMet'
UPDATE [Clinical_Edu_2006].[dbo].[tblSChestTubeFeedback]
SET [Post Test] =
(SELECT
Count([Aware of the contra-indications and complications when caring for a patient with a chest tube and drainage system]) as FCount
FROM Annual_Edu_2006.dbo.HREMP INNER JOIN
dbo.NursingFeedback ON Annual_Edu_2006.dbo.HREMP.[FULL NAME] = dbo.NursingFeedback.[Employee Name] AND
Annual_Edu_2006.dbo.HREMP.DOB = dbo.NursingFeedback.DOB WHERE [Submit Time] >= @DateFrom and [Submit Time]<@DateTo and [Aware of the contra-indications and complications when caring for a patient with a chest tube and drainage system]='WasnotMet'
group by [Aware of the contra-indications and complications when caring for a patient with a chest tube and drainage system] ) where [tblSChestTubeFeedback].Indication='WasnotMet'
UPDATE [Clinical_Edu_2006].[dbo].[tblSChestTubeFeedback]
SET [Post Test] =
(SELECT
Count([Aware of the contra-indications and complications when caring for a patient with a chest tube and drainage system]) as FCount
FROM Annual_Edu_2006.dbo.HREMP INNER JOIN
dbo.NursingFeedback ON Annual_Edu_2006.dbo.HREMP.[FULL NAME] = dbo.NursingFeedback.[Employee Name] AND
Annual_Edu_2006.dbo.HREMP.DOB = dbo.NursingFeedback.DOB WHERE [Submit Time] >= @DateFrom and [Submit Time]<@DateTo and [The post test questions were applicable to the chest tube education powerpoint presentation]='WellMet'
group by [The learner is able to understand chest tubes and indications for a chest insertion] ) where [tblSChestTubeFeedback].Indication='WellMet'
select *
from [Clinical_Edu_2006].[dbo].[tblSChestTubeFeedback]
END
else
begin
delete from [Clinical_Edu_2006].[dbo].[tblSChestTubeFeedback]
INSERT INTO [Clinical_Edu_2006].[dbo].[tblSChestTubeFeedback]
(
[Indication]
, [IndicationC]
)
SELECT [The learner is able to understand chest tubes and indications for a chest insertion]
, Count([The learner is able to understand chest tubes and indications for a chest insertion]) as FCount
FROM Annual_Edu_2006.dbo.HREMP INNER JOIN
dbo.NursingFeedback ON Annual_Edu_2006.dbo.HREMP.[FULL NAME] = dbo.NursingFeedback.[Employee Name] AND
Annual_Edu_2006.dbo.HREMP.DOB = dbo.NursingFeedback.DOB WHERE [Submit Time] >= @DateFrom and [Submit Time]<@DateTo and Annual_Edu_2006.dbo.HREMP.CC like @cc
group by [The learner is able to understand chest tubes and indications for a chest insertion]
select *
from [Clinical_Edu_2006].[dbo].[tblSChestTubeFeedback]
end
end
error message:
Msg 512, Level 16, State 1, Procedure CalChestSum, Line 97
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
I do not see any select statement occure twice
April 8, 2011 at 12:56 pm
I am not going to try to find line 97 in that but based on the error it is likely you have a subquery in the where cluase of one of the statements. sice it is returning more than a single value it is causing the error.
so lets say you had where x=(select y from table) then y could only return a single value not multiple rows.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 8, 2011 at 3:51 pm
Hi,
I only want to add that you have a copy and paste error in the following statement:
UPDATE [Clinical_Edu_2006].[dbo].[tblSChestTubeFeedback]
SET [Bedside Procedure & Interventions] =
(
SELECT Count([The learner understands the bedside procedure and nursing responsibilities when the chest tube is inserted at the bedside]) as FCount
FROM Annual_Edu_2006.dbo.HREMP
INNER JOIN
dbo.NursingFeedback ON Annual_Edu_2006.dbo.HREMP.[FULL NAME] = dbo.NursingFeedback.[Employee Name]
AND Annual_Edu_2006.dbo.HREMP.DOB = dbo.NursingFeedback.DOB
WHERE [Submit Time] >= @DateFrom
and [Submit Time]<@DateTo
and [The learner understands the bedside procedure and nursing responsibilities when the chest tube is inserted at the bedside]='ExcellentlyMet'
group by
[The learner is able to understand chest tubes and indications for a chest insertion]
)
where [tblSChestTubeFeedback].Indication='ExcellentlyMet'
this subquery produces more than one return value:
SELECT Count([The learner understands the bedside procedure and nursing responsibilities when the chest tube is inserted at the bedside]) as FCount
FROM Annual_Edu_2006.dbo.HREMP
INNER JOIN
dbo.NursingFeedback ON Annual_Edu_2006.dbo.HREMP.[FULL NAME] = dbo.NursingFeedback.[Employee Name]
AND Annual_Edu_2006.dbo.HREMP.DOB = dbo.NursingFeedback.DOB
WHERE [Submit Time] >= @DateFrom
and [Submit Time]<@DateTo
and [The learner understands the bedside procedure and nursing responsibilities when the chest tube is inserted at the bedside]='ExcellentlyMet'
group by
[The learner is able to understand chest tubes and indications for a chest insertion]
you should replace the bold column with [The learner understands the bedside procedure and nursing responsibilities when the chest tube is inserted at the bedside]
Greets
Patrick Fiedler
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
www.fiedler-sql-consulting.de
[/font]
April 11, 2011 at 6:43 am
The where condition and group by is not the same. Thanks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply