sql Command not Returning Expected answer

Question

0.00/5 (No votes)

See more:

SQL

Alter Procedure AddStockToBranch
@ItemCode varchar(200)=NULL,
@BranchName varchar(150)=NULL,
@Qty int = NULL,
@TotalUsed int = NULL
AS
if exists(select ItemCode from StockInBranches
            where ItemCode = @ItemCode and BranchName = @BranchName)
begin
    UPDATE StockInBranches  SET Qty = @Qty + 
    (Select Qty from StockInBranches Where ItemCode = @ItemCode and BranchName = @BranchName) Where 
    ItemCode=@ItemCode and BranchName = @BranchName
end
else
begin
    Insert Into StockInBranches(ItemCode,BranchName,Qty,TotalUsed)
                    values     (@ItemCode,@BranchName,@Qty,@TotalUsed)
end

this procedure not returning expected result , Qty value is always giving zero even I pass the value to @Qty. please check the code what I did the mistake there.

Posted 8-Dec-14 2:51am

tastini

Add a Solution

Comments

Maria Lopez 8-Dec-14 9:02am

I think you set @Qty parameter default value as null which cause the problem

tastini 8-Dec-14 9:06am

where I made default value as null ? how I can change it?

jaket-cp 9-Dec-14 6:10am

@Qty int = NULL, is where the default value is set, to change do like @Qty int = 0, or @Qty int = 55, etc.
http://technet.microsoft.com/en-us/library/ms189330(v=sql.105).aspx

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)

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) · Answer 1 · 2014-12-08T03:02:00

Solution 1

You are actually Inserting or Updating some records matching your conditions, but you are not returning anything.

If you want to return @Qty, then at the last SELECT it.

SQL

SELECT @Qty

Posted 8-Dec-14 3:02am

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

Comments

tastini 8-Dec-14 9:05am

I am inserting Value , I pass all the parameters but Qty and totalused always inserting or updating "0" or NULL value.

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 8-Dec-14 9:26am

First go to Database Management Studio, then pass correct data and test, if it is working or not. Also write "print" statements inside the if and else clause, so that you will know, inside which block it is going. Also inside the blocks print the @Qty and @TotalUsed parameters.

If you get everything correct, then problem is while passing the data from the page. So, try it out.

/\jmot · Answer 2 · 2014-12-08T23:51:00

Solution 2

try..

SQL

Begin

if @Qty  is null
begin
set @Qty=0

begin
    UPDATE StockInBranches  SET Qty = @Qty + 
    isnull(Cast(Select Qty from StockInBranches Where ItemCode = @ItemCode and BranchName = @BranchName) as int),0) Where 
    ItemCode=@ItemCode and BranchName = @BranchName
end
End

Posted 8-Dec-14 23:51pm

/\jmot

Comments

tastini 9-Dec-14 6:31am

I have multiple branches ...

/\jmot 9-Dec-14 6:35am

so what's the problem?
this query updating the Stock Qty for branch and ItemId(ItemCode) wise.
so, i think it'll work..