Skip to main content
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?
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.
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.
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.
@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
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
@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
@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.
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.
Terms & ConditionsCookie settings