sp_executesql and like statement

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

Transact-SQL (2000)

sp_executesql and like statement

Author

Topic

kolesnik
Starting Member

8 Posts

Posted - 2005-09-10 : 10:52:09

Hi there,
where is mistake in this stored procedure? It just returns empty record set. I suppose that problem is with LIKE statement. I don't know what to do. Help me, please.

ALTER PROCEDURE items_get
(
@title varchar(50) = NULL
)
AS
declare @sql nvarchar(4000),
@paramlist nvarchar(4000)

select @sql='SELECT * FROM
document d LEFT JOIN
topic t ON d.do_id=t.tp_do_id LEFT JOIN
item i ON t.tp_id=i.it_tp_id
WHERE 1=1 '

IF @title IS NOT NULL
SELECT @sql = @sql + ' AND i.it_title LIKE ''%@xit_title%'' '

SELECT @paramlist = ' @xit_title varchar(50) '

EXEC sp_executesql @sql, @paramlist,@title

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-10 : 11:08:26

what is @xit_title?
do you need maybe:
SELECT @sql = @sql + ' AND i.it_title LIKE '''%'+ @xit_title + '%'''

Go with the flow & have fun! Else fight the flow

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-09-10 : 11:08:47

I guess there is problem with next line after your if statement try these if its work for u....

SELECT @sql = @sql + ' AND i.it_title LIKE %' + @title + '%'

Complicated things can be done by simple thinking

kolesnik
Starting Member

8 Posts

Posted - 2005-09-10 : 11:22:47

I tried to follow this example about building dynamic sql queries, it works without LIKE statement.

http://www.sommarskog.se/dyn-search.html

Your advice have not helped. Still struggling with problem.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-10 : 11:25:31

why do you even use dynamic sql for this??

Go with the flow & have fun! Else fight the flow

kolesnik
Starting Member

8 Posts

Posted - 2005-09-10 : 11:29:36

Thank you for your help guys, I already know the solution:
SELECT @sql = @sql + ' AND i.it_title LIKE ''%''+@xit_title+''%'''

kolesnik
Starting Member

8 Posts

Posted - 2005-09-10 : 11:31:26

I have a lot of search conditions (and don't know which are going to be used by user) And just for the sake of simplicity I asked you the question with one condition.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-10 : 11:44:52

multiple search conditions can be solved without dynamic sql:
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx
under number 4

Go with the flow & have fun! Else fight the flow

kolesnik
Starting Member

8 Posts

Posted - 2005-09-10 : 12:03:19

I know about this solution, but at the link I already wrote http://www.sommarskog.se/dyn-search.html
the author, Microsoft MVP, claims that it is "... A Simple but Slow Approach ..."

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-10 : 12:10:36

that is true... but with proper adjustments it can be preety ok

Go with the flow & have fun! Else fight the flow

kolesnik
Starting Member

8 Posts

Posted - 2005-09-10 : 12:38:26

Do you want to share know how, what kind of adjustments you think about?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-10 : 12:45:35

well for each case is different...
depends on how your data looks like, how many nuls are there in a column, etc...
i've found that by simply swaping
WHERE (Col1 = @Col1) or (@Col1 is null)
to
WHERE (@Col1 is null) or (Col1 = @Col1)
can make a difference.
i usually do this for columns that are not frequent in searches.
the best way to know for you is to test it.
use both approaches and test it on your data.
noone can say this way is surely better than the other.
each one is unique. there are of course general tips&tricks but who says it's the best?

Go with the flow & have fun! Else fight the flow

kolesnik
Starting Member

8 Posts

Posted - 2005-09-10 : 13:06:36

I wish I had time to use more approaches. I will be happy to finish my project before deadline using one "theoretically" right way. Thank you for you hints once more.

tarek294
Starting Member

1 Post

Posted - 2009-01-26 : 23:36:42

You will find answer in url below:
http://webapplication02.blogspot.com/2009/01/dynamic-sql-using-like.html

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-01-27 : 06:22:54

What?

This is the code you posted on that link:


DECLARE @SQL nvarchar(4000)
SET @SQL='SELECT [CategoryName]
FROM [StarterSite_productcatalog].[dbo].[Adventure Works Catalog_CatalogProducts]
where categoryname like ''%''+@param1+''%'''
DECLARE @param1 nvarchar(20)
DECLARE @paramlist nvarchar(20)
SET @param1='sl'
SET @paramlist = '@param1 varchar(20)'
EXEC sp_executesql @sql, @paramlist,@param1

That is truly horrible code.

A) You don't need dynamic SQL for this at all!
B) You declare Variables after you reference them (even if it is in a block of dynamic SQL). OK it works but.... URGH. Spare a though for the poor sap who has to debug this style of code if it goes wrong!

Cleaned up Example:


IF OBJECT_ID('tempDb..#dump') IS NOT NULL DROP TABLE #dump

CREATE TABLE #dump (
	[foo] NVARCHAR(255)
	, [bar] INT
	)

INSERT #dump (
	[foo]
	, [bar]
	)
      SELECT 'This', 5234
UNION SELECT 'Is', 123
UNION SELECT 'A', 1231
UNION SELECT 'Bad', 1002
UNION SELECT 'example!', 123123

DECLARE @sql NVARCHAR(4000)
DECLARE @param1 NVARCHAR(20)
DECLARE @paramList NVARCHAR(255)

SET @sql = N'
SELECT
	[foo]
	, [bar]
FROM
	#dump
WHERE
	[foo] LIKE ''%'' + @param1 + ''%'''

SET @param1 = N'a'
SET @paramList = N'@param1 NVARCHAR(20)'

EXEC sp_executeSql @sql, @paramList, @param1

-- And without the dynamic sql cruft.
SELECT
	[foo]
	, [bar]
FROM
	#dump
WHERE
	[foo] LIKE '%' + @param1 + '%'

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources