Mysql stored procedure optional parameter

How to create stored procedure that accepts optional parameter in SQL Server?

To create optional parameter in stored procedure, we set the parameter value to NULL while creating a stored procedure.

CREATE PROCEDURE [dbo].[GetAllPersonalDetails]
       @personalDetailsId int = null
AS
BEGIN
       -- SET NOCOUNT ON added to prevent extra result sets from
       -- interfering with SELECT statements.
       SET NOCOUNT ON;

    -- Insert statements for procedure here
       IF @personalDetailsId is null
              BEGIN
                     SELECT * FROM PersonalDetails
              END
       ELSE
              BEGIN
                     SELECT * FROM PersonalDetails
                           WHERE PersonalDetailsId = @personalDetailsId
              END
END

The above stored procedure may or may not be passed @personalDetailsId parameter value and it can be called in any of the following way

-- Without parameter
EXEC GetAllPersonalDetails
-- With parameter
EXEC GetAllPersonalDetails 1

Related Posts

I want to modify a SP in MySQL, but don't want it to crash apps currently using that SP.

The SP is: SP_CreateUserReport
Argument 1: Check4ItCode
Argument 2: PasscodeAdmin

Currently, the calling app sends something like:
Call SP_CreateUserReport('CfiCode','12324')

But I want to add a new argument to the SP:

Argument 3: DemoUserEmail

When I do that, and do the same call, the SP will not receive the 3rd new argument and fail.

Eventually, I will update the all the calls from various apps to add the 3rd argument value, But in the meantime, I want to make that 3rd argument optional till I can update all the calling apps. And, if that value is not there, store a NULL in the table for that field.

Here is a sample of the final code I wish to use.

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_CreateUserReport`(
Check4ItCode varchar(255) ,
PasscodeAdmin varchar(255),
DemoUserEmail varchar(255) 
)

BEGIN
INSERT INTO tbl_000_010_MAIN_REPORT_INFO ( 
strCFICode, 
strPasscodeAdmin, 
strDemoUserEmail)

SELECT 
Check4ItCode as CFICode, 
PasscodeAdmin AS AdminCode, 
DemoUserEmail as TheDemoUserEmail

SELECT LAST_INSERT_ID() as NewReportID;
END

Open in new window

As a workaround approach: if you can modify the caller code to supply a document instead, you can both

1. detect if a key/column exists in the supplied document to omit updates where a key is not present and
2. allow for set-null commands to valid keys

Consider the following (slightly bulky) POC (full db<>fiddle)

create proc Test 
    @payload nvarchar(255),
    @echo bit = 1,
    @execute bit = 0
as
begin
    declare 
        @sql nvarchar(1000) = N'update dbo.foo set '+nchar(10),
        @params_schema nvarchar(1000) = N'';

    declare 
        @a int,
        @b varchar(10),
        @c varchar(10);

    drop table if exists #col_cache;
    select 
        c.column_id,
        c.[name],
        p.[key],
        p.[value],
        p.[type] as j_type,
        t.[name] as data_type,
        t.max_length,
        t.precision,
        t.scale,
        case
            when t.[name] in (
                                N'time',
                                N'datetime2',
                                N'datetimeoffset',
                                N'smalldatetime',
                                N'datetime',
                                N'varbinary',
                                N'varchar',
                                N'binary',
                                N'char',
                                N'nvarchar',
                                N'nchar'
                             )
                then N'('+replace(convert(nvarchar(10),c.max_length),N'-1',N'MAX')+ N')'
            when t.[name] in (N'decimal',N'numeric')
                then N'('+convert(nvarchar(10),c.precision)+N','+convert(nvarchar(10),c.scale)+N')'
            else
                N''
        end as type_suffix
    into #col_cache
    from sys.columns c
    outer apply (
        select 
            j.[key],
            j.[value],
            j.[type]
        from openjson(@payload, '$') as j
        where j.[key] = c.[name] collate database_default
    ) as p
    left join sys.types as t on t.user_type_id = c.user_type_id
    where c.[object_id] = object_id(N'dbo.foo') 
      and c.is_identity = 0;

    with kvp as (
        select 
            [key],
            [value]
        from #col_cache
    )
    select 
        @a = a,
        @b = b,
        @c = c
    from kvp
    pivot (
        max([value])
        for [key] in (a,b,c)
    ) p;
    
    select 
        @sql += string_agg(
            N'   '
            + quotename(cc.[name]) 
            + N' = '
            + iif(
                cc.[key] is null, 
                quotename(cc.[name]),
                N'@_' + cc.[name]
              ), 
            nchar(44) + nchar(10)
        ),
        @params_schema += string_agg(
            N'@_' 
            + cc.[name]
            + N' '
            + cc.[data_type]
            + cc.type_suffix,
            nchar(44)
        )
    from #col_cache as cc;
    
    select @sql += nchar(10) + N'where a = @_a;';

    if @echo = 1
    begin
        select 
            @sql as [sql],
            @params_schema as params_schema;
    end
    if @execute = 1
    begin
        exec sp_executesql 
            @sql,
            @params_schema,
            @a,@b,@c;
    end
end

Note the above pattern is limited to a single table pattern of the form...

create table foo (
    a int not null primary key,
    b varchar(10),
    c varchar(10)
);

...and requires modification for each table definition against which it might be applied (unless you fancy nesting dynamic SQL (which I don't)). The approach is similar to the catch-all where clause solution described in greater detail by Erland Sommarskog and Aaron Bertrand.

By way of local example. If our table dbo.foo had the following, data...

...an execution of the following commands...

exec Test N'{"a":1,"b":"foo","c":null}', @execute = 1;
exec Test N'{"a":2,"b":"bar"}', @execute = 1;

...would result in the following data...

Can a stored procedure have optional parameters?

What is the use of optional parameter?

How do you make a stored procedure parameter optional?

Can a stored procedure have no parameters?