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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stahorse
Yak Posting Veteran

86 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
22864 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  
 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.04 seconds. Powered By: Snitz Forums 2000