Receive variable value in 'Where in'

Asked

Viewed 114 times

1

In my procedure I have the following variables:

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

Via C# application I am 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 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 in my past, it works, as below:

WHERE P.RELATORIO in (2,4)
c# sql asp.net

  • Tried to do this? y1.Parameters.Add("@Codigo", SqlDbType.VarChar).Value = "2.4"; If it’s not this, put the query and details plus the problem. It’s weird what you want, maybe for lack of complete information.

  • You’re passing the code on IN of WHERE?

  • I edited the question

2 answers

4


You can use a function to help you in 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 the values with comma or any other delimiter.

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

The function is useful to avoid doing manually in each SP its, and can also use other delimiters.

  • Perfect solution. It will help me a lot. Thank you.

1

Another option is to use a table as 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

So in C#code, instead of passing a string '2.4', you pass a table with two lines.

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";

Browser other questions tagged c# sql asp.net

You are not signed in. Login or sign up in order to post.