如何在SQL中用“”，“”替换符号“”？

苏什玛·古达（Sushma Gowda）

我正在编写一个SQL查询，以便它必须将a替换为,要','传递的字符串。传递的字符串就像，jack,john,tony但是输出的获取是只有表头的空表。

您能为此提供答案吗？

CREATE PROCEDURE [dbo].[usp_GetBillingSystem]
(
@BillingCode VARCHAR(300)
)
AS
BEGIN 
declare @ListOfPlanID varchar(30)
declare  @trimmed varchar(30)
declare  @replaced varchar(30)
set @ListOfPlanID =@BillingCode


If  IsNull(@ListOfPlanID,'')='' return 
If LEN(@ListOfPlanID)=0 return 
SET @trimmed=LTRIM(RTRIM(@ListOfPlanID))
set @replaced=''''+REPLACE(@trimmed,',',''',''')+''''   
select * from BillingSystem where BillingCode IN (@replaced)
END

在vb.net中，我以此方式从上面的sql查询中获取了获取数据表的代码

Public Function getBillingSystemAttributesInfo(ByVal ConnectionString As String, ByVal                      BillingCode As String) As DataTable
Dim conn As SqlConnection
Dim sqlcmd As SqlCommand
Dim dtBillingSystem As DataTable
Try
conn = New SqlConnection(ConnectionString)
conn.Open()
sqlcmd = New SqlCommand("usp_GetBillingSystem", conn)
With sqlcmd

.CommandType = CommandType.StoredProcedure


 .Parameters.Add("@BillingSystemCode", SqlDbType.VarChar, 300)
 .Parameters("@BillingSystemCode").Value = BillingSystemCode

  dataadapter = New SqlDataAdapter()
  dataadapter.SelectCommand = sqlcmd
  dtBillingSystem = New DataTable("BillingSystem")
  dataadapter.Fill(dtBillingSystem)
   End With
   Catch ex As Exception
    Throw ex
    Finally
    conn.Close()
    sqlcmd = Nothing
    End Try
    Return dtBillingSystem
End Function

萨西什·瓦瑞斯（Satheesh Variath）

问题是您不能将变量传递给IN运算符，您必须在此处使用动态SQL

CREATE PROCEDURE [dbo].[usp_GetBillingSystem]
(
@BillingCode VARCHAR(300)
)
AS
BEGIN 
declare @ListOfPlanID varchar(30)
declare  @trimmed varchar(30)
declare  @replaced varchar(30)
set @ListOfPlanID =@BillingCode


If  IsNull(@ListOfPlanID,'')='' return 
If LEN(@ListOfPlanID)=0 return 
SET @trimmed=LTRIM(RTRIM(@ListOfPlanID));

set @replaced=''''+REPLACE(@trimmed,',',''',''')+'''';
declare @qry nvarchar(max);
set @qry = 'select * from BillingSystem where BillingCode IN (' + @replaced + ')';
exec sp_executesql @qry
end

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。