Optional parameters in SQL stored procedure

Question

I am trying to create a stored procedure that has optional parameters. I followed the instructions listed here. I also referenced this SO question. However I keep receiving the following error:

Error converting data type varchar to int.

It works when I execute it as

EXEC sp_get_user {id#}

or

EXEC sp_get_user NULL, {username}

but fails with

EXEC sp_get_user {username}

Stored Procedure

@id int = NULL,
@username nvarchar(50) = NULL

SELECT
    username = COALESCE(a.Username, b.Username),
    password = COALESCE(a.Password, b.Password),
    signup_date = COALESCE(a.SignedUpOn, b.Signup_Date)
FROM table1 a
FULL OUTER JOIN table 2 b
ON a.ID = b.ID
WHERE ((a.ID = @id OR @id IS NULL)
AND (a.Username = @username OR @username IS NULL)
OR (b.ID = @id OR @id IS NULL)
AND (b.Username = @username OR @username IS NULL))

I have tried adding the OPTION(RECOMPILE) and had no success. I want to make this dynamic so other developers can call this SP without having to specify all parameters each time. They will be connecting via LINQ if that makes a difference.

Answer 1

use named parameters in that case

EXEC sp_get_user @username = {username}

if both parameters are optional, SQL server will go by position, so the first one you are passing in will map to the first one in the proc

Answer 2

When executing stored procedures you have to conform for the parameters order as defined respectively, that is why the first and second statements works fine, in the first one EXEC sp_get_user {id#} you passed the id and ignored the user name, then it takes the defined default value. Moreover, in your second statement EXEC sp_get_user NULL, {username} you specified NULL for the id and you passed a value for the username parameter that is why it also works.

On the other hand, the third one EXEC sp_get_user {username} doesn't work because SQL Server treated your parameter {username} as the id value that is why it tries to convert it to integer and of course it will fail. Instead, you have to specify the paramer name while you are passing its value, see the following code:

EXEC sp_get_user @username = {username}