Solved

SQL Set variable if 1 record exists

Posted on 2011-02-25
8
778 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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 50 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 10

Assisted Solution

by:dwe761
dwe761 earned 100 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 350 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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Detach & Attach 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.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

863 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now