Optimize Store Proc - 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 2012 Forums

Transact-SQL (2012)

Optimize Store Proc

Author

Topic

stahorse
Yak Posting Veteran

86 Posts

Posted - 2013-11-01 : 07:52:13

Given this table:

CREATE TABLE [dbo].[Product](
[ProductID] [int] IDENTITY(1,1) NOT NULL,
[Product_Code] [nvarchar](255) NOT NULL UNIQUE,
[Product_Qty] [int] NULL,
CONSTRAINT pk_Product_ID PRIMARY KEY (ProductID)
) ON [PRIMARY]

Can we optimize this Store Proc?

CREATE PROCEDURE [dbo].[AlterProducts]
AS
BEGIN
DECLARE @product_Id int, @product_Code nvarchar(MAX)

DECLARE count_cursor CURSOR FOR
SELECT ProductID, Product_Code
FROM Product

OPEN count_cursor

FETCH NEXT FROM count_cursor
INTO @product_Id, @product_Code

WHILE @@FETCH_STATUS = 0
BEGIN
Print @product_Code
UPDATE Product
SET Product_Code = LEFT(@product_Code,@product_Id) + '-Altered'
WHERE ProductID = @product_Id

FETCH NEXT FROM count_cursor
INTO @product_Id, @product_Code
END
CLOSE count_cursor
DEALLOCATE count_cursor

END

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-11-01 : 08:11:36

CREATE PROCEDURE [dbo].[AlterProducts]
(
@product_Id int, @product_Code nvarchar(MAX)
)AS
BEGIN
UPDATE Product
SET Product_Code = LEFT(@product_Code,@product_Id) + '-Altered'
WHERE product_id=@product_id
END;

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources