Accept input as string and then how to convert that to a integer values

Question

I have a procedure that accepts input as string and in procedure I want the parameter as integer, Please find the below procedure.

CREATE PROCEDURE [dbo].[spPERSON_SELECT]
(
    @PersonID VARCHAR(MAX) = NULL
)
AS
BEGIN 

    SELECT PERSON.name
    FROM PERSON
    WHERE
        PERSON.person_id NOT IN (@PersonID) 

END
GO

Please help!

score 1 · Answer 1 · answered Jul 16 '13 at 21:12

You can either convert that varchar variable into table variable (TSQL's 'array') - search SO, there dozens of examples

or use dynamic SQL e.g.

DECLARE @sSQL varchar(max)

SET @sSQL = 'SELECT PERSON.name  FROM PERSON WHERE PERSON.person_id NOT IN '
SET @sSQL = @sSQL + '(' + @PersonID + ')'

EXEC (@sSQL)

Accept input as string and then how to convert that to a integer values

1 Answers1