ISNULL with LIKE? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

ISNULL with LIKE?

Author

Topic

fralo
Posting Yak Master

161 Posts

Posted - 2008-04-30 : 09:22:12

I create a stored procedure like such:

"create procedure tmptable_query (@lname varchar(20)) as select * from #temp_table where lname like ISNULL(@lname+'%',lname)"

Unfortunately, it only returns exact matches, as if it were written as "where lname = ISNULL(@lname, lname)"

However, if I query from a static table the code works correctly.Any ideas on what could be wrong?

Thank you.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-30 : 09:29:51

Works for me!

Are you sure there is matching data populated in #temp_table?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-30 : 09:31:15

[code]-- Prepare sample data
DECLARE @Sample TABLE (Data VARCHAR(20))

INSERT @Sample
SELECT 'Peso' UNION ALL
SELECT 'Yak' UNION ALL
SELECT 'SQLTeam'

-- Prepare search
DECLARE @Search VARCHAR(20)

-- Search when NULL
SELECT *
FROM @Sample
WHERE Data LIKE ISNULL(@Search + '%', Data)

-- Search when data exists
SET @Search = 'SQL'

SELECT *
FROM @Sample
WHERE Data LIKE ISNULL(@Search + '%', Data)

-- Simpler
SELECT *
FROM @Sample
WHERE Data LIKE ISNULL(@Search, '') + '%'[/code]

E 12°55'05.25"
N 56°04'39.16"

fralo
Posting Yak Master

161 Posts

Posted - 2008-04-30 : 09:45:08

Yes, there is definitely data in my temp table. If I pass in a parameter like 'Smith', it will work correctly. One thing I should mention, however, is that I am running this from a .NET application. There may be factors specific to that which are causing these problems. But if I run this straight from within sql server, it works fine. I probably should have pointed that out to begin with.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources