Case Problem!

Hi all,
I am trying to create the following stored procedure..
I am still not all that familiar with the Case statement so any help would
be appreciated...!!!
I am getting a syntax errors...
Is there a better way to do this?
CREATE PROCEDURE [dbo].[asmt_v1_ins_card_induction]
@.area VARCHAR(120)
AS
BEGIN
Declare @.last_induction INT
SET @.last_induction = (SELECT last_induction FROM asmt_v1_cards )
CASE
WHEN @.last_induction = NULL THEN INSERT INTO asmt_v1_cards (asmt_1)
VALUES(@.area)
WHEN @.last_induction = 1 THEN INSERT INTO asmt_v1_cards (asmt_2)
VALUES(@.area)
WHEN @.last_induction = 2 THEN INSERT INTO asmt_v1_cards (asmt_3)
VALUES(@.area)
WHEN @.last_induction = 3 THEN INSERT INTO asmt_v1_cards (asmt_4)
VALUES(@.area)
END
Cheers,
AdamCASE is an expression, not a statement; it is not used as a
control-of-flow element. To do what you're attempting to do, you'll
need a series of IF.. ELSE statements.
Of course, just from a cursory glance at your code, it appears that
your data model needs work; what do asmt_1, asmt_2... represent? Are
they different attributes of your entity, or are they simply holders
for value?
Stu|||The data model sucks i know,...
But the request from the powers that be, is that is must be that way..
Thanks for the info..
"Mr Ideas Man" <adam@.pertrain.com.au> wrote in message
news:uoQRTyPOGHA.964@.tk2msftngp13.phx.gbl...
> Hi all,
> I am trying to create the following stored procedure..
> I am still not all that familiar with the Case statement so any help would
> be appreciated...!!!
> I am getting a syntax errors...
> Is there a better way to do this?
> CREATE PROCEDURE [dbo].[asmt_v1_ins_card_induction]
> @.area VARCHAR(120)
> AS
> BEGIN
> Declare @.last_induction INT
> SET @.last_induction = (SELECT last_induction FROM asmt_v1_cards )
> CASE
> WHEN @.last_induction = NULL THEN INSERT INTO asmt_v1_cards (asmt_1)
> VALUES(@.area)
> WHEN @.last_induction = 1 THEN INSERT INTO asmt_v1_cards (asmt_2)
> VALUES(@.area)
> WHEN @.last_induction = 2 THEN INSERT INTO asmt_v1_cards (asmt_3)
> VALUES(@.area)
> WHEN @.last_induction = 3 THEN INSERT INTO asmt_v1_cards (asmt_4)
> VALUES(@.area)
> END
> Cheers,
> Adam
>|||without nagging you about the schema and all...here is the insert without
if/else.
insert asmt_v1_cards(asmt_1,asmt_2,asmt_3,asmt_
4)
select case when @.last_induction is null then @.area end,
case when @.last_induction=1 then @.area end,
case when @.last_induction=2 then @.area end,
case when @.last_induction=3 then @.area end
-oj
"Mr Ideas Man" <adam@.pertrain.com.au> wrote in message
news:uoQRTyPOGHA.964@.tk2msftngp13.phx.gbl...
> Hi all,
> I am trying to create the following stored procedure..
> I am still not all that familiar with the Case statement so any help would
> be appreciated...!!!
> I am getting a syntax errors...
> Is there a better way to do this?
> CREATE PROCEDURE [dbo].[asmt_v1_ins_card_induction]
> @.area VARCHAR(120)
> AS
> BEGIN
> Declare @.last_induction INT
> SET @.last_induction = (SELECT last_induction FROM asmt_v1_cards )
> CASE
> WHEN @.last_induction = NULL THEN INSERT INTO asmt_v1_cards (asmt_1)
> VALUES(@.area)
> WHEN @.last_induction = 1 THEN INSERT INTO asmt_v1_cards (asmt_2)
> VALUES(@.area)
> WHEN @.last_induction = 2 THEN INSERT INTO asmt_v1_cards (asmt_3)
> VALUES(@.area)
> WHEN @.last_induction = 3 THEN INSERT INTO asmt_v1_cards (asmt_4)
> VALUES(@.area)
> END
> Cheers,
> Adam
>