Upsert Proc using IF logic

Prev: Iterating XML in SQL Server 2005 (or 2008) stored procedure
Next: 'Having' question

From: Mr.Kane on 20 Jul 2008 22:51

---

Here is the sample code:


CREATE PROCEDURE [dbo].[WDSHE_CATEGORIES_INSERTUPDATE]
@cat_name varchar(50),
@status char(1),
@na_only char(1)
AS

declare @msgcnt int,
@msg_ok varchar(100),
@msg_invalid varchar(100)
set @msg_ok = 'Value Added Successfully'
set @msg_invalid = 'Value Already Exists'
BEGIN
UPDATE dbo.WDSHE_CATEGORIES
SET cat_name = @cat_name, status = @status, na_only = @na_only
WHERE cat_name = @cat_name
IF @@rowcount = 0

INSERT INTO WDSHE_CATEGORIES (CAT_NAME, STATUS, NA_ONLY)
VALUES (@cat_name, @status, @na_only)
SET @msgcnt = 0
SELECT results = @msgcnt , reason = @msg_ok
return @msgcnt
END

------------------------------------------------------------------------------------------------------------------------------------
I am trying to add something like this:

Else IF @@rowcount <> 0
SET @msgcnt = -1
SELECT results = @msgcnt , reason = @msg_invalid
return @msgcnt

is it possible to add this code segment within the existing Begin End
block
or will I have to create a separate block?

From: Aaron Bertrand [SQL Server MVP] on 20 Jul 2008 23:19

---

What purpose does that BEGIN/END block serve?

Typically this operation is:

CREATE PROCEDURE dbo.foo
@params
AS
BEGIN
SET NOCOUNT ON;

UPDATE ...

IF @@ROWCOUNT = 0
BEGIN

INSERT ...

END
END
GO


On 7/20/08 10:51 PM, in article
7608cbdd-0a0d-4b37-813f-59e2b3735636(a)v28g2000pro.googlegroups.com, "Mr.Kane"
<kane.marc(a)gmail.com> wrote:

> Here is the sample code:
>
>
> CREATE PROCEDURE [dbo].[WDSHE_CATEGORIES_INSERTUPDATE]
> @cat_name varchar(50),
> @status char(1),
> @na_only char(1)
> AS
>
> declare @msgcnt int,
> @msg_ok varchar(100),
> @msg_invalid varchar(100)
> set @msg_ok = 'Value Added Successfully'
> set @msg_invalid = 'Value Already Exists'
> BEGIN
> UPDATE dbo.WDSHE_CATEGORIES
> SET cat_name = @cat_name, status = @status, na_only = @na_only
> WHERE cat_name = @cat_name
> IF @@rowcount = 0
>
> INSERT INTO WDSHE_CATEGORIES (CAT_NAME, STATUS, NA_ONLY)
> VALUES (@cat_name, @status, @na_only)
> SET @msgcnt = 0
> SELECT results = @msgcnt , reason = @msg_ok
> return @msgcnt
> END
>
> ------------------------------------------------------------------------------
> ------------------------------------------------------
> I am trying to add something like this:
>
> Else IF @@rowcount <> 0
> SET @msgcnt = -1
> SELECT results = @msgcnt , reason = @msg_invalid
> return @msgcnt
>
> is it possible to add this code segment within the existing Begin End
> block
> or will I have to create a separate block?
>
>

From: Steve Kass on 20 Jul 2008 23:20

---

It's not clear what you want to do in each case, but maybe
this is what you want:

BEGIN
UPDATE dbo.WDSHE_CATEGORIES
SET cat_name = @cat_name, status = @status, na_only = @na_only
WHERE cat_name = @cat_name

IF @@rowcount = 0 BEGIN
INSERT INTO WDSHE_CATEGORIES (CAT_NAME, STATUS, NA_ONLY)
VALUES (@cat_name, @status, @na_only)
SET @msgcnt = 0
END ELSE
SET @msgcnt = -1

SELECT results = @msgcnt , reason = @msg_ok
return @msgcnt
END


Steve Kass
Drew University
http://www.stevekass.com

Mr.Kane wrote:

