SQL Server 存储过程将 varchar 转换为 int [英] SQL Server stored procedure convert varchar to int

问题描述

我有一个需要运行 IN 语句的存储过程.我想知道一种将字符串(逗号分隔的整数列表)转换为 int 的方法.在本例中，positionID 需要进行转换.请帮忙.谢谢

I have a stored procedure which need to run IN statement. I want to know a way to convert a string (list of comma separated integers) to int. In this example, positionID needs to be converted. Please help. Thanks

这是我的存储过程:

Create PROCEDURE [dbo].[spCount]
    @year varchar(50),
    @positionID varchar(50)
AS
BEGIN

Select
    ApplicantID, [Name], PositionID, COUNT(*) AS Votes
                       FROM          dbo.vwLog
                       WHERE Year = @year And PositionID in (@positionID)
                       GROUP BY ApplicantID, [Name], PositionID
                        Order By PositionID, Votes DESC
END

推荐答案

您可以利用 SQL Server 2008 现在支持表类型这一事实.您可以定义表类型并在 .net 端构造一个 DataTable 并将其作为参数传递给您的存储过程.在 SP 方面，该参数的类型为 [您制作的任何表类型] 这是一个示例.

You can take advantage of the fact that SQL Server 2008 now supports table types. You can define a table type and on the .net side construct a DataTable and pass that as a parameter to your stored procedure. On the SP side that parameter is of type [whatever tabletype you made] Here is an example.

TotalPositions = [Some List] //of CSV List
DataTable Positions = new DataTable(); //Create the Datatype
Positions.Columns.Add("PositionID", typeof(int)); //
foreach (string sPos in TotalPositions.Split(','))
Positions.Rows.Add(int.Parse(sPos)); 

然后您可以将位置附加为存储过程的参数

You can then Append Positions as a parameter for your stored procedure

SqlParameter Param = new SqlParameter();
Param.Value = Positions
Param.SqlDbType = SqlDbType.Structured;
Param.ParameterName = @Positions                    
command.Parameters.Add(Param);

在您的数据库中，您必须将表类型定义为

In your database you have to define a table type as

CREATE TYPE [dbo].[Positions] AS TABLE(
    [Position] int NULL,
)
GO

并在您的存储过程中添加

and in your stored procedure add

@MyPositions Positions Readonly

现在您可以将 @MyPositions 视为程序中的一个表并与之进行比较.

Now you can treat @MyPositions as a table in your procedure and compare to it.

这篇关于SQL Server 存储过程将 varchar 转换为 int的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！