My First SP - Need advice - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

My First SP - Need advice

Author

Topic

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-02-23 : 22:01:13

Dear Gurus,
I created my first SP. I've tested the SP, it works like what I wanted it to be. However, I still need your expert opinion. Somehow I think the SP can be written in a more optimized or simplier or better way.

A BIG THANK YOU IN ADVANCE!!!

What the SP does is:
1. Update the table called _myTable
2. User pass in 3 variables - myID, itemNum and Quantity to be updated.
3. After record updated, there are chances that the the same itemNum exist somewhere.
4. If same record (itemNum) is found, then I have to combine the quantity into single record.

-- This is my Table
CREATE TABLE [dbo].[_myTable](
[myID] [int] IDENTITY(1,1) NOT NULL,
[myItemNum] [int] NULL,
[myQuantity] [int] NULL,
[myStatus] [bit] NULL
) ON [PRIMARY]

-- My First SP
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_myFirstSP]
@myID int,
@myItemNum int,
@myQuantity int
AS
BEGIN
DECLARE @Sum_myQuantity int
-- SET NOCOUNT ON;

-- 1. Update the Record according to User Input
UPDATE _myTable SET
myItemNum = @myItemNum,
myQuantity = @myQuantity
WHERE myID = @myID;

-- 2. After Record updated by User could be having same ItemNum from other Records. Check for duplicated records
SELECT myItemNum FROM _myTable WHERE myItemNum = @myItemNum AND myStatus = 1;

-- 3. If Duplicated record found then combine the records by...
IF @@ROWCOUNT > 1
BEGIN
-- 4. Get the Sum of the Finance Quantity and assign it to a variable
SELECT @Sum_myQuantity = (SELECT sum(myQuantity) as Sum_myQuantity FROM _myTable
WHERE myItemNum = @myItemNum AND myStatus = 1);

-- 5. Delete all the old records by set the Status to 0
UPDATE _myTable
SET myStatus = 0
WHERE myItemNum = @myItemNum AND myStatus = 1;

-- 6. Insert Sum of the Finance Quantity into new record
INSERT INTO _myTable (myItemNum, myQuantity, myStatus)
VALUES (@myItemNum, @Sum_myQuantity, 1);

END
END

p/s: somehow I just feel that something is wrong with this line...
SELECT @Sum_myQuantity = (SELECT sum(myQuantity..... )) ;

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-23 : 22:35:53

you can simplified the process by
1. get any existing quantity for itemnum and add it to @myQuantity
3. perform the update
4. delete any other record with same itemnum


select @myQuantity = @myQuantity + isnull(myQuantity, 0)
from   _myTable
where  myItemNum =  @myItemNum
and    myID      <> @myID

update _myTable
set    myItemNum  = @myItemNum,
       myQuantity = @myQuantity
where  myID       = @myID;

delete _myTable
where  myItemNum  = @myItemNum
and    myID       <> @myID;

KH
[spoiler]Time is always against us[/spoiler]

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-02-24 : 19:58:51

quote:
Originally posted by khtan

you can simplified the process by
1. get any existing quantity for itemnum and add it to @myQuantity
3. perform the update
4. delete any other record with same itemnum

Awesome, great idea. I just I'll work towards this. Thanks! Again, you saved my life :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources