SQL Set variable if 1 record exists

See query example below.. I want to set a variable if only 1 record exists.. If no records exists I want to do something if more than 1 record exists I want to do something else..
Set @Lid = (Select Lid from table)--- only if 1 record exists

If @@Rowcount < 1
Print 'error'
If @@Rowcount > 1
Print 'different error'

Open in new window

LVL 1
cheryl9063Asked:
Who is Participating?
 
Rajkumar GsConnect With a Mentor Software EngineerCommented:
Or you can store the records in a temporary table and process from there
declare @table table
(
	Lid int
)
insert into @table 
	select Lid from table -- your main query

declare @count int
select @count = count(Lid) from @table

declare @Lid int

if @count = 1
	select @Lid = Lid from @table
else if @count > 1
-- do something when count is greater than one
else
-- do something when count is lesser than one (zero)

Open in new window


this could avoid reading second time from the main tables
0
 
Patrick MatthewsConnect With a Mentor Commented:
DECLARE @numrows int
DECLARE @Lid varchar(100) --or whatever data type

SET @numrows = (SELECT COUNT(1) FROM table)

IF @numrows = 1
    SET @Lid = (SELECT Lid FROM table)
    ELSE
        IF @numrows = 0
            PRINT 'error'
        ELSE
            PRINT 'different error'

Open in new window

0
 
Rajkumar GsSoftware EngineerCommented:
declare @count int
select @count = count(Lid) from table

declare @Lid int

if @count = 1
	select @Lid = Lid from table
else if @count > 1
-- do something when count is greater than one
else
-- do something when count is lesser than one (zero)

Open in new window

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
dwe761Software EngineerCommented:
if exists(select Lid from table)
      if exists(select top 2 Lid from table)
            Print 'different error - > 1 records exist'
      else
            select @Lid = Lid from table
else
      Print 'no records exist'
0
 
cheryl9063Author Commented:
Thank you.. The only problem with this solution is I would have to do the query twice correct? My real query is pretty big and involves several joins..
0
 
dwe761Connect With a Mentor Software EngineerCommented:
That's why you'd be better off using EXISTS() because it stops after finding any records to match your query rather than running through an entire count.

My original query had a problem.  Use this instead.

if exists(select Lid from table)
      if exists(select count(a.Lid) FROM (select top 2 Lid from Table) a )
            Print 'different error - > 1 records exist'
      else
            select @Lid = Lid from table  --- only if 1 record exists
else
      Print 'no records exist'
0
 
cheryl9063Author Commented:
Thanks!
0
 
Rajkumar GsSoftware EngineerCommented:
Glad to help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.