pls correct the code when authorise it shows error

Question

1.00/5 (2 votes)

See more:

SQL

create table tbl_cyl_gas_master
(
serial_no int,
gas_name varchar(50),
tran_stat varchar(50))

SQL

alter procedure gas
(
@action varchar(20),
@serial_no int,
@gas_name varchar(50),
@tran_stat varchar(50)
)
as
begin
      
       select @tran_stat =(CASE WHEN @action='ADD' then 'FR'
                                                         when @action='Update' then 'FR'
                                                         when @action='Authorise' then 'AU'
                                                         else 'DE'
                                                end )
       --declare @trannew varchar(10),
      
if @action='Add'
       begin
       insert into tbl_Cyl_gas_master values (@gas_name,@tran_stat)
       end
else if @action='Update'
       begin
             
              if @tran_stat == 'FR' && @serial_no==@serial_no
             
                     update tbl_Cyl_gas_master set gas_name=@gas_name where serial_no=@serial_no
             
              else
      
                     RAISERROR ('Transaction not is fresh',16,1);
             
       end
else if @action='Authorise'
       begin
              --select @tran_stat from tbl_cyl_gas_master where serial_no=@serial_no
              if @tran_stat = 'FR'
             
                     update tbl_Cyl_gas_master set tran_stat=@tran_stat where serial_no=@serial_no
             
             
              else
                    
                     RAISERROR ('Transaction already Authorised',16,1)
             
                          
       end
else
       delete gas_name from tbl_Cyl_gas_master where serial_no=@serial_no
 
end

Posted 1-Apr-14 6:04am

vanarajranjit

Updated 1-Apr-14 19:41pm

Andrius Leonavicius

v4

Add a Solution

Comments

Schatak 1-Apr-14 13:23pm

what error you are getting?

2 solutions

Solution 1

Hi,

Replace this line:

SQL

if @tran_stat == 'FR' && @serial_no==@serial_no

with this:

SQL

if (@tran_stat = 'FR' and @serial_no = @serial_no)

and this line:

SQL

insert into tbl_Cyl_gas_master values (@gas_name,@tran_stat)

with this:

SQL

insert into tbl_Cyl_gas_master (gas_name, tran_stat) values (@gas_name, @tran_stat)

Posted 1-Apr-14 7:29am

Andrius Leonavicius

Updated 1-Apr-14 7:39am

v3

Comments

vanarajranjit 1-Apr-14 22:51pm

When authorise the serialno it is in fresh status only but it is not authorising it goes to else part transaction already authorised and when transaction is authorised status it should not update but here it is updating. Pls check and revert back.

Andrius Leonavicius 2-Apr-14 2:11am

Revert back? What kind of RDBMS are you using? As I know, RAISERROR statement should indicate that you're using a SQL Server. So I was checking for syntax errors at first.

Speaking about the code, I don't know why you're using both if @action and if @tran_stat. It seems like unnecessary complexity... I don't know all details, but try to change this line:
--select @tran_stat from tbl_cyl_gas_master where serial_no=@serial_no
if @tran_stat = 'FR'
with this:
if @tran_stat = 'AU'

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)

Schatak · Accepted Answer · 2014-04-01T07:33:00

Solution 2

Little modifications in your code:

Try this

CREATE procedure gas
(
@action varchar(20),
@serial_no int,
@gas_name varchar(50),
@tran_stat varchar(50)
)
as
begin
select @tran_stat =(CASE WHEN @action='ADD' then 'FR' when @action='Update' then 'FR' when @action='Authorise' then 'AU'
else 'DE' end )
--declare @trannew varchar(10),

if @action='Add'
begin
	insert into tbl_Cyl_gas_master(gas_name,tran_stat) values (@gas_name,@tran_stat)
end
else if @action='Update'
begin
	if ((@tran_stat = 'FR') AND (@serial_no=@serial_no))
		update tbl_Cyl_gas_master set gas_name=@gas_name where serial_no=@serial_no
	else
		RAISERROR ('Transaction not is fresh',16,1);
	end
