CODEWITHSUNDEEP
sqlsql-server-2012
Wompguinea
Wompguinea

Reputation: 378

VARCHAR range as variable?

I have a stored procedure that needs to be filtered by a select box that could have a value between 0 to 3. This value corresponds to table where each int value could have two or more VARCHAR values associated with it. For example 0 could be assigned to 'A' 'B' or 'C' while 1 could be 'D' or 'E'

I have attempted to store these options as a variable @m which is set like this:

DECLARE @m varchar(50) = 
     CASE 
        WHEN @mid = 0 
           THEN N'''A'',''B'',''C'',''D'',''E'''
        WHEN @mid = 1 
           THEN N'''A'',''B'''
        WHEN @mid = 2 
           THEN N'(''C'',''D'')'
        ELSE N'''E''' 
     END

Which returns the expected value if I return it on it's own, with the ' marks and , separators in the correct places. But when I try to use it to select the correct records from my table, I get nothing.

select @m --Returns 'C','D','E'
select * from table where mValue in (@m) -- Returns nothing
select * from table where mValue in ('C','D','E') -- Returns all expected rows

And it doesn't make any difference if I have the brackets in the @m variable or not, then second select won't return any rows.

Upvotes: 0

Views: 328

Answers (2)

AB_87
AB_87

Reputation: 1156

As Gordon mentioned, you will have to create a function for splitting your string. I use below logic (if it's always delimited by ,)

CREATE FUNCTION [dbo].[fn_split_string] ( @stringToSplit VARCHAR(MAX) )
RETURNS
 @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE CHARINDEX(',', @stringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(',', @stringToSplit)  
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList 
  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
 END

 INSERT INTO @returnList
 SELECT @stringToSplit

 RETURN
END

Then you can simply use this function in your query.

select * from table where mValue in (select * from fn_split_string (@m))

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270181

One method is to split @m. You can find various "split" functions on the web (the functionality was added in SQL Server 2016).

The idea is then to do something like:

with m(item) as (
      select *
      from dbo.split(@m, ',') s
     )
select *
from table
where mValue in (select item from m) ;

Upvotes: 1

Related Questions