stored procedure parameters

How can I pass these two value combination se开发者_运维技巧parated by AND/ as parameter value to stored procedure:

"8033301552*" OR "08033301552*"

or

"Taiwo*" AND "Ayedun*"

I mean do I need to create a string and pass it as parameter?

Officially not supported.

A workaround can be found at

http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/a5e0fdb24323aa13/e0fcc4c2d38883ec%23e0fcc4c2d38883ec?pli=1

What doesn't work has nothing really to do with RS but has to do with Stored Procedures in SQL Server. You cannot do the following in a stored procedure. Let's say you have a Parameter called @MyParams Now you can map that parameter to a multi-value parameter but if in your stored procedure you try to do this: select * from sometable where somefield in (@MyParams) It won't work. Try it. Create a stored procedure and try to pass a multi-value parameter to the stored procedure. It won't work. What you can do is to have a string parameter that is passed as a multivalue parameter and then change the string into a table. This technique was told to me by SQL Server MVP, Erland Sommarskog For example I have done this inner join charlist_to_table(@STO,Default)f on b.sto = f.str So note this is NOT an issue with RS, it is strictly a stored procedure issue. Here is the function:

CREATE FUNCTION charlist_to_table 
                    (@list      ntext, 
                     @delimiter nchar(1) = N',') 
         RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL, 
                             str     varchar(4000), 
                             nstr    nvarchar(2000)) AS 
   BEGIN 
      DECLARE @pos      int, 
              @textpos  int, 
              @chunklen smallint, 
              @tmpstr   nvarchar(4000), 
              @leftover nvarchar(4000), 
              @tmpval   nvarchar(4000) 
      SET @textpos = 1 
      SET @leftover = '' 
      WHILE @textpos <= datalength(@list) / 2 
      BEGIN 
         SET @chunklen = 4000 - datalength(@leftover) / 2 
         SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen) 
         SET @textpos = @textpos + @chunklen 
         SET @pos = charindex(@delimiter, @tmpstr) 
         WHILE @pos > 0 
         BEGIN 
            SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1))) 
            INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval) 
            SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr)) 
            SET @pos = charindex(@delimiter, @tmpstr) 
         END 
         SET @leftover = @tmpstr 
      END 
      INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)), 
ltrim(rtrim(@leftover))) 
   RETURN 
   END 
GO 

-- Bruce Loehle-Conger MVP SQL Server Reporting Services "Roland Müller" wrote in message

You can pass arrays of values into a stored proc using XML data. I've found this to be a workable solution and solves issues of dynamic SQL possibly being affected by SQL injection.

Take a look at this link for a basic example of how to do it. As the author suggests, this may be overkill for some solutions, but I think it is quite neat and could be modified to meet your requirements.