else if @action='Authorise'
	begin
	if @tran_stat = 'FR'
		update tbl_Cyl_gas_master set tran_stat=@tran_stat where serial_no=@serial_no
	else
		RAISERROR ('Transaction already Authorised',16,1)
	end
else
	delete gas_name from tbl_Cyl_gas_master where serial_no=@serial_no
end
GO

Posted 1-Apr-14 7:33am

Schatak

Comments

vanarajranjit 1-Apr-14 23:00pm

Pls check the above reply for solutions 1

Schatak 2-Apr-14 2:05am

when you are passing 'Authorise' in your Stored Procedure and in a case statement you are saying if when @action='Authorise' then 'AU'
so then next condition "else if @action='Authorise'" is not matching that's why its always saying 'Transaction already Authorised'

vanarajranjit 2-Apr-14 2:19am

When authorise the serialno it is in fresh status only but it is not authorising it goes to else part transaction already authorised and when transaction is authorised status it should not update but here it is updating. Pls check and revert back.

vanarajranjit 2-Apr-14 2:21am

When authorise the serialno it is in fresh status only but it is not authorising it goes to else part transaction already authorised and when transaction is authorised status it should not update but here it is updating. Pls check and revert back.add what are the conditions and controls,features in your mind pls add

vanarajranjit 2-Apr-14 2:23am

I am fresher to sql I am not having much idea now you understand what I written the code so pls make all changesin that code and give to me

Schatak 2-Apr-14 2:57am

as per i understand from your requirements following is the code. and you made so many mistakes in SQL. Delete query was totally disaster. fixed it, run the following code

ALTER procedure gas
(
@action varchar(20),
@serial_no int,
@gas_name varchar(50),
@tran_stat varchar(50)
)
as
BEGIN
SELECT @tran_stat = tran_stat, @serial_no = serial_no, @gas_name = gas_name FROM tbl_Cyl_gas_master WHERE serial_no=@serial_no
IF(@tran_stat ='')
SET @tran_stat = CASE when @action='ADD' then 'FR' END
IF(@action ='add')
BEGIN
INSERT INTO tbl_Cyl_gas_master(serial_no,gas_name,tran_stat) VALUES (@serial_no,@gas_name,@tran_stat)
END
ELSE IF @action='update'
BEGIN
IF (@tran_stat = 'FR')
UPDATE tbl_Cyl_gas_master set gas_name=@gas_name, @tran_stat = @tran_stat WHERE serial_no=@serial_no
ELSE
RAISERROR ('Transaction not is fresh',16,1);
END
ELSE IF @action='authorise'
BEGIN
IF (@tran_stat = 'FR')
UPDATE tbl_Cyl_gas_master SET tran_stat='AU' WHERE serial_no=@serial_no
ELSE
RAISERROR ('Transaction already Authorised',16,1)
END
ELSE
DELETE from tbl_Cyl_gas_master where serial_no=@serial_no
END
GO

vanarajranjit 2-Apr-14 3:29am

Thanks for your reply it working good.any other controls I have to apply?

Schatak 2-Apr-14 3:35am

if its helpful then accept it solution and rate it :)

vanarajranjit 2-Apr-14 4:25am

Pls add more controls to that code..it will helpful me a lot

Schatak 2-Apr-14 4:28am

what controls you want?

vanarajranjit 2-Apr-14 4:35am

no transaction exists with that no or not,if tran is there means RAISERROR ( 'transaction alread exists. cannot ADD', 16, 1) and then select query also need ,and then if the tran is deleted means the whole row is deleted i want in tran_stat it shows DE like that.if you need extra column in table add it..

vanarajranjit 2-Apr-14 4:50am

And also if I inserted oxygen for the column gas_name again some other person inserting same oxygen then user should know the oxygen transaction is already there it should shows... For that I need the controls