Passing comma-separated value from .NET to stored

问题:

I have an SQL query similar to the following:

create procedure test
(
   @param1 nvarchar(max)
)
as
begin
    select * from table where column1 in (@param1)
end

Now I need to pass the value of @param1 from my .net application in such a way that the above query works.

Can some one please advise me on how to pass from my VB.NET code a value which is similiar to below:

'1','2','3'

My main question is how do I structure value of parameter like above example from my .NET application?

回答1:

quickly like that, I would create a table valued function that would parse it so you can do

   select * 
   from table 
   where field in (select field from dbo.myfunction(@param1))

回答2:

For this type of thing, I use this function and use it as follows:

select Column1, column2 from my table where ID in (select item from fnSplit('1,2,3,4,5,6',','))

create FUNCTION [dbo].[fnSplit](
        @sInputList VARCHAR(8000) -- List of delimited items
      , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
    ) 

RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END

回答3:

I don't think the problem is in the values you are passing. @param1 is just a string.

You need to address this in your procedure. Your select statement will not be able to recognize the values in you IN clause. One solution is to take the comma-separated string and insert each record into a table variable Explained Here

If your table variable is table @param_list, you procedure test looks like:

create procedure test ( @param1 nvarchar(max) ) 
as begin 
select * from table where column1 in (Select thefield from @param_list); 
end