CODEWITHSUNDEEP
sqlsql-servert-sql
Alma
Alma

Reputation: 4420

Have a optional parameter in stored procedure

I have a stored procedure that has a State parameter as optional, so if I pass state it should query table using both parameter, if I am not passing the state parameter I only need to query table with the template parameter.

How I can do that?

This is my stored procedure:

CREATE PROCEDURE [dbo].[Get_TempName]
     @TemType varchar(50),
     @State varchar(50) = 'N/A'
AS
BEGIN
     SET NOCOUNT ON;

     SELECT 
         [TempType],
         [TempName],
         [State]
     FROM 
         [Template] WITH (NOLOCK) 
     WHERE
         TempType = @TempType 
         AND [State] = @State
END

Upvotes: 1

Views: 93

Answers (3)

Internet Engineer
Internet Engineer

Reputation: 2534

Create[dbo].[Get_TempName]
  @TemType    varchar(50)
 ,@State    varchar(50) = NULL

AS
BEGIN
 SET NOCOUNT ON;

 SELECT 
   [TempType]
  ,[TempName]
  ,[State]

 FROM [Template]  WITH (NOLOCK) 
  where TempType = @TempType 
 and (([State] IS NULL) OR ([State] = @State))

 END

Upvotes: 2

Gareth Suarez
Gareth Suarez

Reputation: 606

you could do everything in one select statement, but this can have adverse performance implications. It is usually better to separate it out into separate selects based on the parameters. 

Create[dbo].[Get_TempName]
@TemType    varchar(50)
,@State    varchar(50) 

AS
BEGIN
SET NOCOUNT ON;

IF(@State IsNull)
    SELECT 
    [TempType]
    ,[TempName]
    ,[State]
    FROM [Template]  WITH (NOLOCK) 
    where TempType = @TempType 
 ELSE
    SELECT 
    [TempType]
    ,[TempName]
    ,[State]
    FROM [Template]  WITH (NOLOCK) 
    where TempType = @TempType 
    and [State] = @State
END

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93754

You can use OR condition with Is Null check. Also pass NULL in @state parameter when you don't want to use it in filter

Create[dbo].[Get_TempName]
  @TemType    varchar(50)
 ,@State    varchar(50) 

AS
BEGIN
 SET NOCOUNT ON;

 SELECT 
   [TempType]
  ,[TempName]
  ,[State]

 FROM [Template]  WITH (NOLOCK) 
  where TempType = @TempType 
 and ([State] = @State or @state is null)

 END

Upvotes: 1

Related Questions