-1

I am working with a Stored Proc with takes several parameters. It turns out, though, that for one of the params, I need to loop through a list of items and call the SP multiple times. But as I pass the SP every possible value of that type, it would seem more efficient (and simpler) to just bypass the parameter by saying "get them all" by means of a wildcard.

Is it possible to do that, such as with a "*" or a "%" or something?

Theoretically (based on the research I've done), "%" should work, but in my case it's not returning any records. Am I doing something wrong, with this code:

. . .
new SqlParameter()
{
    ParameterName = "@Member",
    SqlDbType = SqlDbType.VarChar,
    Value = '%'
},
. . .

?

B. Clay Shannon-B. Crow Raven
  • 8,547
  • 144
  • 472
  • 862
  • 1
    Just pass NULL with parameter and use `WHERE col = ISNULL(@param, col)` or for more performance `WHERE col = @param or @param IS NULL` – Lukasz Szozda Dec 30 '15 at 15:48
  • That looks like something I would need to add to the SP, which I can't - that's not my domain (no pun intended). – B. Clay Shannon-B. Crow Raven Dec 30 '15 at 15:50
  • 1
    Why then actually USE a parameter, why not just change the stored proc to use the wild card? - ah I see your other comment, you are prevented from doing that... – Mark Schultheiss Dec 30 '15 at 15:50
  • @MarkSchultheiss: See my comment in response to lad2025. I need a way to handle it from my C# code. – B. Clay Shannon-B. Crow Raven Dec 30 '15 at 15:51
  • 1
    Well, `%` *is* a wildcard *when* it's used with the [`LIKE` operator](https://msdn.microsoft.com/en-us/library/ms179859.aspx). But if that's not happening, it's not going to work. – Damien_The_Unbeliever Dec 30 '15 at 15:51
  • 1
    Yes, I was just going to comment that the stored procedure must then use something similar to `...LIKE @Member....` See the answer here for an example: http://stackoverflow.com/questions/10601318/stored-procedure-with-wildcard-parameters – Mark Schultheiss Dec 30 '15 at 15:54

1 Answers1

1

No. Without being able to modify the stored procedure code, there is nothing you can pass as a parameter that will act like a wild card.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52