存储过程查询无效 [英] Stored procedure query is not working
问题描述
我有一个存储过程,我传递参数如下。
Hi,
I have a stored procedure and I am passing parameter as below.
SqlDataCommand cmd = new SqlDataCommand("dbo.GetCustomer");
cmd.AddParam("@CustomerId", strCustomerId, DbType.String);
获取客户的存储过程查询如下:
Stored procedure query to get customer is as below.
SELECT * FROM Customer WHERE CustomerId IN(@CustomerId)
这里如果我在存储过程中传递单个客户ID它工作正常,但如果我用逗号分隔传递多个客户ID,则不会获取结果。
我通过SQL执行sp Server Management Studio甚至得到相同的结果。
Here if I pass single customer id in stored procedure it is working fine but if I pass multiple customer id with comma separated it is not fetching result.
I executed sp through SQL Server Management Studio even getting same result.
USE [TestDB]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[qa_RK_TransactionsProcess]
@CustomerId = N'199350029'
SELECT 'Return Value' = @return_value
GO
上面给出了预期的结果。
但是当我与多个客户执行时如下。
The above give me the expected result.
But when I execute with multiple customer as below.
USE [TestDB]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[qa_RK_TransactionsProcess]
@CustomerId = N'199350029,199350026'
SELECT 'Return Value' = @return_value
GO
它不会返回任何结果。
即使我测试了它正在构建的查询如下。但是我不知道它为什么会这样。
It does''nt return any result.
Even I tested the query it is building properly as below. But I don''t know why it is behaving like this.
SELECT * FROM Customer WHERE CustomerId IN(199350029,199350026)
Any非常感谢帮助。
Any help will be very much appreciated.
推荐答案
您需要将csv值转换为表的行,以使其在IN子句中工作。
以下是一个示例
You need to convert the csv value into rows of a table for it to work inside the IN clause.
Here is a sample
DECLARE @Customer TABLE
(
CustomerId VARCHAR(100),
CustomerName VARCHAR(100)
)
INSERT INTO @Customer
SELECT 1, 'Bob' UNION ALL
SELECT 2, 'Steve' UNION ALL
SELECT 3, 'Smith' UNION ALL
SELECT 4, 'Matt' UNION ALL
SELECT 5, 'John'
SELECT * FROM @Customer
DECLARE @CustomerId VARCHAR(100)
SET @CustomerID = N'1,3,4'
SELECT * FROM @Customer WHERE CustomerId IN (@CustomerID) --This won't work
SELECT * FROM @Customer WHERE CustomerId IN (SELECT VarcharValue FROM dbo.ufn_CsvToVarchar(@CustomerID)) --This will work
这是用于将csv值转换为行的函数。
This is the function used to convert csv values into rows.
CREATE Function dbo.ufn_CsvToVarchar ( @Array varchar(MAX))
returns @VarcharTable table
(VarcharValue varchar(50))
AS
begin
declare @separator char(1)
set @separator = ','
declare @separator_position int
declare @array_value varchar(1000)
set @array = @array + ','
while patindex('%,%' , @array) <> 0
begin
select @separator_position = patindex('%,%' , @array)
select @array_value = left(@array, @separator_position - 1)
Insert @VarcharTable
Values (Cast(@array_value as varchar(50)))
select @array = stuff(@array, 1, @separator_position, '')
end
return
end
希望这会有所帮助。
Hope this helps.
use databasename
create procedure procedurename
(
@CustomerId int
)
as
begin
SELECT * FROM Customer WHERE CustomerId=@CustomerId
end
尝试此程序
try this procedure
这篇关于存储过程查询无效的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!