Best way to return a 1 or 0 for an if exists query
-
what would be the preferred way to return a 0 or a 1 based on a result set, please?
option 1
CREATE Procedure [dbo].[sp_GGA_Testing]
@status int,
@ID int
As
declare @Exists int
set @Exists = 0
select @Exists = 1
from tblONE
where ID = @ID
and [Status] = @status
select @Exists
GO
option 2;
create Procedure [dbo].[sp_GGA_Testing_2]
@status int,
@ID int
As
if (select COUNT(*)from tblONE
where ID = @ID
and [Status] = @status) = 0
RETURN 0
else
RETURN 1;
GO
-- then in the code call the procedure as follows
declare @return_status int;
exec @return_status = [sp_GGA_Testing_2] 0,3581;
select 'Return Status' = @return_status
go
or a completely different way?
-
CREATE PROCEDURE [dbo].[sp_GGA_Testing_3] @status INT, @ID INTAS
IF EXISTS (SELECT 1
FROM tblONE
WHERE ID = @ID
AND [Status] = @status )
SELECT 1;
ELSE
SELECT 0;
GO
Or you can be much the same and assign a variable that's then used as a return or an output variable.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
The idea behind the requirement worries me - what are you looking to do with the result? Anyway, this is one reasonably efficient way to do what you ask:
CREATE TABLE dbo.Example
(
[ID] integer PRIMARY KEY,
[Status] integer NOT NULL
)
GO
INSERT dbo.Example
([ID], [Status])
VALUES
(1, 100);
CREATE FUNCTION dbo.IDStatusExists
(
@ID integer,
@status integer
)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
SELECT
CASE
WHEN EXISTS
(
SELECT 1
FROM dbo.Example AS e
WHERE
e.[ID] = @ID
AND e.[Status] = @status
)
THEN CONVERT(bit, 1)
ELSE CONVERT(bit, 0)
END AS Result;
SELECT ise.Result
FROM dbo.IDStatusExists(1, 100) AS ise;
SELECT ise.Result
FROM dbo.IDStatusExists(2, 200) AS ise;
-
i think you can do it inline with a sub select, like this without using an If structure:
CREATE Procedure [dbo].[sp_GGA_Testing]
@status int,
@ID int
As
SELECT ISNULL(ExistsVal,0)
FROM
(select 1 AS ExistsVal
from tblONE
where ID = @ID
and [Status] = @status
)MyAlias
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Alternatively:
CREATE PROCEDURE dbo.IDStatusExists2
@ID integer,
@status integer,
@Result bit OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT @Result =
CASE
WHEN EXISTS
(
SELECT 1
FROM dbo.Example AS e
WHERE
e.[ID] = @ID
AND e.[Status] = @status
)
THEN CONVERT(bit, 1)
ELSE CONVERT(bit, 0)
END;
END;
DECLARE @Exists bit;
EXECUTE dbo.IDStatusExists2
@ID = 2,
@status = 100,
@Result = @Exists OUTPUT;
SELECT
@Exists;
-
Lowell (1/23/2012)
i think you can do it inline with a sub select, like this without using an If structure:CREATE Procedure [dbo].[sp_GGA_Testing]
@status int,
@ID int
As
SELECT ISNULL(ExistsVal,0)
FROM
(select 1 AS ExistsVal
from tblONE
where ID = @ID
and [Status] = @status
)MyAlias
Two problems with that...
It'll return no rows at all instead of a 0 if there are no matching rows, it'll return multiple rows with 1 if there are multiple matching rows.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (1/23/2012)
CREATE PROCEDURE [dbo].[sp_GGA_Testing_3] @status INT, @ID INTAS
IF EXISTS (SELECT 1
FROM tblONE
WHERE ID = @ID
AND [Status] = @status )
SELECT 1;
ELSE
SELECT 0;
GO
Or you can be much the same and assign a variable that's then used as a return or an output variable.
this is pretty much my option 1, no?
-
GilaMonster (1/23/2012)
Two problems with that...Very generous of you to stop at two 😉
(Sorry Lowell).
-
Geoff A (1/23/2012)
this is pretty much my option 1, no?If there's only ever 1 or 0 matching rows, kinda yes.
If there could be more, then no, not performance wise. If there were several hundred rows that matched, your first option would read all several hundred, the exists would stop reading as soon as SQL could determine whether there was a row or not (best case, read 1 row, worst case read them all)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
SQL Kiwi (1/23/2012)
The idea behind the requirement worries me - what are you looking to do with the result? Anyway, this is one reasonably efficient way to do what you ask:somewhere in the C# code of the app, it calls for the result.
1 it does one thing, 0 it does another.
option 1 is what is in production right now, and to me, it looked a little dated.
I was just wondering with the new feature of SQL since the app was first written, there might be a better way.
-
GilaMonster (1/23/2012)
Geoff A (1/23/2012)
this is pretty much my option 1, no?If there's only ever 1 or 0 matching rows, kinda yes.
If there could be more, then no, not performance wise. If there were several hundred rows that matched, your first option would read all several hundred, the exists would stop reading as soon as SQL could determine whether there was a row or not (best case, read 1 row, worst case read them all)
thanks Gail, that is exactly what i was interested in hearing.
-
Geoff A (1/23/2012)
I was just wondering with the new feature of SQL since the app was first written, there might be a better way.Can't think of any new features that will help with this. My preference for this kind of logic is EXISTS most of the time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Some new features, for fun (though the execution plan is quite cool too):
ALTER PROCEDURE dbo.IDStatusExists3
@ID integer,
@status integer
AS
BEGIN
SET NOCOUNT ON;
WITH Subquery (ID, [Status]) AS
(
SELECT TOP (1)
@ID, @status
FROM dbo.Example AS e
WHERE
e.ID = @ID
AND e.[Status] = @status
)
SELECT
COUNT_BIG(*)
FROM Subquery;
END;
GO
EXECUTE dbo.IDStatusExists3
@ID = 1,
@status = 100;
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply