How to pass a value or no value inside a T-SQL or stored Procedure

How to pass a value  or no value inside a T-SQL or stored Procedure

Let's say we have a table called state which hold three column (StateId, StateCode and StateName). If you don't have this table in your test database, go ahead and make one.

CREATE TABLE [dbo].[State]
(
StateCodeID int IDENTITY(1,1),
StateCode varchar(2) NOT NULL,
[State] varchar(25) NOT NULL
);

Let's insert some data in this table. You can copy these script and insert into your table.

Insert INTO State VALUES
('AK', 'Alaska')
,('AL', 'Alabama')
,('AR', 'Arkansas')
,('AZ', 'Arizona')
,('CA', 'California')
,('CO', 'Colorado')
,('CT', 'Connecticut')
,('DC', 'Dist. of Columbia')
,('DE', 'Delaware')
,('FL', 'Florida')
,('GA', 'Georgia')
,('HI', 'Hawaii')
,('IA', 'Iowa')
,('ID', 'Idaho')
,('IL', 'Illinois')
,('IN', 'Indiana')
,('KS', 'Kansas')
,('KY', 'Kentucky')
,('LA', 'Louisiana')
,('MA', 'Massachusetts')
,('MD', 'Maryland')
,('ME', 'Maine')
,('MI', 'Michigan')
,('MN', 'Minnesota')
,('MO', 'Missouri')
,('MS', 'Mississippi')
,('MT', 'Montana')
,('NC', 'North Carolina')
,('ND', 'North Dakota')
,('NE', 'Nebraska')
,('NH', 'New Hampshire')
,('NJ', 'New Jersey')
,('NM', 'New Mexico')
,('NV', 'Nevada')
,('NY', 'New York')
,('OH', 'Ohio')
,('OK', 'Oklahoma')
,('OR', 'Oregon')
,('PA', 'Pennsylvania')
,('RI', 'Rhode Island')
,('SC', 'South Carolina')
,('SD', 'South Dakota')
,('TN', 'Tennessee')
,('TX', 'Texas')
,('UT', 'Utah')
,('VA', 'Virginia')
,('VT', 'Vermont')
,('WA', 'Washington')
,('WI', 'Wisconsin')
,('WV', 'West Virginia')
,('WY', 'Wyoming');

So this script should enter 51 rows in our table.


Coming to the point, lets say someone ask you write a stored procedure where he need stateCodeID and State value if user enter statecode, if not he want to see all the values.

Let's go ahead write this script

CREATE PROCEDURE [dbo].[usp_GetStateInformation] 
(
@Statecode VARCHAR(2) = NULL
)
AS
Begin
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

SELECT STATECODE
,[STATE]
FROM [STATE]
WHERE STATECODE = ISNULL(@Statecode, StateCode)
ORDER BY StateCode
END;


"ISNULL(@Statecode, StateCode)" this is important function

What ISNULL do is that if there is no value in our variable @StateCode, it will pass null value in select query and it will return all the rows.

Now try executing this stored procedure as

Exec usp_GetStateInformation 'TX'

In this you will get statecode and state value only for 'TX'

And like this

Exec usp_GetStateInformation

In this you will get all the statecode and state value for all rows (51)