In this procedure, I want to delete those number which coming in @msisdn from Master Table and insert into tblDeactive:
ALTER PROCEDURE [dbo].[msisdn_deactivition](@msisdn varchar(1024))
AS
BEGIN
SET NOCOUNT ON;
insert into tblDeactive SELECT * from msisdn_master where msisdn in (@msisdn);
delete from msisdn_master where msisdn in (@msisdn);
END
...but it does not work?
You can't use a single query parameter in place of a list of values. One parameter = one value.
See Parameterize an SQL IN clause for several solutions and lots of discussion about using parameters with the IN( ) predicate.
You need to use a split function, that will split your delimitted @msisdn into a table to select from.
Have a look at this link
How to pass a list of values or array to SQL Server stored procedure?
IN in TSQL needs to take multiple arguments per item. At the moment, if you pass it "123,456,789", it is looking for the single row with value "123,456,789". It isn't looking for "123", "456" or "789".
Look for something like a split udf - plenty on the internet. This takes your delimited string and returns a column of values. Typically you then join the the udf results (rather than use IN).
DON'T use EXEC here, as you shouldn't trust the input.
