loop in Stored Procedure

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

I have table called SpDet having pcode(product code),qty(quantity),vtype(voucher type sale/purchase) ,spdt(transaction date)rate ,amt etc.Now the problem is i have find the closing stock of all products between a specific date.Though i have done it for single item but i can not do it for all products can you all help me !!!!

Sql Stored Procedure

SQL

create procedure [dbo].[stocktrial3] @dtfrom date,@dtto date

as

begin

SET NOCOUNT ON ;

declare @inqty int,@outqty int,@opinqty int,@opoutqty int,@opqty int,@opclqty int,@clqty int

select @opqty=opqty from Item order by PCode

select @opinqty = SUM(qty) from SPDet where Vtype='PU' and SPDt < @dtfrom  group by PCode,Vtype order by PCode

select @opoutqty =SUM(qty) from SPDet where Vtype in ('SP','CM') and SPDt < @dtfrom  group by PCode,Vtype order by PCode

select @inqty =SUM(qty) from SPDet where Vtype='PU' and SPDt between @dtfrom and @dtto  group by PCode,Vtype order by PCode

select @outqty =SUM(qty) from SPDet where Vtype in ('SP','CM') and SPDt between @dtfrom and @dtto  group by PCode,Vtype order by PCode

set @opclqty=@opqty+ISNULL(@opinqty,0)-ISNULL(@opoutqty,0)

set @clqty=ISNULL(@opclqty,0)+ISNULL(@inqty,0)-ISNULL(@outqty,0)

select Item.Pname,@opclqty as PopQty,ISNULL(@inqty,0) as PinQty,ISNULL(@outqty,0) as POutQty,@clqty as PClqty

 from Item order by PCode

end

Result

PName   Op.Qty  InQty  OutQty   Clqty
VAIO    20  0   5   15
Lenevo Laptop   20  0   5   15
LCD 32" 20  0   5   15
LED 32" 20  0   5   15
SS  20  0   5   15
DD  20  0   5   15
Pendrive    20  0   5   15
Raid & Tailor   20  0   5   15
Laptop  20  0   5   15
Tab 20  0   5   15
Lenevo Desktop  20  0   5   15

As u can see that result is all product is same.

Posted 4-Mar-14 8:32am

Sandip Paul 491984

Updated 4-Mar-14 9:07am

Maciej Los

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2014-03-04T09:14:00

Solution 2

Wow! What a terrible solution! Sorry.

To achieve that what you need, use aggregate functions[^], eg.:

SQL

SELECT Product, SUM(Quality) AS SumOfQty
FROM TableName
GROUP BY Product
WHERE DateField BETWEEN @start AND @end

In some cases, pivot table[^] might be helpful.
You are able to achieve that using Common Table Expressions[^] too.

Posted 4-Mar-14 9:14am

Maciej Los

Comments

Sergey Alexandrovich Kryukov 4-Mar-14 15:27pm

Good point, a 5.
—SA

Maciej Los 4-Mar-14 15:31pm

Thank you, Sergey ;)

Sergey Alexandrovich Kryukov · Answer 2 · 2014-03-04T09:09:00

Solution 1

The problem is not clearly explained. For "looping" in SQL stored procedures, please see, for example:
using for loop in stored procedure[^],
http://stackoverflow.com/questions/16115043/sql-stored-procedure-while-loop[^].

—SA

Posted 4-Mar-14 9:09am

Sergey Alexandrovich Kryukov

Comments

Maciej Los 4-Mar-14 15:16pm

Useful links, but i think OP does not need any loop. Have a look at my answer.
BTW: +5!

Sergey Alexandrovich Kryukov 4-Mar-14 15:30pm

Thank you, Maciej.
I responded exclusively to the title of the question, though the linked threads provide interesting ideas on "looping".
However, I kinda failed to understand that "terrible solution" by OP. So, your point is much better.
—SA

Maciej Los 4-Mar-14 16:33pm

Sergey, Paul (OriginalGriff) point me out that improving question's subject like that: [Not a question] is undesirable action. CP team recommend to properly report it, instead make correction to the question. For example: http://www.codeproject.com/Questions/738746/Event-handeling-program-in-csharp

Sorry for off-topic comment, but i didn't knew how to inform you.

Sergey Alexandrovich Kryukov 4-Mar-14 16:35pm

Thank you for the note...
—SA