CODEWITHSUNDEEP
c#sqlstored-procedures
driveBy
driveBy

Reputation:

Send a list of IDs to a SQL Server stored procedure from c#

Is it possible to send a list of IDs to a stored procedure from c#?

UPDATE Germs
SET Mutated = ~Mutated
WHERE (GermID IN (ids))

Upvotes: 1

Views: 2678

Answers (4)

Kevin Dark
Kevin Dark

Reputation: 476

You could try what i have made do with:-

Create a function called Split_String

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE FUNCTION [dbo].[Split_String] 
(   
    @MyString varchar(5000)
)
RETURNS @Results TABLE
(
    Value varchar(1000)
) 
AS
    BEGIN
        DECLARE @Pos int
        DECLARE @StrLen int
        DECLARE @MyLen int
        DECLARE @MyVal varchar
        SET @pos = 1
        SET @MyLen = 1
        WHILE @MyString <> ''
            BEGIN
                SET @MyLen = charindex(',',@MyString)   
                IF @MyLen = 0 SET @MyLen = Len(@MyString)
                INSERT @Results SELECT replace(substring(@MyString, @pos, @MyLen),',','')
                SET @MyString = SUBSTRING(@MyString,@MyLen+1,len(@MyString))
            END
        RETURN 
    END

Then when you use IN() use in the following fashion with a comma separated string:-

SELECT * FROM [youDataBase].[dbo].[Split_String] (<@MyString, varchar(5000),>)

Upvotes: 2

Ben Hoffstein
Ben Hoffstein

Reputation: 103375

This may be a dirty hack, but you can create a temp table and then join to it from within your stored procedure (assuming they are accessed during the same connection). For example:

CREATE TABLE #ids (id int)
INSERT INTO #ids VALUES ('123') -- your C# code would generate all of the inserts

-- From within your stored procedure...
UPDATE g
SET Mutated = ~Mutated
FROM Germs g
JOIN #ids i ON g.GermID = i.id

Upvotes: 2

Nick
Nick

Reputation: 9203

Yep, you can use a chunk of XML to build your list of ID's. Then you can use OPENXML and select from that record set.

Look up OPENXML, sp_preparexmldocument, sp_removexmldocument

Upvotes: 0

Boris Callens
Boris Callens

Reputation: 93357

According to This article, you could try the Table Value Parameter.

Upvotes: 1

Related Questions