-1

I have the following stored procedure that takes one parameter and I need to use that in my IN clause, but that does not work. I get this error when trying.

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

Here is my stored procedure..

CREATE PROCEDURE [dbo].[p_GetSegment] 
    @SegmentIds nVarChar(20)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT dbo.Segment.Name
    FROM dbo.tbl_Category  
    INNER JOIN dbo.Segment ON dbo.tbl_Category.SegmentId
    WHERE (dbo.Segment.Id IN (@SegmentIds))

I pass in "1,2". How can I make this work?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
MTplus
  • 2,077
  • 4
  • 34
  • 51
  • 1
    `IN` works on a list of values. `@Segmentids` is a single value, no matter what it contains. If you want to use multiple values you can use STRING_SPLIT which is available in all supported SQL Server versions, or use [a table-valued parameter](https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-ver15) and pass a table with those values to the stored procedure. How you do that depends on the client. In T-SQL you can create a table variable with `declare @ids table (id int)` – Panagiotis Kanavos Nov 19 '20 at 15:55
  • What is the client? Another T-SQL script? A .NET program? Something else? – Panagiotis Kanavos Nov 19 '20 at 15:56
  • Does this answer your question? [Parameterize an SQL IN clause](https://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause) Converting to `int` shouldn't be a big leap beyond the answers provided. – HABO Nov 19 '20 at 16:12
  • String_split does not work in sql server 2008. I tried by executing the stored procedure. Anyway, I got a solution that worked below. I will then use this stored procedure in a .net webb application – MTplus Nov 19 '20 at 16:55

1 Answers1

0

In SQL Server 2016+, you can use string_split():

WHERE dbo.Segment.Id IN (SELECT value FROM STRING_SPLIT(@SegmentIds, ','))

You can use any string split function for this purpose. You can also just use `like:

where ',' + @SegmentIds + ',' like '%,' + convert(varchar(255), dbo.Segment.Id) + ',%'
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786