CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Black Dagger
Black Dagger

Reputation: 399

Restricted user input values in Stored Procedures SQL

create procedure db.Test
@input1 varchar(max), @input2 {Either 'yes' or 'no'}

I want the input2 from users to be restricted to either 'yes' or 'no'. Can anyone provide me with the syntax.

I found the synax for default value

@input2 varchar(max) default 'yes'

But couldnt find anything for restricted input.

Upvotes: 0

Views: 211

Answers (3)

Mudassir Hasan
Mudassir Hasan

Reputation: 28771

You can create your own custom error messages for RAISERROR.

exec sp_addmessage @msgnum=50010,@severity=1,@msgtext='Invalid input parameters'

After this , errorid 50010 will point to message Invalid input parameters.

create procedure db.Test @input1 varchar(max), @input2 varchar(3)
as
begin
    if @input2 not in ('yes', 'no')
    begin
      RAISERROR ( 50010 ,1,1)
    end

(...)

end
go

Upvotes: 1

XcisioN
XcisioN

Reputation: 107

You can always use a case for this issue, I wrote this example for you so that you can explore a bit with it.

set serveroutput on set verify off

declare
Ship_Value Number(1) := &ShipNumber;
ship_method_type varchar(20);
ship_current_date varchar(20);

begin
select to_char(sysdate,'DD-Mon-YYYY HH24:MI:SS')
into ship_current_date
from dual;

case Ship_Value when 1 then
select ship_method_desc
into ship_method_type
from ship_method
where ship_method_id_no = 1;  
dbms_output.put_line('Date: ' || ship_current_date);
dbms_output.put_line('Shipping method 1 is:' || ship_method_type);

when 2 then
select ship_method_desc
into ship_method_type
from ship_method
where ship_method_id_no = 2;
dbms_output.put_line('Date: ' || ship_current_date);
dbms_output.put_line('Shipping method 2 is:' || ship_method_type);

when 3 then 
select ship_method_desc
into ship_method_type
from ship_method
where ship_method_id_no = 3;
dbms_output.put_line('Date: ' || ship_current_date);
dbms_output.put_line('Shipping method 3 is:' || ship_method_type);

else
dbms_output.put_line('Date: ' || ship_current_date);
dbms_output.put_line('only 1-3 exists');
end case;
end;

You can use similar code for the yes or no input. Hope this helps :)

Upvotes: 0

saamorim
saamorim

Reputation: 3905

You need to make a check with and if statement. There isn't a "restriction" validator for what you need.

create procedure db.Test @input1 varchar(max), @input2 varchar(3)
as
begin
    if @input2 not in ('yes', 'no')
    begin
       --raiserror or similar
       return
    end

(...)

end
go

Upvotes: 4

Related Questions