Where in a stored procedure

Question

I have a myStoreProcedure like

@personId VARCHAR(MAX)

SELECT IdNo, LastName + ', ' + FirstName + ' ' + MiddleName AS Name
FROM            Person
WHERE CAST(Person.PersonId AS varchar(max)) in (@personId)

I want to list all the persons based on PersonId by using the WHERE IN but the datatype is varchar. So far this is what I have tried.

var p0 = p.Select(x => new { id = "'" + x.ToString() + "'" })
    .Select(c => c.id).ToArray();    

string personIds1 = string.Join(",", p0);

Let say, I have 2, 5 PersonId's, so by joining the Id with the code above the result is

personIds1 = '2','5'

Now, when I call the myStoreProcedure with the parameter I got no results.

var list = myEntities.myStoreProcedure(personIds1).Select(t => new PersonEntity
        {
            IdNo = t.IdNo,
            Name = t.Name,
        }).ToList();

My question is:

How to create a stored procedure to read the varchar just like the example:

--@personId = "'2', '5'" <-just an example
WHERE CAST(Person.PersonId AS varchar(max)) in (@personId)

so I can get the PersonId 2, 5 and so on.

is a Int DataType, this is my Primary key for the `Person` Table, I am trying to Cast to Varchar so I can use the `Where In`. this is my [reference](http://www.techonthenet.com/sql/in.php) — spajce, Mar 25 '13 at 21:18
I also can suggest you to avoid this way. You can try @rsenna sution, or try to fill temp table before select - in same connection / transaction. — evgenyl, Mar 25 '13 at 22:06

score 3 · Answer 1 · edited May 23 '17 at 12:11

3

You can pass a list of values to a stored-procedure... you just can't do it the way you're doing!

It is not possible to declare a "macro" parameter. A parameter must be bound to a single object; it cannot be used to generate a SQL statement. And that's exactly what you're trying to do: you are trying to pass a whole IN clause to the stored-procedure, which is simply not allowed.

(Of course, maybe you could do that by using dynamic execution. But please, do not try that on a production database, performance would be terrible... :-/)

If I had to do something similar, I would use a table-valued parameter:

CREATE TYPE [dbo].[IdTable] AS TABLE(
    [Id] [int] NULL
)
GO

CREATE PROCEDURE [dbo].[sp_get_peoplez] (
    @ids IdTable READONLY
) AS
BEGIN
    SELECT IdNo, LastName + ', ' + FirstName + ' ' + MiddleName AS Name
    FROM Person
    WHERE Person.PersonId IN (SELECT Id FROM @ids)
END
GO

And in order to use that procedure through entity-framework, see this answer.

edited May 23 '17 at 12:11

Community

1
1

answered Mar 25 '13 at 21:21

rsenna

11,775
1
54
60

+1 thank you for this answer I got some good idea, but for the meantime, i will try it first before I check as accepted `:D` – spajce Mar 25 '13 at 22:03
1

I wouldn't use dynamic SQL unless necessary, but mostly due to risk and debugging complexity, not performance. There's a performance overhead, but it's mostly an old wives tale. See [Speed Test: Dynamic SQL vs Stored Procedures](http://www.blackwasp.co.uk/SpeedTestSqlSproc.aspx) – Kirk Broadhurst Mar 25 '13 at 22:06
I apologize but I use the `dbContext.Database.SqlQuery(string sql)` to solve my problem, thank you again :) – spajce Mar 26 '13 at 00:03
@Kirk Broadhurst: IMHO it is naive to state that dynamic SQL execution cost is "negligible". The page you've shown us only considers pure execution time of a single statement! There is no regard to concurrency, recompilation, parameter sniffing, plan cache bloat and so forth. Yes, dynamic execution can be an option, if and only if parameter sniffing is used, and query recompilation is reduced (which is NOT not the case of the OP's solution). To put it bluntly, what you are proposing sounds amateurish, at best (unless, of course, we are not talking about real world, multiple-users scenarios). – rsenna Mar 26 '13 at 17:57

Where in a stored procedure

1 Answers1