Please Solve this Error.....!!

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

Please Solve this Error.....!!

Author

Topic

palpatel090
Starting Member

1 Post

Posted - 2011-01-05 : 04:14:43

[code]
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER procedure [dbo].[InsertMarks_CASE]

@StudId int=null,
@m1 int=null,
@m2 int=null,
@m3 int=null,
@class varchar(50)=null,
@total int=null

as
begin
select @total=(@m1+@m2+@m3) from Marks
begin
(
case @total

when (@total >= 180 and @total < 210) then @class= 'First'

when(@total < 180 and @total >= 150) then @class='Second'

when (@total >=210) then @class='Dist'

when (@total < 120) then @class='Fail'
end )
end

end

begin
insert into Marks (StudId,m1,m2,m3,class) values(@StudId,@m1,@m2,@m3,@class)
end

[/code]

i got below error .

[code]
Msg 156, Level 15, State 1, Procedure InsertMarks_CASE, Line 16
Incorrect syntax near the keyword 'case'.
[/code]

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-01-05 : 04:19:43

A case is only valid as part of a select statement. From the looks of things, you want an IF here, not a case.

IF (@total >= 180 and @total < 210)
SET @class= 'First'

... similar for all the other conditions.

Is this a homework exercise?

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2011-01-05 : 04:28:40

You've got too many outer level BEGIN / END as well - I think the INSERT is "outside" your PROCEDURE. Take out the END and BEGIN just above the INSERT

And I don't think you want

select @total=(@m1+@m2+@m3) from Marks

that will perform the summation operation FOR EVERY ROW IN THE [Marks] table. You just want to add up the parameters, right?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-05 : 04:48:21

Let me guess

insert into Marks (StudId,m1,m2,m3,class)
select @StudId,@m1,@m2,@m3,
case
when @m1+@m2+@m3 >= 210 then 'Dist'
when @m1+@m2+@m3 >= 180 then 'First'
when @m1+@m2+@m3 >= 150 then 'Second'
when @m1+@m2+@m3 >= 120 then 'Pass'
else 'Fail'
end

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-05 : 04:58:20

How should marks between 120 and 149 (inclusive) be calculated?

ALTER PROCEDURE dbo.InsertMarks_CASE
(
	@StudId int = null,
	@m1 int = null,
	@m2 int = null,
	@m3 int = null,
	@class varchar(50) = null,
	@total int = null
)
AS

SET NOCOUNT ON

INSERT	dbo.Marks
	(
		StudId,
		m1,
		m2,
		m3,
		class
	)
SELECT	@StudId,
	@m1,
	@m2,
	@m3,
	CASE
		WHEN @m1 + @m1 + @m3 >= 210 THEN 'Dist'
		WHEN @m1 + @m1 + @m3 >= 180 AND @m1 + @m1 + @m3 < 210 THEN 'First'
		WHEN @m1 + @m1 + @m3 >= 150 AND @m1 + @m1 + @m3 < 180 THEN 'Second'
		WHEN @m1 + @m1 + @m3 < 120 THEN 'Fail'
		ELSE 'Unknown'
	END

N 56°04'39.26"
E 12°55'05.63"

Kristen
Test

22859 Posts

Posted - 2011-01-05 : 05:02:49

Are you guys comfortable with repeating the "@m1 + @m1 + @m3" arithmetic so many times?

Personally I would optimise that out (unless Query Optimiser will do it for me?)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-05 : 05:17:16

quote:
Originally posted by Kristen

Are you guys comfortable with repeating the "@m1 + @m1 + @m3" arithmetic so many times?

Personally I would optimise that out (unless Query Optimiser will do it for me?)

I would do a derived table to avoid using the lengthy expression

Madhivanan

Failing to plan is Planning to fail

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-05 : 05:33:20

It's only inserting one row - unless this is being executed many times then it shouldn't matter.
And if tat's the case you would also want to change the order of the case statement so that the most used come first but then might lose because you will need to check both bounds.
If performance was that much of an issue you probably wouldn't do it this way anyway so I wouldn't worry about it.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2011-01-05 : 06:16:33

Yes, good points Nigel. I think worth raising though (if this is a homework / education exercise)

MageshkumarM
Yak Posting Veteran

61 Posts

Posted - 2011-01-05 : 08:01:44

quote:
Originally posted by nigelrivett

Let me guess

insert into Marks (StudId,m1,m2,m3,class)
select @StudId,@m1,@m2,@m3,
case
when @m1+@m2+@m3 >= 210 then 'Dist'
when @m1+@m2+@m3 >= 180 then 'First'
when @m1+@m2+@m3 >= 150 then 'Second'
when @m1+@m2+@m3 >= 120 then 'Pass'
else 'Fail'
end

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Hi nigel,
Your code short and good...!

MAG,
Start with the new Idea..:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources