CODEWITHSUNDEEP
sqlsql-serverselectstored-procedures
tomwesolowski
tomwesolowski

Reputation: 956

Stored Procedure with SELECT and NULLS

I have following stored procedure:

ALTER PROCEDURE [dbo].[SearchProfile]

(
@Type varchar,
@Name varchar
)

AS
SELECT * 
FROM dbo.Profiles 
WHERE ((@Type IS NULL) 
 OR (dbo.Profiles.Type = @Type)) 
 AND  ((@Name IS NULL) 
 OR (Name = @Name));

When I execute this with:

exec SearchProfile NULL, NULL

It returns all rows which is OK, but when I change one of NULLs to some value...

exec SearchProfile 'abc', NULL

It doesn't return anything.

What am I doing wrong? Thanks in advance.

Upvotes: 0

Views: 65

Answers (1)

Adriaan Stander
Adriaan Stander

Reputation: 166606

You need to specify the length of the variables.

If you run

DECLARE @Type varchar = 'abc'

SELECT @Type

It returns 

a

So change it to something like

@Type varchar(50),
@Name varchar(50)

SQL Fiddle DEMO

Upvotes: 3

Related Questions