"CASE" function on t sql

• smokynightshadefrancesca

  Valued Member

  Points: 54

  More actions

  December 4, 2017 at 9:06 am

  #402724

  Hi ! I'm still here asking for your help
  I need to create a stored procedure that takes for imput an id int, and a @operation char
  i woul lite my stored procedur to CRUD in relation of the char I insert but even if compile it doesn't give me the results i want when execute.
  where is the problem?

  
create procedure SWITCH
    @id int null,
    @operazione char
as
set xact_abort on

  begin try
if @operazione ='d'
begin

          declare @tra1name varchar(10)
        select @tra1name ='delete'
        BEGIN TRAN @tra1name
            delete OffertaLavoro
            where ID_offerta =@id
            print 'Offerta eliminita con successo'
        COMMIT TRAN
    end
        

  if @operazione ='u'
    begin
            declare @titolo varchar(50),
            @rif varchar(50),
            @postiVac int,
            @Dip varchar(50),
            @liv int,
            @paC varchar(50),
            @car varchar(50),
            @cat varchar(50) =NULL,
            @sotcat varchar(50) =Null,
            @ref int
        BEGIN TRAN
            update OffertaLavoro
            set
            titolo=@titolo,
            riferimento=    @rif,
            postiVacanti=    @postiVac,
            Dipartimento=    @Dip,
            fk_livelloIDTipo =    @liv,
            personaleACarico=     @paC,
            caratteristiche=    @car,
            categoria=    @cat,
            sottocategoria=    @sotcat,
            fk_IDAzienda_offertaLavoro=    @ref
            where ID_offerta =@id
            print 'Offerta Modificata con successo'
        commit tran;
    end;    

  if @operazione ='c'
    begin
            declare @titolo1 varchar(50),
            @rif1 varchar(50),
            @postiVac1 int,
            @Dip1 varchar(50),
            @liv1 int,
            @paC1 varchar(50),
            @car1 varchar(50),
            @cat1 varchar(50) =NULL,
            @sotcat1 varchar(50) =Null,
            @ref1 int

          BEGIN TRAN
            insert into OffertaLavoro(
                offertaLavoro.titolo,
                offertaLavoro.riferimento,
                offertaLavoro.postiVacanti,
                offertaLavoro.Dipartimento,
                offertaLavoro.fk_livelloIDTipo,
                offertaLavoro.personaleACarico,
                offertaLavoro.caratteristiche,
                offertaLavoro.categoria,
                offertaLavoro.sottocategoria,
                offertaLavoro.fk_IDAzienda_offertaLavoro)
            values (@titolo1, @rif1,@postiVac1,@Dip1,@liv1,@paC1,@car1 ,@cat1,@sotcat1,@ref1);
            print 'Offerta inserita con successo'
        COMMIT TRAN
    end;    

      end try

  begin catch
    print 'Errore, o comando non riconosciuto';
    throw;
end catch


  P.S. this is the OffertaLavoro tab

  
CREATE TABLE [dbo].[OffertaLavoro](
    [ID_offerta] [int] IDENTITY(1,1) NOT NULL,
    [titolo] [varchar](50) NOT NULL,
    [riferimento] [varchar](50) NOT NULL,
    [postiVacanti] [int] NOT NULL,
    [Dipartimento] [varchar](50) NOT NULL,
    [fk_livelloIDTipo] [int] NOT NULL,
    [personaleACarico] [varchar](50) NOT NULL,
    [caratteristiche] [varchar](3500) NOT NULL,
    [categoria] [varchar](50) NULL,
    [sottocategoria] [varchar](50) NULL,
    [fk_IDAzienda_offertaLavoro] [int] NOT NULL,
CONSTRAINT [PK__OffertaL__4A6DA1C1C7779B46] PRIMARY KEY CLUSTERED  

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  December 4, 2017 at 9:36 am

  #1970556

  You're declaring variables instead of parameters. You'll simply insert null values.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• T.Ashish

  SSCarpal Tunnel

  Points: 4248

  More actions

  December 15, 2017 at 3:19 am

  #1972270

  Whatever you are declaring inside your begin try - end try block, does not have any value assigned before insert statement. This is resulting in NULL insertion, thus you are not seeing anything. Something like this will return something:

   declare @titolo1 varchar(50)    =    '1',
      @rif1 varchar(50)        =    '2',
      @postiVac1 int            =    '3',
      @Dip1 varchar(50)        =    '4',
      @liv1 int                =    '5',
      @paC1 varchar(50)        =    '6',
      @car1 varchar(50)        =    '7',
      @cat1 varchar(50) =NULL,
      @sotcat1 varchar(50) =Null,
      @ref1 int                =    8

     BEGIN TRAN
  select @titolo1, @rif1,@postiVac1,@Dip1,@liv1,@paC1,@car1 ,@cat1,@sotcat1,@ref1

• Ramesh Saive

  SSC-Insane

  Points: 24275

  More actions

  December 15, 2017 at 5:25 am

  #1972279

  Since this is a CRUD procedure, and as Luis said, you should be adding the variables as input parameters for the procedure for CREATE & UPDATE operations.

  --Ramesh
  

  ---