CODEWITHSUNDEEP
stored-proceduresexists
randy
randy

Reputation: 11

Stored procedure if exists not giving correct answer

I created a sql 2005 stored proc to tell me if the CRID I am searching for is approved by the supervisor. [SuperApproved] is a bit, [CRID] is char(36). Even if the CRID doesn't exist, I am still getting 1. Any help in writing this?

USE [cr_Saturn2]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  Randy Baker
-- Create date: 10/12/2010
-- Description: Check for Approved CRID in CostReportTracking
-- =============================================
alter PROCEDURE [dbo].[st_Is_CostReportApproved]
 -- Add the parameters for the stored procedure here
 @myECR char(36) = null  
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

if exists(
 select [CRID]
 from [dbo].[CostReportTracking]
 where [SuperApproved] = 1)

 -- exists- cost report is Approved by Supervisor
 select 1
else
 -- does not exist
 select 0    
END

Upvotes: 1

Views: 139

Answers (1)

Klaus Byskov Pedersen
Klaus Byskov Pedersen

Reputation: 120917

I think you need to add an and [CRID] = @myECR somewhere. Right now you are just checking if there is any record that has been SuperApproved.

Upvotes: 2

Related Questions