If exists and if not exists

Question

Hi, i have a stored procedure with parameter and column in TableA as TripId and i want to do following checks on TripId.

1. If user changes the TripId then update it in TableA

2. If TripId doesnt exist in TableA then insert it

3. If TripId is Null or blank in table and user passes a numeric trip id then update it in table.

4. TripId should be a valid 5 digit number.

How do i put all these checks in one query in stored proc?

Answer 1

What is your SQL Server version?

IF @TripID LIKE '[1-9][0-9][0-9][0-9][0-9]'   

    

MERGE dbo.Referral_Custom_Attr_Def as trg 
     USING (select @TripID as TripID) as src ON trg.TripID = src.TripID    

WHEN NOT MATCHED         INSERT (Key_Value,Group_Key,Type,Description,Multi_Valued,Created_On,Created_By,TripId)    
         values(@FootHill_LowGroupID,@FootHill_LowGroupID_GroupKey,'INT',@FootHill_LowGroupID_GroupKey,1,getdate(),@Created_By, @TripID)

OUTPUT $Action, Inserted.FootHill_LowGroupID_attr_id

That's an idea for SQL Server 2008 and up. For earlier versions you need to use INSERT/UPDATE pattern, I suggest to check

this blog

Patterns that don't work as expected.

    

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

--Temporary Table Creation (You can use your table instead of this)
If(Object_Id('tempdb.dbo.#TmpTripDetail') Is Not Null)
Begin
	Drop Table dbo.#TmpTripDetail
End
Go

Create Table tempdb.dbo.#TmpTripDetail(UserId Int, TripID Varchar(5))
--------------------------------------------------
Declare @TripID Varchar(5), @UserId Int, @ReturnMessage Varchar(1000)
Select @TripID = '78945',@UserId = 5, @ReturnMessage = ''

-->>>>>>>>>>>Trip ID validation>>>>>>>>>>>>>>>>>>>>>
If Not (@TripID Like '[0-9][0-9][0-9][0-9][0-9]')
Begin
	Select @ReturnMessage = 'Trip Id Not valid'
	GoTo Msg
End
--<<<<<<<<<<Trip ID Validation Ended<<<<<<<<<<<<<<<<<


If(												--Trip ID Exists or not
	(Select COUNT(1) From dbo.#TmpTripDetail t 
		Where t.TripID = @TripID 
			And t.UserId = @UserId 
		)>0
	)
Begin
	Update dbo.#TmpTripDetail					--Trip Update for User if exists
	Set TripId = @TripId
	Where UserId = @UserId  
	
	Set @ReturnMessage = 'User Trip Detail Updated'
End
Else 
Begin
	Insert Into dbo.#TmpTripDetail ([TripID], [UserId]) --Trip Added in User
	Values (@TripID , @UserId)  
	
	
	Set @ReturnMessage = 'User Trip Detail Added'
End

Msg:
Select @ReturnMessage As Message

Answer 3

1. You can check oldTripId with new one, if both are not same means updated. Simple IF condition is sufficient for that.
2. You can check

IF NOT EXIST (SELECT 1 FROM TableA Where TripId=@TripId)
Begin
  --Set Insert statement here
End

Same way using IF condition you can check for third condition.

Answer 4

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

This is minimal polite behavior on a SQL forum. 

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html 

We do not put checks in procedures; they go into the DDL. That is why trip_id will always be NOT NULL and five digits if you did correct DDL.  a This sounds like a simple MERGE statement. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL