Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 790
  • Last Modified:

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

0
cheryl9063
Asked:
cheryl9063
  • 3
  • 2
  • 2
  • +1
3 Solutions
 
Patrick MatthewsCommented:
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
dwe761Software 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
 
Rajkumar GsSoftware 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
 
cheryl9063Author Commented:
Thanks!
0
 
Rajkumar GsSoftware EngineerCommented:
Glad to help
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now