CODEWITHSUNDEEP
sqlsql-serverstored-procedures
user2825468
user2825468

Reputation: 63

A simple Parameterized Stored Procedure

I need a very simple stored procedure, suppose we have some data of employees in a table. One of column contain NickName of employees. NickName column is a nullable, for example we have 10 records and 7 employees have nick name whereas other 3 are null. I want to pass a parameter for NickName; if I pass value in parameter, data should be returned according to parameter value and if don’t pass any value in parameter then all 10 records should be displayed means both NickName and null value records should be displayed.

The following stored procedure is functioning well but it doesn't return the rows with NULL values:

    @name VARCHAR(30) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    SELECT * 
    FROM Emp 
    WHERE NickName = ISNULL(@name, NickName)
END

I will be very thankful if some answered, please.

Upvotes: 1

Views: 48

Answers (2)

DhruvJoshi
DhruvJoshi

Reputation: 17126

you can try something like following

@name varchar(30)= NULL
AS
BEGIN

SET NOCOUNT ON;
SELECT * from Emp where ISNULL(NickName,'') = COALESCE(@name,Nickname, '')
end

Upvotes: 2

Missak Boyajian
Missak Boyajian

Reputation: 2245

@name varchar(30)= NULL
AS
BEGIN

SET NOCOUNT ON;
SELECT * from Emp where NickName IS NULL OR NickName  = @name
end

Upvotes: 2

Related Questions