SQL Server stored procedure default value behavior

Question

I have a stored procedure in SQL Server. I want to set default value when null is passed; otherwise, use the passed value. However, I am not sure if my code reflects the behavior I want. If not, what should I do?

CREATE PROCEDURE [dbo].[test]
    (@PassedTableName nvarchar(100)) 
AS
BEGIN
    DECLARE @TableName AS NVarchar(255) = 'defaultTablenName'

    SELECT @TableName = QUOTENAME(TABLE_NAME)
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = @PassedTableName 

    DECLARE @sql0 AS NVARCHAR(MAX)
    SELECT @sql0 = 'select count(*) from ' + @TableName 
    EXEC(@SQL0)

select @TableName = isnull(QUOTENAME( TABLE_NAME ), 'defaultTablenName') — Yogesh Sharma
– Yogesh Sharma, Commented Oct 18, 2017 at 14:04
Are you trying to set optional parameters or just send a NULL to the parameter? — NicoRiff
– NicoRiff, Commented Oct 18, 2017 at 14:04
@YogeshSharma, that's my first idea too, but will it work if the select returns no row at all? — jarlh
– jarlh, Commented Oct 18, 2017 at 14:08
Just change @end WHERE TABLE_NAME = ISNULL(@PassedTableName, TableName) — Yogesh Sharma
– Yogesh Sharma, Commented Oct 18, 2017 at 14:15

BradC · Accepted Answer · 2017-10-18 14:24:43Z

3

Just specify the default value when the parameter is declared:

CREATE PROCEDURE [dbo].[test]
    (@PassedTableName nvarchar(100) = 'defaultTableName')

If the user passes in a parameter, it will use it. If they exclude the parameter, it will default to defaultTableName.

One warning, though. If the user includes the parameter but explicitly specifies NULL (by calling dbo.test @PassedTableName = NULL), the value will be NULL. In that case, I'd recommend:

CREATE PROCEDURE [dbo].[test]
    (@PassedTableName nvarchar(100) = NULL) 

IF @PassedTableName IS NULL 
SET @PassedTableName = 'defaultTableName'

edited Oct 18, 2017 at 14:24

answered Oct 18, 2017 at 14:08

BradC

40.3k14 gold badges77 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

GuidoG Over a year ago

should the declaration not use 'defaultTableName' in stead of null ?

BradC Over a year ago

@GuidoG sounds right, the original example was a little vague.

Magisch · Accepted Answer · 2017-10-18 14:13:42Z

2

You can do this by using COALESCE or by specifiying the default value when the parameter is declared.

CREATE PROCEDURE [dbo].[test]
    (@PassedTableName nvarchar(100) = 'defaultTableName')

or

DO STUFF WHERE tablename = COALESCE(@PassedTableName,'defaultTableName')

Either will work. There's another way by using IF

IF @PassedTableName IS NULL
  SET @PassedTableName = 'defaultTableName';

answered Oct 18, 2017 at 14:13

Magisch

7,3529 gold badges40 silver badges53 bronze badges

Collectives™ on Stack Overflow

SQL Server stored procedure default value behavior

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related