Solved

SQL Set variable if 1 record exists

Posted on 2011-02-25
8
777 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 10

Assisted Solution

by:dwe761
dwe761 earned 100 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks!
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
Comment Utility
Glad to help
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

744 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

17 Experts available now in Live!

Get 1:1 Help Now