valid or invalid result in select command

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)

valid or invalid result in select command

Author

Topic

Exir
Posting Yak Master

151 Posts

Posted - 2008-10-29 : 07:22:58

I have written this Sp:


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[SearchForCode] 
@code int
AS
BEGIN
SELECT * From request WHERE id=@code
END

I want that if there was no result for SELECT command, a variable set as 0 or false and if the result was valid, set the variable with 1 or true.
How can i do that?

cvraghu
Posting Yak Master

187 Posts

Posted - 2008-10-29 : 07:29:33

Use Count function to find out the total no of rows available for the particular code -

SELECT Count(*) From request WHERE id=@code

If none, it will return 0, else it will return a number greater than 0.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 07:31:03

Add an OUTPUT parameter of bit type and set value inside

Exir
Posting Yak Master

151 Posts

Posted - 2008-10-29 : 08:00:23

Could you please explain more
What is the problem with this code?
[CODE]
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[SearchForCode]
@code int
AS
BEGIN
DECLARE @num int=0
SET @num=(SELECT Count(*) From request WHERE id=@code)
END
[/CODE]
the errors are:
Msg 139, Level 15, State 1, Procedure SearchForCode, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 1, Procedure SearchForCode, Line 6
Must declare the scalar variable "@num".

avijit_mca
Posting Yak Master

109 Posts

Posted - 2008-10-29 : 08:09:21

alter PROCEDURE [dbo].[SearchForCode]
@code varchar(50)
AS
declare @flag as int
BEGIN
if exists(SELECT * From request WHERE id=@code) begin set @flag= 1
end
else
begin set @flag= 0 end
select @flag
END

Regards,
avijit

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 08:15:27

quote:
Originally posted by Exir

Could you please explain more
What is the problem with this code?
[CODE]
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[SearchForCode]
@code int
AS
BEGIN
DECLARE @num int
SET @num=(SELECT Count(*) From request WHERE id=@code)

END
[/CODE]
the errors are:
Msg 139, Level 15, State 1, Procedure SearchForCode, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 1, Procedure SearchForCode, Line 6
Must declare the scalar variable "@num".

dont need assigment in DECLARE

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-29 : 08:34:49

quote:

dont need assigment in DECLARE

And in fact you can't. If you need to you can do it like this


DECLARE @var INT SET @var = 0

However that stops you from doing things like this


DECLARE
   @a INT
   , @b INT
   , @c INT
   , @d VARCHAR(50)

Unlike C (I'm taking a stab in the dark based on your syntax here) the declared variable will receive a value of NULL implicitly so no need to really assign a value right away.

-------------
Charlie

ursangel
Starting Member

17 Posts

Posted - 2008-10-29 : 08:43:16

ALTER PROCEDURE [dbo].[SearchForCode]
@code int
AS
BEGIN
declare @result int
SELECT * From request WHERE id=@code

if (@@row_count = 0)
set @result = 0
else
set @result = 1

select @result
END

Regards
Angel

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-29 : 09:14:22

quote:
Originally posted by ursangel

ALTER PROCEDURE [dbo].[SearchForCode]
@code int
AS
BEGIN
declare @result int
SELECT * From request WHERE id=@code

if (@@row_count = 0)
set @result = 0
else
set @result = 1

select @result
END

Regards
Angel

This is ineffecient if there are many rows for that parameter value
Also why are you returing the resultset unneccessarily

Madhivanan

Failing to plan is Planning to fail

ursangel
Starting Member

17 Posts

Posted - 2008-10-29 : 10:25:35

Sorry was not lloking into all the requirement of : Exir
You ver askign what was wrong in the SP you have written below right?
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[SearchForCode]
@code int
AS
BEGIN
DECLARE @num int=0
SET @num=(SELECT Count(*) From request WHERE id=@code)
END

/******* here is the answer ********/
first of all you cannot assign a value to the local variable, during its declaration
rewrite it as
DECLARE @num int;
Set @num = 0;
-- secondly chnage the way the count is taken to the variable
SELECT @num = Count(*) From request WHERE id=@code

This will do i suppose.
Sorry for confusing with that @@rowcount function and all those.

Regards
Angel

Regards
Angel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources