optional parameters in SQL Server stored proc?

sql-server-2008 stored-procedures optional-parameters
larryq picture larryq · Nov 27, 2009 · Viewed 236.8k times · Source

I'm writing some stored procs in SQL Server 2008, and wondered if the concept of optional input parameters is possible here?

I suppose I could always pass in NULL for parameters I don't want to use, check the value in the stored proc, then take things from there, but I was interested if the concept is available here. Thanks!

Answer

Raj More picture Raj More · Nov 27, 2009

You can declare like this

CREATE PROCEDURE MyProcName
    @Parameter1 INT = 1,
    @Parameter2 VARCHAR (100) = 'StringValue',
    @Parameter3 VARCHAR (100) = NULL
AS

/* check for the NULL / default value (indicating nothing was passed */
if (@Parameter3 IS NULL)
BEGIN
    /* whatever code you desire for a missing parameter*/
    INSERT INTO ........
END

/* and use it in the query as so*/
SELECT *
FROM Table
WHERE Column = @Parameter

Related questions

SQL Server stored procedure parameters

I am developing a framework, where in I am a calling stored procedure with dynamically created parameters. I am building parameter collection at the runtime. The problem occurs when I am passing a parameter to stored procedure, but stored proc …

Read More
How do I find a stored procedure containing <text>?

I need to search a SQL server 2008 for stored procedures containing where maybe the name of a database field or variable name.

Read More
SQL Server Insert if not exists

I want to insert data into my table, but insert only data that doesn't already exist in my database. Here is my code: ALTER PROCEDURE [dbo].[EmailsRecebidosInsert] (@_DE nvarchar(50), @_ASSUNTO nvarchar(50), @_DATA nvarchar(30) ) AS BEGIN INSERT INTO EmailsRecebidos (De, Assunto, …

Read More