1

These were my instructions:

Create a stored procedure that receives two parameters:

@i_action

in which:

  • 'I' = inserts a value
  • 'U' = Updates values
  • 'D' = Delete Values
  • 'C' = Consult

and @_option

where

  • 1 = cursor
  • 2 = specific value

for @i_action it must go through the procedure.

On top of that:

  • If @i_action = 'C' and @i_option = 2 it must return a value indicating "Invalid option"
  • if @i_action = 'C' and @i_option = 1 it must go trough the table with an specific value

I am honestly at a lost as I have never done a stored procedure that is this complex (I have not even made an IF or CASE that is this complex)

this is what I am attempting right now:

GO
CREATE PROCEDURE TWOPTIONS
@i_action  varchar (1),
@i_option int,

AS

IF @i_option = 2

declare @value 

IF @i_action = 'I' Insert into Authors values ()
IF @i_action = 'U' Update Authors Set country = 
IF @i_action = 'C' Select * from Authors 
IF @i_action = 'D' delete from Authors where Name =  

I am mostly struggling with the structure I am sure I can make the instructions work once I figure it out.

1
  • Hey @Bijujose , that is exactly where I am stuck, I feel as if I have to create 7 if options? Im not sure how to make it so if its one value it creates a pointer of it is another I just insert an individual value and how to create all those values within unless I go IF @action = U and @option = 1, then @action = U and @option = 2 and so on? I feel as if I have to use nested if statements but I don't know how to use them in SQL
    – Fabs123
    Jul 8, 2021 at 11:35

1 Answer 1

4

In T-SQL, nested if statements can be achieved using the following pattern:

IF (<condition(s)>)
  BEGIN
    <Statement(s)>
  END
ELSE IF (<condition(s)>)
  BEGIN
    <Statement(s)>
  END
ELSE
  BEGIN
    <Statements(s)>
  END

Official documentation: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/else-if-else-transact-sql

Note: If you are executing a single statement the BEGIN/END is unnecessary.

So if you're trying to do:

if @i_action = 'C' and @i_option = 2 it must return a value indicating "Invalid option" if @i_action = 'C' and @i_option = 1 it must go trough the table with an specific value

Then your code would look like:

IF (@i_action = 'C' AND @i_option = 2)
  RAISERROR('Invalid Option',16,1)
ELSE IF (@i_action = 'C')
  BEGIN
    <Do whatever>
  END

I'm not sure if your instructions were from homework, a job interview, or user requirements, but I'd strongly recommend splitting the stored procedure into four procedures as:

  1. The arguments for each would vary (if I'm returning or deleting an 'Author' I only need the primary key to do so)
  2. The permissions may vary
  3. Each SP can be optimized independently, if needed

So Author_Create, Author_Update, Author_Return, Author_Delete. No need to make things difficult by combining them.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.