CODEWITHSUNDEEP
sql-serverstored-procedures
C.J.
C.J.

Reputation: 3527

Like ISNULL % doesn't work

I have a SP with a simple query:

@first_name nvarchar(50) = null
AS
BEGIN
Select * from empTable where first_name like %ISNULL(@first_name, first_name)%
END

What I want to do is, if the user enter a value to @first_name, the query should act like Select * from empTable where first_name like '%John%'

And if the use doesn't enter name, I should be able to return everything - as if there is no where condition.

It keeps telling me there is a syntax error in my SP

Upvotes: 0

Views: 47

Answers (1)

Dave.Gugg
Dave.Gugg

Reputation: 6771

Try:

@first_name nvarchar(50) = null
AS
BEGIN
Select * from empTable where first_name like '%' + ISNULL(@first_name, first_name) + '%'
END

Upvotes: 4

Related Questions