Solved

Conditional Stored procedures

Posted on 2009-06-30
7
168 Views
Last Modified: 2012-05-07
I am looking for a way to insert data into a table but if there is already a record there with the same employeeid or userid, I want it to be an update.  How would I do this in a stored procedure?  Thank you for your help ahead of time.
0
Comment
Question by:VBBRett
  • 3
  • 2
  • 2
7 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24746047

create procedure testSp
@EmployeeID int
AS
BEGIN
SET NOCOUNT ON
INSERT INTO urTable  (EmployeeID)
SELECT @EmployeeID
WHERE NOT EXISTS (SELECT 1 FROM urTable WHERE EmployeeID = @EmployeeID )
IF @@ROWCOUNT = 0
  UPDATE urTable
 SET ColumnName = Value
 WHERE EmployeeID = @EmployeeID
END
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24746081
here you have a comparison of the 2 possible methods:
http://weblogs.sqlteam.com/mladenp/archive/2007/07/30/60273.aspx

I usually go with the  UPDATE + INSERT, and not with the EXISTS method...
0
 

Author Comment

by:VBBRett
ID: 24746274
angelll, can you give me an example of the update + insert conditional statement?
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24746410
you did not look into the link, did you?
the code is there.
0
 

Author Comment

by:VBBRett
ID: 24746549
Here is my insert statement.  How would I change this to a conditional insert statement, or update if there is a record?  See the code below:  Thanks!
USE [EmployeeS2]

GO

/****** Object:  StoredProcedure [dbo].[EmployeeS2Answers]    Script Date: 06/30/2009 11:48:16 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:		<Me>

-- Create date: <06/25/09>

-- Description:	<This procedure enters question 

--information into EmployeeS2 Table in the

-- Employee database

-- =============================================

ALTER PROCEDURE [dbo].[EmployeeScreeningAnswers]

		@EmployeeId		int,

		@Question1		bit,

		@Question2		bit,

		@Question3		bit,

		@Question4		bit,

		@Question5		bit,

		@Question6		bit,	

		@Question7		bit,

		@Question8		bit,

		@Question9		bit,

		@Question10		bit,

		@Question11		bit,

		@Question12		bit,

		@Question13		bit,

		@Question14		bit,

		@Question15		bit,	

		@Question16		bit,		

		@LastUpdate		datetime,

		@LastUpdateBy	nvarchar

			

AS

BEGIN
 

	INSERT INTO [dbo].[EmployeeS2](EmployeeId, Q1, Q2, Q3, Q4, Q5, Q6, Q7, Q8, Q9,

Q10, Q11, Q12, Q13, Q14, Q15, Q16, LastUpdate, LastUpdateBy)

VALUES (@EmployeeId, @Question1, @Question2, @Question3, @Question4, @Question5, @Question6,

@Question7, @Question8, @Question9, @Question10, @Question11, @Question12, @Question13, 

@Question14, @Question15, @Question16, @LastUpdate, @LastUpdateBy)
 

END

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24746630
presuming employeeid is the primary key...
ALTER PROCEDURE [dbo].[EmployeeScreeningAnswers]

		@EmployeeId		int,

		@Question1		bit,

		@Question2		bit,

		@Question3		bit,

		@Question4		bit,

		@Question5		bit,

		@Question6		bit,	

		@Question7		bit,

		@Question8		bit,

		@Question9		bit,

		@Question10		bit,

		@Question11		bit,

		@Question12		bit,

		@Question13		bit,

		@Question14		bit,

		@Question15		bit,	

		@Question16		bit,		

		@LastUpdate		datetime,

		@LastUpdateBy	nvarchar

			

AS

BEGIN

UPDATE [dbo].[EmployeeS2]

     SET Question1 = @Question1

       , Question2 = @Question2

       ... etc ...

  WHERE EmployeeId = @EmployeeId

IF @@ROWCOUNT = 0

BEGIN 

	INSERT INTO [dbo].[EmployeeS2](EmployeeId, Q1, Q2, Q3, Q4, Q5, Q6, Q7, Q8, Q9,

Q10, Q11, Q12, Q13, Q14, Q15, Q16, LastUpdate, LastUpdateBy)

VALUES (@EmployeeId, @Question1, @Question2, @Question3, @Question4, @Question5, @Question6,

@Question7, @Question8, @Question9, @Question10, @Question11, @Question12, @Question13, 

@Question14, @Question15, @Question16, @LastUpdate, @LastUpdateBy)

END

END

Open in new window

0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 24751251
something like this

USE [EmployeeS2]

GO

/****** Object:  StoredProcedure [dbo].[EmployeeS2Answers]    Script Date: 06/30/2009 11:48:16 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:		<Me>

-- Create date: <06/25/09>

-- Description:	<This procedure enters question 

--information into EmployeeS2 Table in the

-- Employee database

-- =============================================

ALTER PROCEDURE [dbo].[EmployeeScreeningAnswers]

		@EmployeeId		int,

		@Question1		bit,

		@Question2		bit,

		@Question3		bit,

		@Question4		bit,

		@Question5		bit,

		@Question6		bit,	

		@Question7		bit,

		@Question8		bit,

		@Question9		bit,

		@Question10		bit,

		@Question11		bit,

		@Question12		bit,

		@Question13		bit,

		@Question14		bit,

		@Question15		bit,	

		@Question16		bit,		

		@LastUpdate		datetime,

		@LastUpdateBy	nvarchar

			

AS

BEGIN

SET NOCOUNT 

INSERT INTO [dbo].[EmployeeS2](EmployeeId, Q1, Q2, Q3, Q4, Q5, Q6, Q7, Q8, Q9,

				Q10, Q11, Q12, Q13, Q14, Q15, Q16, LastUpdate, LastUpdateBy)

SELECT @EmployeeId, @Question1, @Question2, @Question3, @Question4, @Question5, @Question6,

		@Question7, @Question8, @Question9, @Question10, @Question11, @Question12, @Question13, 

		@Question14, @Question15, @Question16, @LastUpdate, @LastUpdateBy

WHERE NOT EXISTS (SELECT 1 FROM [dbo].[EmployeeS2] WHERE EmployeeID = @EmployeeId )

IF @@ROWCOUNT = 0 

	UPDATE [dbo].[EmployeeS2]

	SET Q1 = @Question1, 

	    Q2 = @Question2,...

	EmployeeID = @EmployeeId		
 

END 

Open in new window

0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
In this article I will describe the Copy Database Wizard 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.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

757 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