>Here is the sample code:
>
>
>CREATE PROCEDURE [dbo].[WDSHE_CATEGORIES_INSERTUPDATE]
>@cat_name varchar(50),
>@status char(1),
>@na_only char(1)
>AS
>
>declare @msgcnt int,
> @msg_ok varchar(100),
> @msg_invalid varchar(100)
> set @msg_ok = 'Value Added Successfully'
> set @msg_invalid = 'Value Already Exists'
>BEGIN
>UPDATE dbo.WDSHE_CATEGORIES
>SET cat_name = @cat_name, status = @status, na_only = @na_only
>WHERE cat_name = @cat_name
>IF @@rowcount = 0
>
>INSERT INTO WDSHE_CATEGORIES (CAT_NAME, STATUS, NA_ONLY)
>VALUES (@cat_name, @status, @na_only)
>SET @msgcnt = 0
>SELECT results = @msgcnt , reason = @msg_ok
>return @msgcnt
>END
>
>------------------------------------------------------------------------------------------------------------------------------------
>I am trying to add something like this:
>
>Else IF @@rowcount <> 0
>SET @msgcnt = -1
>SELECT results = @msgcnt , reason = @msg_invalid
>return @msgcnt
>
>is it possible to add this code segment within the existing Begin End
>block
>or will I have to create a separate block?
>
>
>
>

From: Tom Cooper on 20 Jul 2008 23:28

---

I'm not sure from your description exactly what you want. But I suspect it
is something like:

CREATE PROCEDURE [dbo].[WDSHE_CATEGORIES_INSERTUPDATE]
@cat_name varchar(50),
@status char(1),
@na_only char(1)
AS

declare @msgcnt int,
@msg_ok varchar(100),
@msg_invalid varchar(100)
set @msg_ok = 'Value Added Successfully'
set @msg_invalid = 'Value Already Exists'

UPDATE dbo.WDSHE_CATEGORIES
SET cat_name = @cat_name, status = @status, na_only = @na_only
WHERE cat_name = @cat_name
IF @@rowcount = 0
BEGIN
INSERT INTO WDSHE_CATEGORIES (CAT_NAME, STATUS, NA_ONLY)
VALUES (@cat_name, @status, @na_only)
SET @msgcnt = 0
SELECT results = @msgcnt , reason = @msg_ok
END
ELSE
BEGIN
SET @msgcnt = -1
SELECT results = @msgcnt , reason = @msg_invalid
END
return @msgcnt
go

Tom
"Mr.Kane" <kane.marc(a)gmail.com> wrote in message
news:7608cbdd-0a0d-4b37-813f-59e2b3735636(a)v28g2000pro.googlegroups.com...
> Here is the sample code:
>
>
> CREATE PROCEDURE [dbo].[WDSHE_CATEGORIES_INSERTUPDATE]
> @cat_name varchar(50),
> @status char(1),
> @na_only char(1)
> AS
>
> declare @msgcnt int,
> @msg_ok varchar(100),
> @msg_invalid varchar(100)
> set @msg_ok = 'Value Added Successfully'
> set @msg_invalid = 'Value Already Exists'
> BEGIN
> UPDATE dbo.WDSHE_CATEGORIES
> SET cat_name = @cat_name, status = @status, na_only = @na_only
> WHERE cat_name = @cat_name
> IF @@rowcount = 0
>
> INSERT INTO WDSHE_CATEGORIES (CAT_NAME, STATUS, NA_ONLY)
> VALUES (@cat_name, @status, @na_only)
> SET @msgcnt = 0
> SELECT results = @msgcnt , reason = @msg_ok
> return @msgcnt
> END
>
> ------------------------------------------------------------------------------------------------------------------------------------
> I am trying to add something like this:
>
> Else IF @@rowcount <> 0
> SET @msgcnt = -1
> SELECT results = @msgcnt , reason = @msg_invalid
> return @msgcnt
>
> is it possible to add this code segment within the existing Begin End
> block
> or will I have to create a separate block?
>
>

From: Mr.Kane on 23 Jul 2008 00:56

---

Just wanted to thank you guys for taking the time to help.
My goal was to avoid the often used "If Exists" logic in order to
reduce the scan count and the subsequent logical/physical reads that
are made when using this approach
1 read for the existence check
1 read for the update or insert (depending on existence)

