CODEWITHSUNDEEP
c#sql-serverstored-proceduresparameters
Thành Nguyễn
Thành Nguyễn

Reputation: 9

How to get multi values from a single parameter into procedure in SQL Server

Please help me with the following problem.

I want to get multi values from a single parameter into the procedure in SQL Server, I'm learning SQL and C#.

Here is my code:

ALTER PROCEDURE AProc_Getback 
    (@fcode VARCHAR(10))
AS
BEGIN
    UPDATE t_PALM_PersonnelFileMst 
    SET fdflag = 0, frdate = null, fddate = null,
        fdtype = null, fdreason = null
    WHERE fcode IN ('@fcode')

    DELETE FROM t_pald_dimissiontrm 
    WHERE fcode IN ('@fcode')
END

Exec AProc_Getback '512888,512889'

Thanks very much

Upvotes: 0

Views: 480

Answers (3)

Fabio
Fabio

Reputation: 32445

Use Table-Valued Parameters

CREATE TYPE FCodes AS TABLE ( 
    Code VARCHAR(32) -- Use type of "fcode" column
);

Stored procedure

ALTER PROCEDURE AProc_Getback 
(
    @fcode FCodes
)
AS
BEGIN
    UPDATE t_PALM_PersonnelFileMst SET
        fdflag = 0,
        frdate = null,
        fddate = null,
        fdtype = null, 
        fdreason = null
    WHERE fcode IN (SELECT Code FROM @fcode)

    DELETE FROM t_pald_dimissiontrm 
    WHERE fcode IN (SELECT Code FROM @fcode)
END

Execute

DECLARE @Codes AS FCodes
INSERT INTO @Codes VALUES ('123'), ('456'), ('789')

EXEC AProc_Getback @Codes

From C# you can create parameter of type SqlDbType.Structure with DataTable as value and pass it to the stored procedures

var codesParameter = new SqlParameter
{
    ParameterName = "@fcodes",
    SqlDbType = SqlDbType.Structure,
    TypeName = "dbo.FCodes", // Important! - name of type in database
    Value = dataTableOfCodes // Should contain column with name "Code"
};

using (var connection = new SqlConnection(connectionString)) 
using (var command = connection.CreateCommand()) 
{
    command.CommandText = "dbo.AProc_Getback";
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add(codesParameter);

    connection.Open();
    command.ExecuteNonQuery();
}

Upvotes: 3

Hiran
Hiran

Reputation: 1180

  Alter PROCEDURE AProc_Getback ( @fcode varchar(Max)

    ) AS

    Begin update t_PALM_PersonnelFileMst set fdflag = 0,frdate = null,fddate = null,fdtype = null, fdreason = null where fcode in (select Item
from dbo.SplitString(@fcode, ',')) 
    delete from t_pald_dimissiontrm where fcode in (select Item
from dbo.SplitString(@fcode, ',')) end

then

Exec AProc_Getback '512888,512889'

Upvotes: -1

ViKiNG
ViKiNG

Reputation: 1334

First of all, your parameter must be capable of holding incoming data. currently, it can only receive data up to 10 characters. See @fcode varchar(10).

If you don't have split function available with you, you can create one. Alternatively, you can use some inline code to achieve that. For example:

ALTER PROCEDURE AProc_Getback(@Fcode VARCHAR(MAX))
AS
BEGIN

    CREATE TABLE #FCodes (Item  VARCHAR(30));
    DECLARE @Insert VARCHAR(MAX) = 'INSERT INTO #FCodes VALUES ('''+REPLACE(@Fcode,',','''),(''')+''');';
    EXEC (@Insert);   


    UPDATE t_PALM_PersonnelFileMst
    SET
        fdflag = 0,
        frdate = NULL,
        fddate = NULL,
        fdtype = NULL,
        fdreason = NULL
    WHERE fcode IN(SELECT Item FROM #FCodes);


    DELETE FROM t_pald_dimissiontrm
    WHERE fcode IN(SELECT Item FROM #FCodes);
END;

Upvotes: 0

Related Questions