Reputation: 956
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
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)
Upvotes: 3