the "Upsert" method only results in the Optimizer conducting 1 total
scan and handling the update/insert in one pass.

Below is the updated code, again thanks for lending a hand:

ALTER PROCEDURE [dbo].[WDSHE_CATEGORIES_INSERTUPDATE]
@cat_name varchar(50),
@status char(1),
@na_only char(1)
AS

declare @msgcnt int,
@msg_ok varchar(100),
@msg_invalid varchar(100)
set @msg_ok = 'Value Added Successfully'
set @msg_invalid = 'Value Already Exists'
BEGIN
UPDATE dbo.WDSHE_CATEGORIES
SET cat_name = @cat_name, status = @status, na_only = @na_only
WHERE cat_name = @cat_name
IF @@rowcount = 0
BEGIN
INSERT INTO WDSHE_CATEGORIES (CAT_NAME, STATUS, NA_ONLY)
VALUES (@cat_name, @status, @na_only)
SET @msgcnt = 0
SELECT results = @msgcnt , reason = @msg_ok
return @msgcnt
END
ELSE
SET @msgcnt = -1
SELECT results = @msgcnt , reason = @msg_invalid
END













On Jul 20, 8:28 pm, "Tom Cooper"
<tomcoo...(a)comcast.no.spam.please.net> wrote:
> I'm not sure from your description exactly what you want. But I suspect it
> is something like:
>
> CREATE PROCEDURE [dbo].[WDSHE_CATEGORIES_INSERTUPDATE]
> @cat_name varchar(50),
> @status char(1),
> @na_only char(1)
> AS
>
> declare @msgcnt int,
> @msg_ok varchar(100),
> @msg_invalid varchar(100)
> set @msg_ok = 'Value Added Successfully'
> set @msg_invalid = 'Value Already Exists'
>
> UPDATE dbo.WDSHE_CATEGORIES
> SET cat_name = @cat_name, status = @status, na_only = @na_only
> WHERE cat_name = @cat_name
> IF @@rowcount = 0
> BEGIN
> INSERT INTO WDSHE_CATEGORIES (CAT_NAME, STATUS, NA_ONLY)
> VALUES (@cat_name, @status, @na_only)
> SET @msgcnt = 0
> SELECT results = @msgcnt , reason = @msg_ok
> END
> ELSE
> BEGIN
> SET @msgcnt = -1
> SELECT results = @msgcnt , reason = @msg_invalid
> END
> return @msgcnt
> go
>
> Tom"Mr.Kane" <kane.m...(a)gmail.com> wrote in message
>
> news:7608cbdd-0a0d-4b37-813f-59e2b3735636(a)v28g2000pro.googlegroups.com...
>
> > Here is the sample code:
>
> > CREATE PROCEDURE [dbo].[WDSHE_CATEGORIES_INSERTUPDATE]
> > @cat_name varchar(50),
> > @status char(1),
> > @na_only char(1)
> > AS
>
> > declare @msgcnt int,
> > @msg_ok varchar(100),
> > @msg_invalid varchar(100)
> > set @msg_ok = 'Value Added Successfully'
> > set @msg_invalid = 'Value Already Exists'
> > BEGIN
> > UPDATE dbo.WDSHE_CATEGORIES
> > SET cat_name = @cat_name, status = @status, na_only = @na_only
> > WHERE cat_name = @cat_name
> > IF @@rowcount = 0
>
> > INSERT INTO WDSHE_CATEGORIES (CAT_NAME, STATUS, NA_ONLY)
> > VALUES (@cat_name, @status, @na_only)
> > SET @msgcnt = 0
> > SELECT results = @msgcnt , reason = @msg_ok
> > return @msgcnt
> > END
>
> > ------------------------------------------------------------------------------------------------------------------------------------
> > I am trying to add something like this:
>
> > Else IF @@rowcount <> 0
> > SET @msgcnt = -1
> > SELECT results = @msgcnt , reason = @msg_invalid
> > return @msgcnt
>
> > is it possible to add this code segment within the existing Begin End
> > block
> > or will I have to create a separate block?

 | 
Pages: 1
Prev: Iterating XML in SQL Server 2005 (or 2008) stored procedure
Next: 'Having' question