Dynamic where clause quotes for variable

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 2000 Forums

SQL Server Development (2000)

Dynamic where clause quotes for variable

Author

Topic

BananaQuaalude
Starting Member

2 Posts

Posted - 2007-12-03 : 11:46:53

I have the following dynamic sql in a stored procedure:

DECLARE @SQL NVARCHAR(2000)
DECLARE @ITEM VARCHAR(30)

SET @SQL = 'SELECT * FROM ITEM '
SET @ITEM = '1SK0000001'

IF @ITEM != '%'
BEGIN
SET @SQL = @SQL + 'WHERE ITEM_ID LIKE ' + @ITEM
END
print @SQL

exec sp_executesql @SQL
-----------------------------

The problem is here is the SQL being executed:

SELECT * FROM ITEM WHERE ITEM_ID LIKE 1SK0000001

when this is what I need:

SELECT * FROM ITEM WHERE ITEM_ID LIKE '1SK0000001'

I'm using dynamic SQL because if the @ITEM parameter (hard coded here but will be passed in to the SP) does not have a value, I don't even want to include that WHERE clause.

This is a simple example, but for my real problem I'm using an outer join for a table, and if the parameter isn't present I can't include a where clause related to that table because that will prevent data in the main table from being returned.

I've one example of doing this, from http://www.sommarskog.se/dyn-search.html referred to in another thread here, but can't get it to work:

IF @custname IS NOT NULL -- 51
SELECT @sql = @sql + ' AND c.CompanyName LIKE @xcustname + ''%'''

Any help is greatly appreciated!

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-12-03 : 11:48:33

Try if this works:


DECLARE @SQL NVARCHAR(2000)
DECLARE @ITEM VARCHAR(30)

SET @SQL = 'SELECT * FROM ITEM '
SET @ITEM = '1SK0000001'

IF @ITEM != '%'
BEGIN
	SET @SQL = @SQL + 'WHERE ITEM_ID LIKE ''' + @ITEM + ''''
END
print @SQL

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

BananaQuaalude
Starting Member

2 Posts

Posted - 2007-12-03 : 12:00:20

Awesome!

I was confused by the way the quotes are set apart. This works.

Thank you very much!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-04 : 01:16:24

Why dont you use non-dynamic sql?



DECLARE @ITEM VARCHAR(30)

SET @ITEM = '1SK0000001'

IF @ITEM != '%'
BEGIN
	SELECT * FROM ITEM WHERE ITEM_ID LIKE @ITEM+'%'
END

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources