• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 393
  • Last Modified:

Better way for If exists statement

The following is my statment is there a better way to write this to get what I want it to do.  Bascially if it exists I want to select the SSn so i can provide a message that they exist already, if not I want to insert them into the DB.  I bascially have to use the select statement twice in the statement, is there a bette way??


IF Exists(Select strSSN from ModreScheduler.dbo.tblSchedule where intTimeSlotID in 
		(Select intTimeSlotID from ModreScheduler.dbo.tblTimeSlots where intEventDateID = 509 and intEventDateID in 
		(Select intEventDateID from ModreScheduler.dbo.tblEventDate where intEventID = 142)) and strSSN = '000000000') 
		Select strSSN from ModreScheduler.dbo.tblSchedule where intTimeSlotID in 
		(Select intTimeSlotID from ModreScheduler.dbo.tblTimeSlots where intEventDateID = 509 and intEventDateID in 
		(Select intEventDateID from ModreScheduler.dbo.tblEventDate where intEventID = 142)) and strSSN = '000000000'
		else 
		Insert ModreScheduler.dbo.tblSchedule (intTimeSlotid, strSSN, strInputPerson, strComments) VALUES (2309, '000000000', 'john.doe', '')

Open in new window

0
kdeutsch
Asked:
kdeutsch
1 Solution
 
Neil RussellTechnical Development LeadCommented:
Firstly, each time you run this, WHAT of the above are vaiable? Is the eventID ALWAYS 142? intEventDateID Always 509 etc?

Whats variable?
0
 
Nico BontenbalCommented:
You could store the result in a variable and use this in the rest of procedure like this:
declare @strSSN varchar(1000)
set @strSSN = (Select strSSN from ModreScheduler.dbo.tblSchedule where intTimeSlotID in 
		(Select intTimeSlotID from ModreScheduler.dbo.tblTimeSlots where intEventDateID = 509 and intEventDateID in 
		(Select intEventDateID from ModreScheduler.dbo.tblEventDate where intEventID = 142)) and strSSN = '000000000') 

IF @strSSN is not null
		Select strSSN from ModreScheduler.dbo.tblSchedule where intTimeSlotID in 
		(Select intTimeSlotID from ModreScheduler.dbo.tblTimeSlots where intEventDateID = 509 and intEventDateID in 
		(Select intEventDateID from ModreScheduler.dbo.tblEventDate where intEventID = 142)) and strSSN = '000000000'
		else 
		Insert ModreScheduler.dbo.tblSchedule (intTimeSlotid, strSSN, strInputPerson, strComments) VALUES (2309, '000000000', 'john.doe', '')

Open in new window

(untested and might have syntax errors but I hope you'll get the general idea)
0
 
Ephraim WangoyaCommented:
try @@ROWCOUNT
Select strSSN from ModreScheduler.dbo.tblSchedule where intTimeSlotID in 
		(Select intTimeSlotID from ModreScheduler.dbo.tblTimeSlots where intEventDateID = 509 and intEventDateID in 
		(Select intEventDateID from ModreScheduler.dbo.tblEventDate where intEventID = 142)) and strSSN = '000000000'

if @@ROWCOUNT = 0
  Insert ModreScheduler.dbo.tblSchedule (intTimeSlotid, strSSN, strInputPerson, strComments) VALUES (2309, '000000000', 'john.doe', '')

Open in new window

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.

 
Kalyanum Deepak KumarCommented:
Something in this front :

declare @strSSN nvarchar(max)
declare DECLARE @result INT

set @strSSN = 'IF EXISTS(Select strSSN from ModreScheduler.dbo.tblSchedule where intTimeSlotID in
            (Select intTimeSlotID from ModreScheduler.dbo.tblTimeSlots where intEventDateID = 509 and intEventDateID in
            (Select intEventDateID from ModreScheduler.dbo.tblEventDate where intEventID = 142)) and strSSN = ''000000000'')'
            
            EXEC @result = sp_executesql @strSSN

IF @result = 0
            EXEC @result = sp_executesql @strSSN
            else
            Insert ModreScheduler.dbo.tblSchedule (intTimeSlotid, strSSN, strInputPerson, strComments) VALUES (2309, '000000000', 'john.doe', '')
            
0
 
kdeutschAuthor Commented:
Neilsr:,

The values change each and every time depending on what they pick from dropdowns.
0
 
kdeutschAuthor Commented:
ewangoya:
If there a way with this to find out if something was inserted or not, current the rason for the return of the selct is to pop up a message saying that a record exists.
0
 
Ephraim WangoyaCommented:
You can handle that in the if section

 
Select strSSN from ModreScheduler.dbo.tblSchedule where intTimeSlotID in 
		(Select intTimeSlotID from ModreScheduler.dbo.tblTimeSlots where intEventDateID = 509 and intEventDateID in 
		(Select intEventDateID from ModreScheduler.dbo.tblEventDate where intEventID = 142)) and strSSN = '000000000'

if @@ROWCOUNT = 0
  Insert ModreScheduler.dbo.tblSchedule (intTimeSlotid, strSSN, strInputPerson, strComments) VALUES (2309, '000000000', 'john.doe', '')
else
  print 'Record Exists'

Open in new window


Or if its in a stored procedure, you can set a boolean variable and return it through the procedure
0
 
GhunaimaCommented:
Try This,

declare @strSSN varchar(9)
Select @strSSN = strSSN from ModreScheduler.dbo.tblSchedule where intTimeSlotID in
  (Select intTimeSlotID from ModreScheduler.dbo.tblTimeSlots where intEventDateID = 509 and
    intEventDateID in (Select intEventDateID from ModreScheduler.dbo.tblEventDate
                                  where intEventID = 142)) and strSSN = '000000000'

if @strSSN is null Insert ModreScheduler.dbo.tblSchedule
                          (intTimeSlotid, strSSN, strInputPerson, strComments)
                     VALUES (2309, '000000000', 'john.doe', '')
0
 
kdeutschAuthor Commented:
thanks this works, just as good as mine with less coding, what I was looking for.
0
 
sureshbabukrishCommented:
try this ...


IF Exists (Select 1from ModreScheduler.dbo.tblSchedule where intTimeSlotID in  
                (Select intTimeSlotID from ModreScheduler.dbo.tblTimeSlots where intEventDateID = 509 and intEventDateID in  
                (Select intEventDateID from ModreScheduler.dbo.tblEventDate where intEventID = 142)) and strSSN = '000000000'  )
  Insert ModreScheduler.dbo.tblSchedule (intTimeSlotid, strSSN, strInputPerson, strComments) VALUES (2309, '000000000', 'john.doe', '')  
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now