Stored Proc variable '%' doesn't seem to work

[Mike555]

This is probably so simple and I'm totally missing it.

I have this simple stored proc below which is not working as it should. It should select records where the MessageID column is LIKE the @MessageID parameter. For some reason the LIKE part is not working. Why is this? Thanks.

CREATE PROCEDURE dbo.spSearchEntries
(
	@MessageID varchar(50)
)
AS
	SET NOCOUNT ON;
SELECT     dbo.tblEntries.*
FROM       dbo.tblEntries
[COLOR=green][b]WHERE (dbo.tblEntries.MessageID LIKE '%@Message%')[/b][/color]
GO

--
Mike

 

[Sooty70]

You are looking for messages like '%@Message%', presumably you want to search for message like the contents of @Message?

Try this:

WHERE (dbo.tblEntries.MessageID LIKE '%' + @Message + '%')

 

[SQLSister]

Try:

SELECT     dbo.tblEntries.*
FROM       dbo.tblEntries
WHERE (dbo.tblEntries.MessageID LIKE '% +@Message+%')

Incidentally, this is very inefficient code. You should never use selct * and using a wildcard as the first character means that SQL Server will not use the indexes. This may be OK if you have a small table but not if you have a large one. If you are going to do this often, you might need to consider using full-text search instead.

Questions about posting. See faq183-874

 

[Sooty70]

Ahhhh, does SQL Server use the + sign as an escape character?

 

[Sooty70]

Incidentally, why should you never use select *? I think that as a rule of thumb it is good practice to list out all the column names and that re-ordering the column list, adding / removing columns is easier but do you have different reasons?

 

[SQLSister]

Select * is less efficent than listing all the columns. IT also leads to people returning more columns than they actually need most of the time which is bad both for the SQL Server and the network.

Questions about posting. See faq183-874

 

[Sooty70]

I agree with your second point, I only select * if I actually need all the columns. I would be interested to learn more about the performance ramifications of using * though. Do you know of any papers?

Mike~ didn't mean to hijack your thread but I think you have an answer now so maybe it's not all bad? ;-)

 

[SQLSister]

Well I can't remember where I read it (and can't find the reference offhand), but I understand that Select * requires the database to take the extra step of looking up the column names. Not a big performance hit obviously. But over a wqole database and lots of accesses, the little things do add up.

The more important point is not to use it when you don't need all the columns which means it is never appropriate in an inner join query because two columns will have the exact same data and that is returning more than you want. And truly you often do not need all the columns. People write queries with all the columns to reuse code, but code reuse is a bad idea if it makes the database less efficient.

But in the course of looking for the refernce to why to not use Select *, I found a good site with lots of performance tuning info. Anyone intersted in the topic might want to check out:

http://www.sql-server-performance.com/articles_performance.asp

Questions about posting. See faq183-874

 

[swampBoogie]

I understand that Select * requires the database to take the extra step of looking up the column names.

The DBMS needs to look up the columns anyhow. For instance it needs to verify that the specified columns exists.

 

[Mike555]

Thanks for the correction. Incidentally it always seem to be my threads which spark these discussions!

--
Mike