Procedure not getting a value - 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)

Procedure not getting a value

Author

Topic

jcarver
Starting Member

18 Posts

Posted - 2007-07-16 : 11:51:27

I have a SQL statement that gets two parameters fed to it, one for a column name and one for a search text. The stored Procedure looks like

ALTER PROCEDURE dbo.sp_SearchVersion
@Field nvarchar(50) = NULL,
@LikeWhat nvarchar(50) = NULL
AS
SET NOCOUNT ON

SELECT
[name],
Right(swVers, 12) AS ShortVersion,
swVers,
OS,
JAM,
stamp,
mateID,
Site
FROM Mates
WHERE ISNULL(@Field, mateID) LIKE ISNULL('%'+@LikeWhat+'%', mateID)
RETURN

If I get rid of the ISNULL(@Field, mateID) and replace that with an explicit ' Site '(No Quotes) it works fine. However, if I tell that parameter to pass the value ' Site ' (No Quotes)to @Field, it won't return anything. Any ideas anyone?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-16 : 11:54:16

Do you also supply leading and trailing spaces for @LikeWhat parameter?

Peter Larsson
Helsingborg, Sweden

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-16 : 11:57:47

When there is a value in @field it becomes a string comparison rather than looking for data in the table with that column. For example, if @field = 'Site' coming through as parameter value, your SQL query becomes:


SELECT..
..
FROM Mates
WHERe 'Site'  LIKE <something>

which is not same as:


SELECT..
..
FROM Mates
WHERe Site LIKE <something>

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jcarver
Starting Member

18 Posts

Posted - 2007-07-16 : 12:13:01

I See. So how would I go about fixing that issue? Is there a way to get around that?

nr
SQLTeam MVY

12543 Posts

Posted - 2007-07-16 : 12:20:20

Have a look at dynamic sql.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-16 : 12:51:06

quote:
Originally posted by nr

Have a look at dynamic sql.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

here... http://www.sommarskog.se/dynamic_sql.html

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources