use of coalesce
USE [DB_Name] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_FillMarks] @studentId bigint AS BEGIN SET NOCOUNT ON; SELECT ID, Name, Marks FROM t_Results r where STUDENTID = COALESCE(NULLIF(@studentId, '0'), STUDENTID) and Marks >= 80 END
This will select details of student whose id is passed to the procedure if the id matches with that in the DB and his/her marks are greater or equal to 80.
NULLIF(@studentId, ‘0’) will return the first value that matches with the student id.
COALESCE will ensure to return first non-null value.