从数据库检索 [英] Retrieve from database

问题描述

我有一个String str，其中包含多个客户ID，例如C00001，C00002，C00003 ..用逗号分隔.
现在，我想将此字符串str作为单个参数传递给我的存储过程.我尝试了下面的代码，但没有用.
有人可以帮我吗?
例如

I have one String str which contains multiple customer Id like C00001,C00002,C00003.. seperated by comma.
Now i wants to pass this string str as single parameter to my stored procedure. I tried below code but it doen''t works.
can anybody please help me???????

eg

foreach (string s in idlist)
        {
            str = str +"'"+ s +"'" + ",";
        }
        str = str.Substring(0, str.Length - 1);

现在，我想将此str作为参数传递给我的存储过程

存储过程就是这样
从Customer_Master中选择*，其中CustomerId IN(@CustomerId)

Now i wants to pass this str to my stored procedure as parameter

stored procedure is like this
select * from Customer_Master where CustomerId IN(@CustomerId)

推荐答案

请使用以下代码:

将单个参数的客户ID字符串发送到存储过程.然后在编写SP时使用以下方法:

Hi, use below code:

Send Customer ID String in single parameter to Stored Procedure. Then use below method when you writing SP:

Create procedure [dbo].[Get_Customer]
(
@strCustomerID varchar(5000)
)
As
Begin
Declare @x int, @i int
Declare @Cust_ID varchar(50)
Select *, ROW_NUMBER() OVER(ORDER BY (Select 0)) AS RowNumber into #Cust from dbo.split(@strCustomerID,',')

Select @x = COUNT(*) from #Cust
	while(@i<=@x)
	Begin
		Select @Cust_ID = items from #Cust where RowNumber = @i
SET @query='Select * from CustomerTable where CustomerID ='''+ @Cust_ID + ''''
print @query
		EXEC sp_executesql @query 
		------------------------------------
		
		Set @i = @i+1
	End


End

您可以创建用户定义的功能来分割客户ID，例如

You can create user defined function to split Customer IDs like this

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
GO
--Test
select * from fnSplit('1,22,333,444,,5555,666', ',')
select * from fnSplit('1##22#333##444','##')  --note second item has embedded #
select * from fnSplit('1 22 333 444  5555 666', ' ')

您可以在存储过程中使用此功能.使用相同功能的示例代码片段如下所述

This function you can use in your Stored Procedure. sample code snippet for using the same function is as follows

select * from Customer_Master where CustomerId IN(SELECT * FROM	dbo.fnSplit(@CustomerId, ','))

其中 @CustomerId 是 varchar 类型的参数.传递 @CustomerId 字符串的值时，可能类似于C00001，C00002，C00003 ...

希望这对您有所帮助.如果您需要进一步的帮助，请告诉我.
如果此方法正常，请接受解决方案.
谢谢

Where @CustomerId is parameter of type varchar. While passing the values of @CustomerId string can be like C00001,C00002,C00003...

I hope this will help you out. If you need further help please let me know.
If this works fine please accept the solution.
Thanks

根据您的存储过程，您不能这样做！

You can''t do that according to your stored procedure!

select * from Customer_Master where CustomerId IN(@CustomerId)

这与您想要的类似:
-您的str应该是:

This is similar what you want:
- Your str should be:

str="'C00001','C00002','C00003'";

-您的存储过程应为:

- Your stored procedure should be:

exec ('select * from Customer_Master where CustomerId IN(' + @CustomerId + ')')

这篇关于从数据库检索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！