SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Optimize Store Proc
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stahorse
Yak Posting Veteran

85 Posts

Posted - 11/01/2013 :  07:52:13  Show Profile  Reply with Quote
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

India
22761 Posts

Posted - 11/01/2013 :  08:11:36  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

Edited by - madhivanan on 11/01/2013 08:12:46
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000