?
Solved

Order and syntax of multiple/conditional operations in stored procedures

Posted on 2007-10-14
10
Medium Priority
?
193 Views
Last Modified: 2010-03-19
Hello:

I haven't written very many stored procedures, just simple one's with just one command. Now, I am daring to write a complex one with a few conditional statements in SQL Server 2005.  Essentially, I need to insert into or update to two related rows at once (an employee table and the rate the company pays the employee table). Below are the queries I want to write. Can anyone specify where I should insert the Go's, put in the Paramaters, etc.

Here's the logic and thanks in advance:

-- Variables (everything except @FkUserId is provided by a web form)

      @FirstName varchar(40),
      @LastName varchar(40),
      @LoginName varchar(100),
      @FkUserId int,
      @Rate money,
      @RateStart datetime,
      @RateEnd datetime,
      @Active varchar(1)

@FkUserId = (SELECT PkUserId FROM tblUser WHERE FirstName = @FirstName, LastName = @LastName, Email = @Email)

if @FkUserId is Null then
INSERT INTO tblUser(FirstName, LastName, Email)
VALUES (@FirstName, LastName, @Email)
also
INSERT INTO tblUserRate (FkUserId, Rate, RateStart, RateEnd, Active)
VALUES (@FkUserId, @Rate, @RateStart, @RateEnd, @Active)

if @FkUserId is NOT Null then
UPDATE tblUser
SET FirstName = @FirstName, LastName = @LastName, Email = @Email
WHERE PkUserId = @FkUserId
also
UPDATE tblUserRates
SET @Rate, @RateStart, @RateEnd, @Active
WHERE FkUserId = @FkUserId

END

0
Comment
Question by:freezegravity
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 14

Assisted Solution

by:Jai S
Jai S earned 200 total points
ID: 20076132
change this
@FkUserId = (SELECT PkUserId FROM tblUser WHERE FirstName = @FirstName, LastName = @LastName, Email = @Email)
to
@FkUserId = (SELECT PkUserId FROM tblUser WHERE FirstName = @FirstName and LastName = @LastName and Email = @Email)

if you are using"also " in your qwuery...remove it...just provide the statements tht is enuf
0
 
LVL 14

Expert Comment

by:Jai S
ID: 20076134
and ofcourse you need to have a create procedure command as

CREATE PROCEDURE procname
--give you parameters here
as
you sql statements...
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 800 total points
ID: 20076186
CREATE PROCEDURE set_user_record
      @FirstName varchar(40),
      @LastName varchar(40),
      @LoginName varchar(100),
      @FkUserId int,
      @Rate money,
      @RateStart datetime,
      @RateEnd datetime,
      @Active varchar(1),
  FkUserId int OUTPUT
AS


SELECT @FkUserId = PkUserId FROM tblUser WHERE FirstName = @FirstName AND LastName = @LastName AND Email = @Email)

if @FkUserId is Null then
BEGIN
INSERT INTO tblUser(FirstName, LastName, Email)
VALUES (@FirstName, LastName, @Email)

-- assuming that the field is an identity, fill it in autoamtically
SET @FkUserId = SCOPE_IDENTITY()

INSERT INTO tblUserRate (FkUserId, Rate, RateStart, RateEnd, Active)
VALUES (@FkUserId, @Rate, @RateStart, @RateEnd, @Active)
END
ELSE
BEGIN
UPDATE tblUser
SET FirstName = @FirstName, LastName = @LastName, Email = @Email
WHERE PkUserId = @FkUserId

UPDATE tblUserRates
SET @Rate, @RateStart, @RateEnd, @Active
WHERE FkUserId = @FkUserId

END


0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20076193

      @FirstName varchar(40),
      @LastName varchar(40),
      @LoginName varchar(100),
      @FkUserId int,
      @Rate money,
      @RateStart datetime,
      @RateEnd datetime,
      @Active varchar(1)

SELECT @FkUserId =  PkUserId FROM tblUser WHERE FirstName = @FirstName AND LastName = @LastName  AND Email = @Email
BEGIN TRAN
if @FkUserId is Null then
BEGIN
      INSERT INTO tblUser(FirstName, LastName, Email)
      VALUES (@FirstName, LastName, @Email)
      IF @@ERROR<>0 OR @@ROWCOUNT = 0  --- If some error happened or No rows were inserted, then roll back
      BEGIN
            ROLLBACK TRAN
            SELECT -201 AS ErrValue
            RETURN      
      END
      INSERT INTO tblUserRate (FkUserId, Rate, RateStart, RateEnd, Active)
      VALUES (@FkUserId, @Rate, @RateStart, @RateEnd, @Active)
      IF @@ERROR<>0 OR @@ROWCOUNT = 0
      BEGIN
            ROLLBACK TRAN
            SELECT -202 AS ErrValue
            RETURN      
      END

END

if @FkUserId is NOT Null then
BEGIN

      UPDATE tblUser
      SET FirstName = @FirstName, LastName = @LastName, Email = @Email
      WHERE PkUserId = @FkUserId
      IF @@ERROR<>0 OR @@ROWCOUNT = 0
      BEGIN
            ROLLBACK TRAN
            SELECT -203 AS ErrValue
            RETURN      
      END
      UPDATE tblUserRates
      SET Rate = @Rate, RateStart= @RateStart, RateEnd = @RateEnd, Active = @Active
      WHERE FkUserId = @FkUserId
      
      IF @@ERROR<>0 OR @@ROWCOUNT = 0
      BEGIN
            ROLLBACK TRAN
            SELECT -204 AS ErrValue
            RETURN      
      END
      
END
COMMIT TRAN
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1000 total points
ID: 20076209
In case you need to insert the identity value as Angel mentioned, change the Proc to something like this

CREATE PROC someProcName
      @FirstName varchar(40),
      @LastName varchar(40),
      @LoginName varchar(100),
      @FkUserId int,
      @Rate money,
      @RateStart datetime,
      @RateEnd datetime,
      @Active varchar(1),
      @Email VARCHAR(100)
AS
DECLARE @Err INT, @Rows INT

SELECT @FkUserId =  PkUserId FROM tblUser WHERE FirstName = @FirstName AND LastName = @LastName  AND Email = @Email
BEGIN TRAN
if @FkUserId is Null  
BEGIN
      INSERT INTO tblUser(FirstName, LastName, Email)
      VALUES (@FirstName, @LastName, @Email)
      SELECT @Err = @@ERROR , @Rows = @@ROWCOUNT, @FkUserId  =SCOPE_IDENTITY()
      IF @Err<>0 OR @Rows = 0  --- If some error happened or No rows were inserted, then roll back
      BEGIN
            ROLLBACK TRAN
            SELECT -201 AS ErrValue
            RETURN      
      END
      INSERT INTO tblUserRate (FkUserId, Rate, RateStart, RateEnd, Active)
      VALUES (@FkUserId, @Rate, @RateStart, @RateEnd, @Active)
      IF @@ERROR<>0 OR @@ROWCOUNT = 0
      BEGIN
            ROLLBACK TRAN
            SELECT -202 AS ErrValue
            RETURN      
      END

END

ELSE
BEGIN

      UPDATE tblUser
      SET FirstName = @FirstName, LastName = @LastName, Email = @Email
      WHERE PkUserId = @FkUserId
      IF @@ERROR<>0 OR @@ROWCOUNT = 0
      BEGIN
            ROLLBACK TRAN
            SELECT -203 AS ErrValue
            RETURN      
      END
      UPDATE tblUserRates
      SET Rate = @Rate, RateStart= @RateStart, RateEnd = @RateEnd, Active = @Active
      WHERE FkUserId = @FkUserId
      
      IF @@ERROR<>0 OR @@ROWCOUNT = 0
      BEGIN
            ROLLBACK TRAN
            SELECT -204 AS ErrValue
            RETURN      
      END
      
END
COMMIT TRAN
0
 

Author Comment

by:freezegravity
ID: 20078962
Hello aneeshattingal:

Thanks for doing a wonderful job, even navigating an error I put on my original question.

@LoginName varchar(100) should have been @Email varchar(100)

I tried your stored procedure. The only problem is that I did not not supply FkUserId to the stored procedure so it gave me the expected error

"Msg 201, Level 16, State 4, Procedure sprInsertUpdateUser, Line 0
Procedure or Function 'sprInsertUpdateUser' expects parameter '@FkUserId', which was not supplied."

You see, I cannot know in advance what the FkUserId might be for a brand new insert of an employee. So, before Inserting into tblUserRates, you would again have to query this:

SELECT @FkUserId =  PkUserId FROM tblUser WHERE FirstName = @FirstName AND LastName = @LastName  AND Email = @Email

does that change your code any?

THANKS!


0
 

Author Comment

by:freezegravity
ID: 20079122
Woops ... in my haste, I forgot to say hello and thanks to 'angellll" and "jaiganeshsrinivasan." Your contribution was certainly very helpful!
0
 

Author Comment

by:freezegravity
ID: 20079123
SO THANKYOU!
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 800 total points
ID: 20079147
change

      @FkUserId int,

into

      @FkUserId int = null,
0
 

Author Comment

by:freezegravity
ID: 20086584
THANKS ALL for an excellent answer!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Loops Section Overview

807 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