stored procedure selecting multiple record id

Question

I have a collection of record ids(x,y,z,..) which is pass to a stored procedure as a string value. My stored procedure is:

CREATE PROCEDURE [dbo].[Sp_Getvalue](@recordId varchar(30)) 
AS
BEGIN    
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    select  
    x.record_Id,
    x.name,
    x.address from dbo.tblvalue x where x.record_Id in (@recordId)
END

in code behind

var obj = context.Sp_Getvalue(value);

I am getting error as

An exception of type 'System.Data.EntityCommandExecutionException' occurred in System.Data.Entity.dll but was not handled in user code

That's the message that terminates your application, not the real exception. Debug your code and check what the EntityCommandExecutionException tells you. Even better, add a try/catch around your code and log the full exception — Panagiotis Kanavos, Feb 13 '15 at 09:51
I don't believe you can directly do an `... in @IdList`, Read this article http://www.codeproject.com/Tips/584680/Using-comma-separated-value-parameter-strings-in-S — Dave Becker, Feb 13 '15 at 09:52
possible duplicate of [Passing a varchar full of comma delimited values to a SQL Server IN function](http://stackoverflow.com/questions/878833/passing-a-varchar-full-of-comma-delimited-values-to-a-sql-server-in-function) — Mahesh, Feb 13 '15 at 10:09
By the way, as long as we're here: it's generally frowned upon to use a comma-delimited string to pass multiple IDs to a stored procedure. (For example: what if you passed so many IDs that your `varchar(30)` variable wasn't long enough?) Try using an XML variable instead. — Ann L., Feb 13 '15 at 17:25

score 0 · Answer 1 · answered Feb 13 '15 at 12:03

You can not use the parameter directly in IN clause Please check the following syntax where I used a SQL split string function

CREATE PROCEDURE [dbo].[Sp_Getvalue](@recordId varchar(30)) 
AS
BEGIN    
    SET NOCOUNT ON;

    select  
        x.record_Id,
        x.name,
        x.address 
    from dbo.tblvalue x 
    where 
        x.record_Id in (
            select val from dbo.split(@recordId,',')
        )
END

You can copy the split function codes from here: http://www.kodyaz.com/articles/t-sql-convert-split-delimeted-string-as-rows-using-xml.aspx and create on your target database

score 0 · Answer 2 · answered Feb 13 '15 at 15:54

You cannot directly use the comma separated values in IN clause and no need of any create any functions for that. There are two approaches to do that

1. Use Dynamic Sql to get records according to values in @recordId

CREATE PROCEDURE [dbo].[Sp_Getvalue](@recordId varchar(30)) 
AS
BEGIN    
SET NOCOUNT ON;

DECLARE @query NVARCHAR(MAX)
SET @query = 'INSERT INTO TABLETOINSERT(COL,COL2,COL3)
              SELECT record_Id,name,address 
              FROM tblvalue 
              WHERE record_Id IN('+@recordId+')' 

EXEC SP_EXECUTESQL @query

END

Click here to view result

If you do not want to insert, and just want select statement, remove the INSERT INTO TABLETOINSERT(COL,COL2,COL3) from the dynamic sql.

2. Using XML format to split comma separated values to row and using IN

CREATE PROCEDURE [dbo].[Sp_Getvalue](@recordId varchar(30)) 
AS
BEGIN    
SET NOCOUNT ON;


INSERT INTO TABLETOINSERT(COL1,COL2,COL3)
SELECT record_Id,NAME,[ADDRESS]
FROM tblvalue
WHERE record_Id IN
(
    -- Convert comma separated values to rows
    SELECT LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'KeyWords' 
    FROM  
    (        
         SELECT CAST ('<M>' + REPLACE(@recordId, ',', '</M><M>') + '</M>' AS XML) AS Data      
    ) AS A 
    CROSS APPLY Data.nodes ('/M') AS Split(a)
)

END

Click here to view result

stored procedure selecting multiple record id

2 Answers2