Below code works for 'Main Stream' or 'Premium' as parameter, however I am trying to make it work for both of them as you can see below, but it doesn't return any results. How can I make the below code work?
declare @myParameter varchar(50)
SET @myParameter = 'Main Stream , Premium'
select * FROM sales
where myCategory IN (@myParameter)
You could try something like
--Split
DECLARE @textXML XML
DECLARE @data NVARCHAR(MAX),
@delimiter NVARCHAR(5)
SELECT @data = 'Main Stream , Premium',
@delimiter = ','
SELECT @textXML = CAST('<d>' + REPLACE(@data, @delimiter, '</d><d>') + '</d>' AS XML)
SELECT T.split.value('.', 'nvarchar(max)') AS data
FROM @textXML.nodes('/d') T(split)
You could either store this in a temp table, or use it in the IN clause.
For @Hoy comment
You could look at nodes() Method (xml Data Type)
The nodes() method is useful when you want to shred an xml data type instance into relational data. It allows you to identify nodes that will be mapped into a new row.
Also, have a look at xml Data Type Methods
You could then use it as
select *
FROM sales
where myCategory IN (
SELECT T.split.value('.', 'nvarchar(max)')
FROM @textXML.nodes('/d') T(split)
)
try:
select id from dbo.split("24,25,26",',');
so you need to to:
select * FROM sales
where myCategory IN ( select id from dbo.split(@myParameter,",");
and add split function in your database by running:
Create FUNCTION [Split](@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (id varchar(8000))
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(id) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end
Go
I found a nice solution for a similar issue on another thread on SO.
Basically, it uses a function for splitting a string (@MyParameter in your case) using a delimiter (',' in your case).
Here is how you call the function:
declare @myParameter varchar(50)
SET @myParameter = 'Main Stream,Premium'
--this table will hold delimited values
DECLARE @ParsedTable TABLE
(
[id] [int] NOT NULL,
[content] [nvarchar](50) NULL
)
--Parsing @myParameter
INSERT INTO @ParsedTable
SELECT * from [dbo].[SplitString](@myParameter, ',')
--SELECT * FROM @ParsedTable --This will show you the values...
--run the query
select * FROM sales
where myCategory IN (SELECT content from @ParsedTable)
And here is the code for creating the split functions:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SplitString]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[SplitString]
go
/****** Object: UserDefinedFunction [dbo].[SplitString] Script Date: 11/04/2013 19:04:05 ******/
SET ANSI_NULLS ON
go
SET QUOTED_IDENTIFIER ON
go
CREATE FUNCTION [dbo].[SplitString]
(
-- Add the parameters for the function here
@StringToDelimit nvarchar(500),
@deliminator nvarchar(2)
)
RETURNS
@ReturnTable TABLE
(
-- Add the column definitions for the TABLE variable here
[id] [int] IDENTITY(1,1) NOT NULL,
[content] [nvarchar](50) NULL
)
AS
BEGIN
Declare @delimitorIdx int
Declare @content varchar(50)
--initialize spaces
Select @delimitorIdx = charindex(@deliminator,@StringToDelimit,0)
While @delimitorIdx > 0
Begin
Select @content = substring(@StringToDelimit,0,charindex(@deliminator,@StringToDelimit,0))
Insert Into @ReturnTable(content)
Select @content
Select @StringToDelimit = substring(@StringToDelimit,charindex(@deliminator,@StringToDelimit,0)+ 1,len(@StringToDelimit) - charindex(' ',@StringToDelimit,0))
Select @delimitorIdx = charindex(@deliminator,@StringToDelimit,0)
end
If len(@StringToDelimit) > 0
Insert Into @ReturnTable
Select @StringToDelimit
RETURN
END
go
declare @myParameter varchar(50)
SET @myParameter = 'Main Stream , Premium'
select * FROM sales
where myCategory IN (select * from string_split(@myParameter,','))
