SQL Server stored procedure convert varchar to int

问题:

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

Here is my stored procedure:

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

回答1:

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

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

回答2:

Does the list you pass in have to be comma-separated? XML will work for 2005 and beyond:

DECLARE @productIds xml
SET @productIds ='<Positions><id>3</id><id>6</id><id>15</id></Positions>' 

DECLARE @Positions TABLE (ID int) 

INSERT INTO @Positions (ID) SELECT ParamValues.ID.value('.','VARCHAR(20)')
FROM @productIds.nodes('/Positions/id') as ParamValues(ID) 


SELECT * FROM 
    dbo.vwLog L
INNER JOIN 
     @Positions p
ON    p.ID = L.PositionID

回答3:

See SQL Server Split

And combine that with CAST/CONVERT