SQL Server stored procedure with parameters

Get Help
1

this is my first participation in this forum.
I have discovered Metabase recently and I think it is very interesting.

I would create a new question to execute this command (sp01 is a stored procedure):

exec dbo.sp01
@DATE1_FROM = NULL,
@DATE1_TO = NULL,
@DATE2_FROM = {{Date2_From}},
@DATE2_TO = {{Date2_To}},
@FLAG_1 = 1,
@FLAG_2 = 0

if I enter both values for the parameters, the command is executed correctly.

But I need the parameters to be optional, or to be able to pass the value NULL.

I have not been able to do that so far.

can someone help me?

thanks.

2

Hi @wjz
Please always post "Diagnostic Info" from Admin > Troubleshooting, when starting a new topic.
You would need to use Optional Clauses or Complex Default Values:
https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html#setting-a-default-value
So either:
[[ @DATE2_FROM = {{Date2_From}}, ]]
Or:
@DATE2_FROM = [[ {{Date2_From}}, --]] null,

1 Like
3

Hi there! I'm also trying to do something similar like the above one. I've a stored procedure in which all the parameters should be made optional. When I put the optional clauses and give value for any one of the parameters, it does not get filtered, it gives the whole set of records. How do I solve this ?

DECLARE @depid INT
select @depid = (select top 1 departmentid from departments where {{departmentid}})

EXEC Demo_GetEmployees [[@departmentid = @depid , @startdate = {{startdate}}, @enddate = {{enddate}}]]

DECLARE @depid INT
SELECT  @depid = (SELECT TOP  1 DepartmentID  from Departments  WHERE "dbo"."Departments"."DepartmentID" IN (1))

EXEC Demo_GetEmployees
4

@sgayathri
Please read this carefully: https://www.metabase.com/docs/latest/questions/native-editor/sql-parameters#optional-clauses
The optional clauses are for everything within the clause, so if you have multiple variables, then all needs to be defined before the optional clause takes effect.
Try something like this:

EXEC Demo_GetEmployees
@departmentid = [[(select top 1 departmentid from departments where {{departmentid}}) --]] null
, @startdate = [[{{startdate}} --]] null
, @enddate = [[{{enddate}} --]] null
5

Thanks! It worked this way.