like statement wrong?????? - 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 2000 Forums

Transact-SQL (2000)

like statement wrong??????

Author

Topic

gotafly
Yak Posting Veteran

54 Posts

Posted - 2007-01-08 : 19:00:49

ALTER PROCEDURE [dbo].[GetArticlesList]
(
@ArticleIssueID varchar(30),
@ArticleTypeID varchar(30),
@ArticletownID varchar(30)
)
AS
DECLARE @sql nvarchar(4000)
Set @sql =
' SELECT ArticleTitle, ArticleID
FROM Articles
WHERE (ArticleTypeID = ' +@ArticleTypeID+') AND (ArticleIssueID = ' +@ArticleIssueID+')'
if @ArticletownID <> '0'
Set @sql = @sql + ' and (Articletown like %''' + @ArticletownID + '''%)'

Set @sql = @sql + ' ORDER BY ArticleDateStart DESC'

EXEC (@sql)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2007-01-08 : 19:06:23

(Articletown like ''%' + @ArticletownID + '%'')'

rockmoose

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-08 : 19:08:36

Stay away from the dark side! Only use dynamic SQL where absolutely needed!

ALTER PROCEDURE dbo.GetArticlesList
(
	@ArticleIssueID VARCHAR(30),
	@ArticleTypeID VARCHAR(30),
	@ArticletownID VARCHAR(30)
)
AS

SET NOCOUNT ON

SELECT		ArticleTitle,
		ArticleID
FROM		Articles
WHERE		ArticleTypeID = @ArticleTypeID
		AND ArticleIssueID = @ArticleIssueID
		AND (@ArticleTownID = '0' OR Articletown LIKE '%' + NULLIF(@ArticleTownID, '0') + '%'
ORDER BY	ArticleDateStart DESC

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-08 : 19:24:09

You are better of using NULL as value for @ArticleTownID, instead of '0'.

Peter Larsson
Helsingborg, Sweden

gotafly
Yak Posting Veteran

54 Posts

Posted - 2007-01-08 : 19:30:13

Still not working. I need to do this with dynamic sql PESO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-08 : 19:38:38

quote:
Originally posted by gotafly

Still not working.

What? Which error do you get?

quote:
Originally posted by gotafly

I need to do this with dynamic sql

Why?

With the specs provided above, my suggestion should work just fine.
Is there something you are not telling us?

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources