SQL Parameter as Char[]

All I am having a little problem he开发者_如何学JAVAre is the sample code

create table Sections (ID int, name char(1), Description nvarchar(50))

insert into Sections values (1,'A','A Section')
insert into Sections values (2,'B','B Section')
insert into Sections values (3,'C','C Section')
insert into Sections values (4,'D','D Section')
insert into Sections values (5,'E','E Section')
insert into Sections values (6,'F','F Section')

select * from Sections
declare @Names nvarchar(10) ='A,B,C';
select *from Sections where name in (@Names)

What i want is that i will pass an string that string will be splited into each char and that char array will be passed in the where clause. I have tried it with numeric that works fine but in the char it not working properly. what i have done is like this

declare @Categories nvarchar(50)='ABC';
declare @array nvarchar(50);
declare @lenth int =len(@Categories);
Declare @c int =1;
declare @param nvarchar(50)='' ;

while @c <=@lenth
begin
set @param = @param + ''''+ SUBSTRING(@Categories ,@c,1) +''''+','
set @c =@c+1

end
print @param

declare @Cats nvarchar(50); 
set @Cats=  substring(@param, 0,len(@param)-0)

Its output like 'A','B','C'. but when i am passing this @Cats to the sp this will return no records.

also one more thing i am not in favor of that this query to be executed as dynamic query.

---

Also, you can use this UDF - user defined function, I wrote once for myself.

CREATE FUNCTION [dbo].[fnSplit] 
(
    @Value nvarchar(max),
    @Seprator nvarchar(5)   
)
RETURNS @Table TABLE
(
    val NVARCHAR(MAX)
) 
AS
BEGIN
    DECLARE @XML XML
    SET @XML = N'<root><r>'+REPLACE(@VALUE,@SEPRATOR,'</r><r>')+'</r></root>'

    INSERT INTO @Table(val) SELECT x.value('.','NVARCHAR(MAX)') FROM @XML.nodes('//root/r') AS records(x)
    RETURN
END

To use this function, see below:

dbo.fnSplit(input list string, splitter string) return Table

Example usage:

declare @Sections table (ID int, name char(1), Description nvarchar(50))
declare @Names nvarchar(10) ='C,B';

insert into @Sections values (1,'A','A Section')
insert into @Sections values (2,'B','B Section')
insert into @Sections values (3,'C','C Section')

select *from @Sections where name in (Select val from dbo.fnSplit(@names,','))

---

I can see in your second script that originally you have your params without the , delimiter. If you can pass them that way and if your parameters are always char(1), then you can try this:

DECLARE @Categories nvarchar(50) = 'ABC';

SELECT s.*
FROM Sections s
  INNER JOIN master..spt_values v ON v.type = 'P'
    AND v.number BETWEEN 1 AND LEN(@Categories)
WHERE s.name = SUBSTRING(@Categories, v.number, 1)

One note on master..spt_values: this is a system table that exists in SQL Server 2005+, and I don't know about earlier versions. And you didn't mention what's yours.

---

This is still dynamic sql, but you can build up a string like

select * from Sections where name in ( 'A','B','C') 

and then execute it using exec.

eg

declare @Names nvarchar(80) 
declare @command nvarchar(80) 
set @Names ='''A'',''B'',''C'''
set @command = 'select * from Sections where name in ( ' + @Names + ')'

exec (@str)