sql Updation Problem

Question

1.00/5 (1 vote)

See more:

Hi Sir,

Is there any Prob with the Below SQL Query.?

SQL

DECLARE @Status varchar;
DeCLARE @Office_Status varchar;


Set @Status = 'Viewed';
Set @Office_Status= (Select Office_Status From tbl_Office
Where Master_ID = 1);

if (@Status = 'Viewed' AND @Office_Status= 'Finished')

BEGIN
Update tbl_Office Set Office_Status = @Status
Where Master_ID = 1

END

The Current "Office_Status" is "Finished" , But it is not yet Updating. Please Help me.

Thanks,

Dileep

Posted 22-Aug-12 6:36am

dilzz

Add a Solution

Comments

Santhosh Kumar Jayaraman 22-Aug-12 12:58pm

It looks ok. but can you check inside if loop whether its hitting loop by putting a print statement inside if loop

4 solutions

Solution 4

If I'm not reading the code too sloppy, you could perhaps both simplify and enhance the code you have written. If you give the following statement a try, it should do the exact same update but without variables and if statements (since they are embedded in the update statement):

SQL

UPDATE tbl_Office 
SET    Office_Status = 'Viewed'
WHERE  Master_ID = 1
AND    Office_Status = 'Finished';

Posted 22-Aug-12 10:58am

Wendelius

Comments

__TR__ 23-Aug-12 2:46am

My 5.
I had come to improve my solution to include the above update statement. I guess i don't need to include it anymore :)

Wendelius 23-Aug-12 9:00am

Thanks :)

Solution 1

Hi,
It looks like one of the test conditons in If statement is not true causing the update statement to not get executed. My guees is @Office_Status is not equal to 'Finished'.
An easy way to check it is by adding the below statement before the if condition to see the values of the variables.

SQL

SELECT @Status AS Status, @Office_Status AS Office_Status

Posted 22-Aug-12 6:52am

__TR__

Comments

dilzz 22-Aug-12 13:00pm

When i add the query as u mentiond then my out put is
Status = N and Office_Status = V

Don't know why these types of datas are shown, Please advice me

Thanks

__TR__ 22-Aug-12 13:05pm

Try declaring your variables as shown below.
DECLARE @Status varchar(20);
DECLARE @Office_Status varchar(20);

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)

Santhosh Kumar Jayaraman · Accepted Answer · 2012-08-22T07:10:00

Ok the issue is when you declare just varchar it can hold only one character inside that variable.

varchar is equal to varchar(1)

You need to increase the size.

Also one more thing i noticed in your query is

if (@Status = 'Viewed' AND @Office_Status= 'Finished')

Anyhow you are setting @status inside the query block. So why is it required?

just make it as

if( @Office_Status= 'Finished')

dilzz · Accepted Answer · 2012-08-22T07:10:00

Solution 2

HI I got the Solution,

In this query i have Delcelear @Office_Status and @Status as varchar,
Then it is take it as only one charector,

When i have DECLARE the variavle as Varchar(50) then it take as the whole word, and it is working fine now.

Thanks for every one..

Posted 22-Aug-12 7:10am

dilzz

sql Updation Problem

4 solutions

Solution 4

Solution 1

Solution 3

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us