Basic IF and LEN() question - 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 2008 Forums

Transact-SQL (2008)

Basic IF and LEN() question

Author

Topic

Westside
Starting Member

9 Posts

Posted - 2010-07-16 : 14:17:16

Hi,

I'm trying to build a condition around this where clause but I can't get the syntax right.

IF LEN(@myCommaList) > 0
WHERE s.list_Id IN (' + @myCommaList + ')'

I'm not good with SQL and logic so any help appreciated. I only want to include the where clause if that variable has length greater than 0

Any help appreciated

-ws

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-07-16 : 14:29:39

Are you doing some form of Dynamic SQL?

If so maybe this?

DECLARE @SQL VARCHAR(MAX)

SET @SQL = CAST('<start of query>'  AS VARCHAR(MAX))

IF LEN(@myCommaList) > 0
BEGIN
	SET @SQL = @SQL + CAST('WHERE s.list_Id IN (' + @myCommaList + ')' AS VARCHAR(MAX))
END

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-07-16 : 14:35:53

use Visakh's ParseValues function. It takes a delimited string ansd turns it into a table, which you can then join on

CREATE FUNCTION ParseValues
(@String varchar(8000),
@Delimiter char(1)
)
RETURNS @RESULTS TABLE
(ID int identity(1,1),
Val varchar(1000)
)
AS
BEGIN
DECLARE @Value varchar(100)

WHILE @String is not null
BEGIN
SELECT @Value=CASE WHEN CHARINDEX(@Delimiter,@String) >0 THEN LEFT(@String,CHARINDEX(@Delimiter,@String)-1) ELSE @String END,
@String=CASE WHEN CHARINDEX(@Delimiter,@String) >0 THEN SUBSTRING(@String,CHARINDEX(@Delimiter,@String)+1,LEN(@String)) ELSE NULL END
INSERT INTO @RESULTS (Val)
SELECT @Value
END
RETURN
END

Jim

Everyday I learn something that somebody else already knew

Westside
Starting Member

9 Posts

Posted - 2010-07-16 : 14:44:12

@Lamprey, That worked... thanks..

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2010-07-16 : 20:09:44

IF the list is pretty big, you should also consider table valued parameters.. or SQLBlog.com has a post by Adam Machanic (if I remember correctly) on this exact same topic - creating a table out of a list of values.. he compares different techniques with perf results.. so you can pick the one that works best for you. worth the read.

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