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

 

[donutman]

Several thoughts. There are several metrics that should be considered by the programmer when writing a SP. The top two in the vast majority of cases, imho, are run-time efficiency and easy of understanding. Which comes 1st depends on the situation. Number of lines of code is ONLY important to the extent that extra spaces and "affected" writing style puts too much "style" and not enough "meat" on each screen full (see my rewrite).
I would strenuously argue for multiple UDFs and NO DYNAMIC SQL. There's WAY TOO MUCH dynamic SQL floating around...I may have to put those programmers on my terrorist watch list. There's virtually no GOOD reason to write generic SPs...it's a matter of priorities gone awry, imho.
I would also argue for a few minor changes in form, but one definitive change: Rn - Do Not use Select in place of Set just because you don't have to repeat Select!

CREATE Function SplitTokensVarChar(
      @InputText varchar(7999), @Delimiter varchar(20))
   RETURNS @Array TABLE (TokenID int PRIMARY KEY, Value varchar(7999))
AS
   DECLARE @TokenID int, @Pos int, @End int [green]--[One type per line][/green]
   DECLARE @TextLength int, @DelimLength int
   DECLARE @Value varchar(8000)   
   SET @TokenID = 1
   SET @InputText = LTrim(RTrim(@InputText))
   SET @TextLength = Len(@InputText)    
   IF @TextLength = 0 RETURN
[green]--exit possible here    [/green]
   IF IsNull(@Delimiter,'') = '' BEGIN
        WHILE @TokenID <= @TextLength BEGIN
            INSERT @Array VALUES (@TokenID, SubString(@InputText,@TokenID,1))
            SET @TokenID = @TokenID + 1
        END END
   ELSE BEGIN
       SET @Pos = 1
       SET @End = 1
       SET @DelimLength = Len(@Delimiter)
       SET @InputText = @InputText + @Delimiter
       SET @End = CharIndex(@Delimiter, @InputText)
       WHILE @End > 0 BEGIN
          SET @Value = SubString(@InputText, @Pos, @End - @Pos)
          INSERT @Array VALUES (@TokenID, LTrim(RTrim(@Value)))
          SET @TokenID = @TokenID + 1
          SET @Pos = @End + @DelimLength,
          SET @End = CharIndex(@Delimiter, @InputText, @Pos)
       END
   END
   RETURN

-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]

I missed two 7999 conversions.

 

[donutman]

Say SQLSister, why not add your template documentation idea to the above code. Let's make this a poster child for the Code thread. Of course, ESquared can do what he likes...it's his FAQ.
I also forgot to add the type identifier to the variable names. A little procedual documentation would be helpful (IF IsNull clause). BTW, in the SplitInt version why isn't the return table Value an integer? And what's the point of having TokenID column?
-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]

In the Int version the Value was supposed to be int... it is in my code but in creating the version I blew it.

The ID was because for whatever reason people on the web seemed to be concerned with knowing what order the tokens were in. This seemed like a reasonable requirement so I supported it. For example, you could use it for sorting fields and would definitely need an order for that.

 

[ESquared]

I don't agree with your idea about DECLARE and SET.

For one thing, there are cases where you MUST select two values into variables in one operation:

SELECT @Err = @@ERROR,
   @RCount = @@ROWCOUNT

Go try this any other way and it won't work (took me a while to figure this one out, too).

For another, even though you and I both have a strong desire to do things the right way I also think we should balance that with the easy way. It's already going to be a big enough challenge for most people new to SQL or programming to learn how to indent properly. To make them type SET for each assignment is extra work--and unneeded in my mind. Additionally, I think it's easier to read with one SELECT.

-- Multiple SETs

   SET @Pos = 1
   SET @End = 1
   SET @DelimLength = Len(@Delimiter)
   SET @InputText = @InputText + @Delimiter
   SET @End = CharIndex(@Delimiter, @InputText)

-- One SELECT

   SELECT
      @Pos = 1,
      @End = 1,
      @DelimLength = Len(@Delimiter),
      @InputText = @InputText + @Delimiter,
      @End = CharIndex(@Delimiter, @InputText)

Yeah, you have to get used to putting commas after each line (or as some programmers do, before each one, see below) but that is a small drawback compared to how much cleaner it is and easy to read. Don't you agree?

   SELECT @Pos = 1
      , @End = 1
      , @DelimLength = Len(@Delimiter)
      , @InputText = @InputText + @Delimiter
      , @End = CharIndex(@Delimiter, @InputText)

FYI, when I said dynamic SQL I was also referring to pseudo-dynamic SQL such as using more complicated WHERE clauses or using program flow logic.

For example, in the inline table version of my function which allows a variable type to be selected:

SELECT TokenID,
   CASE @VarType
      WHEN 'varchar' THEN RetVal -- Already varchar
      WHEN 'int' THEN Cast(int, RetVal)
      WHEN 'bit' THEN Cast(bit, RetVal)
      ELSE RetVal END Value
   FROM @Array

You can put anything you want into the CASE|ELSE clause to define a default type. And there might need to be more 'intelligence' for certain data types. For example, int could be

   WHEN 'int' THEN Cast(int,
      CASE Left(RetVal,1) WHEN '1' THEN 1 ELSE 0 END)

or some other logical way to make sure no error occurs no matter what string is passed in.

E²

 

[donutman]

Select is for queries and Set is for assignment. They make your intentions know without equivocation. If a Select has an assignment, then I expect it to be set-based not procedural. I don't want to have to examine statements below a particular line to determine what is meant by the current line unless there's no choice. The @@ERROR example is nothing more than an exception that requires special handling and should be typed on 1 line!
Just my opinion.
-Karl

 

[ESquared]

SQL Server Books Online:

SELECT @local_variable

Specifies that the given local variable (created using DECLARE @local_variable) should be set to the specified expression.

It is recommended that SET @local_variable be used for variable assignment rather than SELECT @local_variable. For more information, see SET @local_variable.
quote]

Alas, I am defeated by the very resource I turned to to defend my position!

 

[ESquared]

Future readers of this thread may like to consult the FAQ answer on Tek-Tips, Passing a list of values to a Stored Procedure (Part II).