Solved

Control procedure parameter - default value

  • 18 June 2019
  • 8 replies
  • 234 views

Userlevel 4
Badge +13
First I created a control procedure with a parameter. Then I filled in a default value for the parameter in the task parameter tab. However the produced SQL code that actually creates the procedure does not set this default value on the database. Is this the correct behavior?

The generated code in the software factory includes this part:

code:
create procedure task_procedure_name
(
@from_date date
)
as
begin

However I would expect something like this:

code:
create procedure task_procedure_name
(
@from_date date = '1970-01-01'
)
as
begin

My goal is to create a procedure which includes a parameter with a default value which I can override if necessary when calling the procedure using the execute command. Can this be accomplished?
icon

Best answer by Vincent Doppenberg 18 June 2019, 10:35

View original

This topic has been closed for comments

8 replies

Userlevel 7
Badge +11
@Pim Actually, T-SQL does support optional parameters by supplying a default value for a parameter. For example:
code:
create or alter procedure optional
(
@a int,
@b int = 1,
@c int = null
)
as
begin
select @a, @b, @c
end
go

exec optional 1
go

--Result: 1, 1, NULL


When the optional parameters are followed by parameters without a default value, you have to explicitly specify the parameter names when executing the stored procedure:
code:
create or alter procedure optional_named
(
@a int,
@b int = 1,
@c int
)
as
begin
select @a, @b, @c
end
go

exec optional_named @a = 1, @c = 2
go
Userlevel 6
Badge +4
You are correct that the default value is not generated into the creation script of the stored procedure, even though we do support this feature for tables. You can submit an idea here for this feature to be implemented for procedures as well.

With that said, the GUI will enter the default value for the parameter when starting the task and provide you with a way to override it if necessary. If this is not sufficient due to the procedure being called outside of the GUI as well, then I suggest awaiting the response to the idea or going for a temporary, suboptimal solution such as setting the parameter to a hardcoded default value in the body of the procedure when it is null.
Userlevel 3
Badge +3
@Jasper It seems you have got me beat there. I was completely sure it worked that way.
But then again, if I hadn't made that mistake you wouldn't have taught me:
  1. The truth about defaults on stored_procedure parameters
  2. That it is possible to 'create or alter' a procedure
Still my point about function overloading stands, although now the example isn't very good any more.
Userlevel 4
Badge +13
Alright I will post an idea. Could you elaborate a bit on that method with a hard coded default? I've tried using isnull for the parameter but the execute command still complains in SSMS that I don't provide a value for it.
Userlevel 6
Badge +4
I'm afraid that there is no way to fix that problem without marking the parameter as optional (which is what you are requesting in your idea). You will have to explicitly call the procedure with NULL as the value for the parameter in question.
Userlevel 5
Badge +5
@Roland, Can't you just create your procedure including the parameter and within the procedure have something like:
code:
if @from_date is null
begin
set @from_date = '1970-01-01'
end


When calling the procedure, you can just call it with an empty value, like so:
code:
declare @date date
exec task_procedure_name @date
Userlevel 3
Badge +3
Unfortunately T-SQL is not like (for example) C#, which allows to not supply any value for parameters that have a default. in T-SQL you will always have to supply a value to all parameters, which means you would have to call your procedure like:
code:
exec task_procedure_name null

There is one other option though, which in other languages would be called 'function overloading', this is basically a copy of the procedure which calls the main procedure with the necessary parameters. While this is technically not possible in T-SQL you can imitate it like so:
code:
create procedure task_procedure_name_default
as
begin
exec task_procedure_name '19700101'
end

Now if you want to supply nothing to the procedure you can call 'task_procedure_name_default', if you do have a value you can call 'task_procedure_name'.

Which I would model in the SF by creating a subroutine (depending on your actual needs), which would actually allow you to name it 'procedure_name' (which is a bit neater). Now it can be discussed whether or not this is 'low code' but it certainly allows you to create some convenience functions for yourself
Userlevel 4
Badge +13
I indeed solved it by always calling the procedure with the parameter and a value. I thought about making a separate default procedure also that on its turn calls the original procedure with a default value but we don't actually need it. It would still be neat if setting a parameter default value would end up in the sql code as to make calling with a parameter from outside the application truly optional but the current situation is sufficient for our application.

@Pim The fact that T-SQL supports default values for parameters was the reason for this topic. 🙂 It struck me as inconsistent to have default values for table columns written to the database and at the same time not with default values for procedure parameters. There simply is no difference in generated SQL code between having default parameter values or not despite the fact that T-SQL is perfectly capable. That seems a bit of a miss but perhaps there are reasons for the current situation that I am not aware of. I am still learning every day myself.