SQL Parameter as Char[]

Question

All I am having a little problem here 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.

score 1 · Answer 1 · answered Feb 02 '11 at 11:01

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.

score 1 · Accepted Answer · answered Feb 02 '11 at 13:57

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,','))

[Which bit did you write yourself?](http://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-recor/2837662#2837662) — Martin Smith, Feb 02 '11 at 18:28
Your Right. Thanks for your Attention. My english is bad, and this text is wroted by of my firend. usualy i don't write **myself** in my text's because many of solutions that i found are from internet. — ABI, Feb 02 '11 at 18:56

score 0 · Answer 3 · answered Feb 02 '11 at 11:21

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)

SQL Parameter as Char[]

3 Answers3