Using an array as input variable 2

[Danster]

Hi all,
I have a stored procedure which requests some inputs from the user.
One of my variables needs to be a list of numbers which I then will be using to pull records from a table which match with the variables. I am expecting the user to enter into @MyList something like 1,5,17,22,25,34 (basically a random set of numbers with a delimiter).

i.e. CREATE PROCEDURE sp_MySP @MyList

SELECT * FROM MyTable WHERE MyField IN @MyList

MyField is an INT field. I've spend too many hours trying to get this to work so now I asking the experts...is this possible?
What problems am I getting?
The stored proc is the basis of a Crystal report. After the user has entered in the numbers, it doesn't return any rows.
I've tried things like WHERE STR(MyField) IN @MyList, getting the input to be in the format '1', '4', '12' etc (i.e enclosing each value within its own single-quotes). Nothing seems to work.

I'm pretty sure the bottom line problem is that @MyList needs to be an array whereas SQL is expecting it to be a single value. I just want users to enter in their numbers separated by a comma or other delimiter.

Ideas?

Danster

 

[ESquared]

You either need to use dynamic SQL or convert a text list of integers into a rowset.

Info about dynamic SQL:
Implementing a Dynamic WHERE Clause and the Discussion of it
Tek-tips thread183-850718: Pros and Cons of using Dynamic SQL vs Stored procedure

About a text list of integers:
Passing a Comma Delimited Parameter to a Stored Procedure
Creating a User Defined Function in SQL for Comma-Delimited Searches
Pass Arrays To SQL Server
Turning Strings into Tables

Those should do the trick for you.

-Erik


P.S. If @MyList is an int, it cannot contain '1,5,17,22,25,34' because that is not a number, that is a text string such as varchar. One of the most important distinctions in any programming language is the difference between a value and a string.

For example, the string '1' does not equal the value 1. If you look at the space in memory holding these, you'll find:

1 (Value): 1 (binary 00000001)
'1' (String): 49 (binary 00110001)

The 49 is the ASCII number of the character displayed on the screen. 65 is the uppercase letter A. 97 is the lowercase letter a. 32 is a space. And so on.

I hope this helps

 

[TRACEYMARYLAND]

i had a value i passed in as
'internet,email,fax'
@Usage_Type nvarchar(100) = null,



CHARINDEX(',' + CAST(DAILYUSAGE_Usage_type as varchar(100)) + ',',',' +
case @Usage_Type when '' then CAST(DAILYUSAGE_USage_type as varchar(100)) else @Usage_Type end + ',')>0

This reads the string until it finds the , and then compaires it with a column i have...DAILYUSAGE_Usage_Type

 

[ESquared]

Tracey,

That's an interesting solution.

To other readers:

Read
',',','
as
',' , ',' +
and you'll be less confused.

 

[ESquared]

And you're selecting ALL records when passing an empty string, interesting.

 

[Danster]

ESquared,
Thanks for your post with various links. I went with the first one from codeproject.com but likely the others would've done just as good.
Anyway, I've applied it to my code & bingo!

 

[ESquared]

Eww! I just looked more closely at that solution and he uses a system table!?!? You should convert it to use a table variable.

 

[TRACEYMARYLAND]

Which one did you use in codeproject

Just for reference

 

[Danster]

Traceymaryland
I pretty much copied the whole codeproject solution as stated in the link given by Esquared. Basically I created a normal table which just stores 1000 rows, each with INT column, values 1 thru 1000.

Then did the same SELECT(Convert(int, NullIf.....and so on as mentioned.
Then created a temp table called #MyTable, mentioned under step 4.

ESquared,
I had same reaction when I saw the temp table and figured a table variable would've been just as good. Ah well, it still works.

 

[ESquared]

Well, I'm sorry I posted that first link, some of the others are probably better. Having to use a real table just to store sequential numbers in it sounds not so great, to me.

One method I particularly like is a user-defined inline function that returns a rowset you can join against!!

 

[ESquared]

Fed up with all the examples I saw online, I wrote my own versions. They are substantially different for simple common-sense reasons.

The string version will accept a delimiter of null or empty string, and will return each character of the input text in its own row. This same behavior doesn't make sense for the integer function so it just returns an empty table.

To my annoyance, Cast, Convert, and Str all barf when you try to convert a non-numeric string to int, instead of just returning zero. So to my delight the IsNumeric function works on strings to tell you whether they can be converted to a numeric data type. But, to my annoyance again, it says that the string ',' is numeric but the conversion functions obviously fail on that. So, you can break this function if you pass in commas wih a non-comma-delimiter between them. There may be other ways to break it. I don't know how to fix the problem.



To return strings:

IF EXISTS (SELECT * FROM sysobjects WHERE Name = N'SplitTokensVarChar' AND Type = 'TF') DROP FUNCTION SplitTokensVarChar
GO
CREATE Function SplitTokensVarChar(
	@InputText varchar(7999),
	@Delimiter varchar(20))
RETURNS @Array TABLE (
	TokenID int PRIMARY KEY,
	Value varchar(7999))

AS

BEGIN

	DECLARE @TokenID int,
		@Pos int,
		@End int,
		@TextLength int,
		@DelimLength int,
		@Value varchar(7999)
	
	SELECT @TokenID = 1,
		@InputText = LTrim(RTrim(@InputText)),
		@TextLength = Len(@InputText)	

	IF @TextLength = 0 RETURN
	
	IF IsNull(@Delimiter,'') = '' BEGIN
		WHILE @TokenID <= @TextLength BEGIN
			INSERT @Array VALUES (@TokenID, SubString(@InputText,@TokenID,1))
			SET @TokenID = @TokenID + 1
		END
	END ELSE BEGIN
		SELECT @Pos = 1,
			@End = 1,
			@DelimLength = Len(@Delimiter),
			@InputText = @InputText + @Delimiter,
			@End = CharIndex(@Delimiter, @InputText)
		WHILE @End > 0 BEGIN
			SET @Value = SubString(@InputText, @Pos, @End - @Pos)
			INSERT @Array VALUES (@TokenID, LTrim(RTrim(@Value)))
			SELECT @TokenID = @TokenID + 1,
				@Pos = @End + @DelimLength,
				@End = CharIndex(@Delimiter, @InputText, @Pos)
		END
	END
	
	RETURN

END

GO

SELECT * FROM SplitTokensVarChar('frog,lamb,sheep,cow,duck',',')

And to return integers:

IF EXISTS (SELECT * FROM sysobjects WHERE Name = N'SplitTokensInt' AND Type = 'TF') DROP FUNCTION SplitTokensInt

GO

CREATE Function SplitTokensInt(
	@InputText varchar(7999),
	@Delimiter varchar(20))
RETURNS @Array TABLE (
	TokenID int PRIMARY KEY,
	Value varchar(7999))

AS

BEGIN

	DECLARE @TokenID int,
		@Pos int,
		@End int,
		@TextLength int,
		@DelimLength int,
		@ValueText varchar(7999)
	
	SELECT @TokenID = 1,
		@InputText = LTrim(RTrim(ISNULL(@InputText,''))),
		@TextLength = Len(@InputText)	

	IF @TextLength = 0 RETURN
	IF NOT IsNull(@Delimiter,'') = '' BEGIN
		SELECT @Pos = 1,
			@End = 1,
			@DelimLength = Len(@Delimiter),
			@InputText = @InputText + @Delimiter,
			@End = CharIndex(@Delimiter, @InputText)
		WHILE @End > 0 BEGIN
			SET @ValueText = SubString(@InputText, @Pos, @End - @Pos)
         INSERT @Array VALUES (@TokenID, CASE WHEN IsNumeric(@ValueText)=1 THEN Convert(int,@ValueText) ELSE 0 END)
			SELECT @TokenID = @TokenID + 1,
				@Pos = @End + @DelimLength,
				@End = CharIndex(@Delimiter, @InputText, @Pos)
		END
	END
	
	RETURN

END

GO

SELECT * FROM SplitTokensInt('28,13,frog,51,2,0,7',',')

Now, to use it just do this:

SELECT MyTable.*
FROM MyTable INNER JOIN dbo.SplitTokensInt(@MyList, ',') I ON MyTable.MyField = I.Value

E²

P.S. And for any UDF-function-haters out there (donutman you know I'm talking to you), consider that this will run only once, so even joined against the humongousest table shouldn't be a problem. It's not impossible it could even be faster. Isn't joining one table to another faster than using many OR conditions?

 

[donutman]

The only FAQ I've written uses UDF's. You should make your code an FAQ.
-Karl

[red] Cursors, triggers and user-defined functions are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

Oh my God, I am guilty of RAS Syndrome!!!! I can't believe I said "UDF function." Please forgive me of my grievous error!

User Defined Function function.
Redundant Acronym Syndrome Syndrome

Before I make this an FAQ does anyone want to critique it or try it out or suggest I take up the accordion instead because I'm such a bad programmer?

 

[donutman]

I've got a suggestion...convert that END ELSE BEGIN! Aren't you up kind of early this morning? I have an excuse I'm rebuilding my file server...it has to be ready in a few hours.
I'm wondering about something I saw in one of your links in regard to our coding thread. Should variable have their type indicated. Your UDF mixes data types and it would be good to be aware of it:
@InputText = @InputText + @Delimiter
so maybe it should be
@vInputText = @vInputText + @iDelimiter
I do that in VB, but haven't been doing that in SQL. Programs get very long, SQL usually doesn't. But that's not a good excuse.
-Karl

[red] Cursors, triggers and user-defined functions are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

I live on the west coast.

Yes, I saw the END ELSE BEGIN and I honestly tried all the ways we worked out and didn't like them! Sigh... each one has a serious drawback. I refuse to accept
[tt] RegularStatement END[/tt]
and I sure don't like
[tt] END END[/tt]
and I don't like
[tt] END[/tt]
[tt] END[/tt]
although it's slightly better, and you definitely hate
[tt] END ELSE BEGIN[/tt]
and why could SQL have been better designed? Sigh.

Which of the last three do you vote for?

I also noticed the variable type prefix, which I religiously do in VB, and perhaps you are right it should be done here, too. I tend to use more descriptive variables than many programmers and that is a help right there, but...

@Delimiter isn't an integer!

 

[donutman]

DECLARE @TokenID int,
@Pos int,
@End int,
@TextLength int,
@DelimLength int,
@ValueText varchar(7999)
and I thought we agreed on END END as the exception. Do you have some stubborn German blood in you too? Guess we are going to have to DEMAND a vote since nobody seems to want to touch it.
-Karl

[red] Cursors, triggers and user-defined functions are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

Opps, nm on the declares...I'm getting tired, but almost done with server.
-Karl

[red] Cursors, triggers and user-defined functions are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

Good night...job done, everything back to normal. At least until the users get here and tell me what's broke. I'm going to have to post a warning on XP Server 2003. Don't install it on your SQL box. It screws up links to Access databases!
-Karl

[red] Cursors, triggers and user-defined functions are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

I do have German blood in me. Bah!! Humbug!!!


I was just thinking about how I don't really feel like writing 14 functions:

SplitInt
SplitVarChar
SplitReal
SplitCurrency
SplitDecimal
SplitDateTime
...

And so on. How could we pass the type desired as a parameter, such as:

Split(varchar,'frog,in,a,blender')

Maybe using an inline function rather than a table function? It would look almost identical but the last select statement would convert the values as appropriate by selecting them all from the temporary table and using some form of dynamic SQL to do the type conversion... what do you think?

I also had another thought about the END ELSE BEGIN thing. What if we broke the indent rules? I know, this is a bad idea. I hate that SQL doesn't see an ELSE or an ELSE BEGIN by itself as an END ELSE BEGIN. Can we ask for syntax changes in the new version? yuk yuk

IF Condition1 BEGIN
   IF Condition2 BEGIN
      Statement1
      Statement2
   END
 END 
ELSE BEGIN
   Statement3
   Statement4
END

 

[donutman]

Well I'm home now...going to bed, but since you're still up. I think your newest compromise is ok too. Let's do present the 3 choices for a vote. I know they're going to think we're completely nuts to be sooo anal about it.
I can't think right now.
-Karl

[red] Cursors, triggers and user-defined functions are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]