Receive variable value in 'where in'

1

In my procedure I have the following variables:

@Codigo VARCHAR(MAX),@cont as int, @DataPago as VARCHAR(10)

Via C # application I'm passing the values as follows:

y1.Parameters.Add("@Codigo", SqlDbType.VarChar).Value = "2,4";
y1.Parameters.Add("@cont", SqlDbType.Int).Value = 1;
y1.Parameters.Add("@DataPago", SqlDbType.VarChar, 10).Value = "20/05/2015";

The problem is that I'm getting the following error:

  

Conversion failed when converting the varchar value '2,4' to data type int.

However, when I put the direct value into my procedure, it works, as below:

WHERE P.RELATORIO in (2,4)
    
asked by anonymous 23.10.2015 / 18:10

2 answers

4

You can use a function to help you with this, follow the code below for your problem:

CREATE FUNCTION [dbo].[SplitStrByDelimiter]
(
    @String varchar(MAX), @Delimiter char(1)
) 
RETURNS @temptable TABLE (items varchar(MAX) COLLATE SQL_Latin1_General_CP1_CI_AI)       
AS   
BEGIN      
    declare @idx int       
    declare @slice varchar(8000)       

    select @idx = 1       
        if len(@String)<1 or @String is null  return       

    while @idx!= 0       
    begin       
        set @idx = charindex(@Delimiter,@String)       
        if @idx!=0       
            set @slice = left(@String,@idx - 1)       
        else       
            set @slice = @String       

        if(len(@slice)>0)  
            insert into @temptable(Items) values(@slice)       

        set @String = right(@String,len(@String) - @idx)       
        if len(@String) = 0 break       
    end   
return
END

And then in your query you can pass values with a comma or any other delimiter.

..
WHERE P.RELATORIO in (select items from [dbo].[SplitStrByDelimiter](@Codigo,','))

The function is useful avoiding to do manually in each SP its, and also to use other delimiters.

    
23.10.2015 / 18:42
1

Another option is to use a table as a parameter

CREATE TYPE RelatorioType AS TABLE ( 
    Codigo int primary key
);
GO

/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE dbo.ConsultarRelatorio
    @Relatorio RelatorioType Readonly, 
    @cont int,
    @DataPago varchar(10)
AS
BEGIN
    SELECT * 
    FROM Pessoas P
    JOIN @Relatorio R ON P.RELATORIO = R.Codigo
    WHERE
        P.cont = @cont AND
        P.DataPago = @DataPago
END
GO

Then in the C # code, instead of passing a string '2,4', you pass a table with two rows.

var relatorio = new DataTable();
relatorio.Columns.Add("Codigo", typeof(int));
relatorio.Rows.Add(2);
relatorio.Rows.Add(4);

var pRelatorio = new SqlParameter("@Relatorio", SqlDbType.Structured);
pRelatorio.TypeName = "dbo.RelatorioType";
pRelatorio.Value = relatorio;

y1.Parameters.Add(pRelatorio);
y1.Parameters.Add("@cont", SqlDbType.Int).Value = 1;
y1.Parameters.Add("@DataPago", SqlDbType.VarChar, 10).Value = "20/05/2015";
    
23.10.2015 / 19:31