SQL Server stored procedure converts varchar to int

Question

I have a stored procedure that should run an IN statement. I want to know how to convert a string (a list of integers separated by commas) to int. In this example, positionIDyou need to convert. 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

Answer 1

You can take advantage of the fact that SQL Server 2008 now supports table types. You can determine the type of table and the construction of the .net a side DataTableand pass this as a parameter to your stored procedure. On the SP side, this parameter is of the type [any type of table 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)); 

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

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

@MyPositions Positions Readonly

@MyPositions .

Answer 2

, ? XML 2005 :

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

Answer 3

See SQL Server Partitioning

And combine this with CAST / CONVERT