How to convert a string of number into integer value

[Kingkumar]

Hi ,
I have a string of numbers eg(1,2,3,4,5) which i pass to my stored pocedure as varchar ( @agentid shown in code below) now the problem i am facing is that agentid is int so it throws an error.
can some one tell me how to convert this string to int type
or is there a different way of sendig this list of int as for example can i send it like array or something

thanks for your help

following is my code
CREATE PROCEDURE [dbo].[spUSER_GetUserAgents]
@agentid varchar(500)
AS

SET NOCOUNT ON

SELECT
distinct con.AgentNo, isnull(c.LastName + ', ' + c.FirstName, 'N/A') AS AgentName
FROM
MPUserGroup con inner join contact c on con.AgentNo = c.ClientNo
WHERE
con.AgentNo in (@agentid)
ORDER BY
AGENTNAME

 

[rac2]

Instead of converting the string to a set list of integers, build the query as a string then EXECUTE the string.

EXECUTE(
  "SELECT 
        distinct con.AgentNo, isnull(c.LastName + ', ' + c.FirstName, 'N/A')  AS AgentName 
     FROM 
        MPUserGroup con inner join contact c on con.AgentNo = c.ClientNo 
    WHERE
        con.AgentNo in (" + @agentid + ")
    ORDER BY 
        AGENTNAME"
)

 

[SQLBill]

You won't be able to convert that string into an integer. Integers do not have commas. You could parse out each individual number and convert that to an integer.

Check out the use of CHARINDEX and PATINDEX in the BOL. Using one of those, you could find the location of each comma and 'pull' out the number between commas.

-SQLBill

Posting advice: FAQ481-4875

 

[JamesLean]

There are two FAQs on how to pass a list of values to a stored procedure:

Part 1
faq183-3979

Part 2
faq183-5207

--James

 

[Kingkumar]

Thanks a lot guys for your input.
it was of great help.
I used the one provided by the FAQ 183-3979 by jameslean
It worked fine.
Thanks
--King