Function review?

Question

Function review?

SQL_path

SSCrazy

Points: 2810
More actions
April 27, 2012 at 7:46 am

#273305

Hi, How do I make this function to handle null or empty values
I do filter my other stored procedure data based on data received from this function, but it fails when there is empty or null value . Thanks.
Stored proc piece
-----------------
Where
(p.product IN (SELECT Item FROM dbo.brakstring(@product, ',')) Or @product IS NULL)
------FUNCTION
ALTER FUNCTION [dbo].[brakstring](@Text varchar(max),@Delimiter varchar(2)= ' ')
RETURNS @Strings TABLE
(
Position int IDENTITY PRIMARY KEY,
Item varchar(max)
)
AS
BEGIN
DECLARE @index int
SET @index = -1
WHILE (LEN(@Text) > 0)
BEGIN
SET @index = CHARINDEX(@Delimiter,@Text)
IF (@Index = 0) And (LEN(@Text) > 0)
BEGIN
INSERT INTO @Strings VALUES (@Text)
BREAK
END
IF (@Index > 1)
BEGIN
INSERT INTO @Strings VALUES (LEFT(@Text,@Index - 1))
SET @Text = RIGHT(@Text, (LEN(@Text) - @index))
END
ELSE
SET @Text = RIGHT(@Text, (LEN(@Text) - @index))
END
RETURN
END

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

You should read the article in my signature about splitting strings. It will blow this away for performance and it will explain how to use it.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 2

Oddly enough, I'll second that recommendation. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vinu512 SSCoach Points: 15729 More actions · Answer 3

if that doesn't help....then I'll third the recommendation. lol!!!!....this could go on :w00t::-D:hehe::w00t::-D:hehe:

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