Is it possible to Avoid Loop statement

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

General SQL Server Forums

New to SQL Server Programming

Is it possible to Avoid Loop statement

Author

Topic

dhinasql
Posting Yak Master

195 Posts

Posted - 2011-05-29 : 22:21:13

Friends,

I have temp table with product information, for all the product id and serial number i want to calculate the deduction value, for calculating the deduction value lot of logic are there, so the deduction value will be calculated in separate Stored Procedure , and the result will be updated in some other table.

Please guide me to optimize my Stored Procedure

Example code

DECLARE @Deduction_Value MONEY =0
DECLARE @CountBills INT
SELECT @CountBills =(SELECT COUNT(1) FROM @tempBillNos)

SomeLoop Statement - I want to avoid the loop OR provide me best way to Use Loops [ For loop / while OR something else ]
BEGIN
EXEC SP__Calculate_Deduction @ProductId= @strProductCode,
@strSerialNumber=@strSlNumber,
@mnyDeductionAmount = @Deduction_Value OUTPUT

-- THE I have to get the @strProductCode and @strSlNumber from the every row of @tempBillNos and pass the parameter to SP__Calculate_Deduction and the output parameter @Deduction_Value will be used for the below Update Statement.

UPDATE tbl_Bills SET Deduction_Value = @Deduction_Value , Net_Value = Gross_Value - @Deduction_Value

END

Please help me to optimize the above scenario. Looping statement also fine , so your sample query will be much appreciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-29 : 22:27:04

what is your stored procedure doing ? can it converted to a function ?
if possible, then you can


update tbl_bills set deduction_value = dbo.fn_calcualte_deduction( . . .)
where . . .

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

dhinasql
Posting Yak Master

195 Posts

Posted - 2011-05-30 : 00:05:02

The stored procedure already used in lot of place in my system, It has some complex logic to calculate the deduction value for the product. So better we can use the same stored procedure.

If we need to use looping statement also no problem, but give the most optimized way of using for this scenario.

Thanks and looking forward to hear from you.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-30 : 00:14:48

looping will not be the optimized way of updating.

If you really want performance, perform that complex logic on the UPDATE query itself.

Next best thing is to convert the stored procedure to a UDF (User defined function) if this is possible (technically). And change the stored procedure to call the UDF

The last option will be to use cursor or while loop to do that

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

dhinasql
Posting Yak Master

195 Posts

Posted - 2011-05-30 : 00:26:36

Thanks for your response.

As of now i would like to go with While loop.

Please let me know how to do that and Execute the SP for row by row.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-30 : 00:34:02

check out the Books Online.

Syntax and examples are there

Cursor
http://msdn.microsoft.com/en-us/library/ms180169.aspx

While Loop
http://msdn.microsoft.com/en-US/library/ms178642%28v=SQL.90%29.aspx

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources