?
Solved

SQL Set variable if 1 record exists

Posted on 2011-02-25
8
Medium Priority
?
785 Views
Last Modified: 2012-05-11
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
Comment
Question by:cheryl9063
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 200 total points
ID: 34980555
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
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34980577
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
 
LVL 10

Expert Comment

by:dwe761
ID: 34980579
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Author Comment

by:cheryl9063
ID: 34980585
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
 
LVL 10

Assisted Solution

by:dwe761
dwe761 earned 400 total points
ID: 34980712
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
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 1400 total points
ID: 34980767
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
 
LVL 1

Author Closing Comment

by:cheryl9063
ID: 34981815
Thanks!
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34981905
Glad to help
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question