In Operator in Update Statement

[Modica82]

Hi all,

I am trying to use the In Operator in an update statement, but its not working.

CREATE PROCEDURE Time_ApproveTime
(
@TimeToApprove As VarChar(500)
)

AS

UPDATE CT_Time
SET Approved = -1
WHERE CONVERT(VARCHAR, THID) In (@TimeToApprove)

My problem is i am pulling in a string of integers if thats makes any sense. i.e. 85, 86, 87 etc. THID Is an integer, can someone please help me with this??

Rob

 

[BSman]

Since THID is an integer, it would make more sense to convert TimeToApprove to an integer. Try something like this (the function might be cint, val, or something else, I'm not sure at the moment):

WHERE THID = int(@TimeToApprove)

 

[dbomrrsm]

what error message do you receive when you try to run the above ?

[blue]DBomrrsm[/blue]

 

[SQLSister]

Passing a list of values to a Stored Procedure faq183-3979
Passing a list of values to a Stored Procedure (Part II) faq183-5207


Questions about posting. See faq183-874

 

[dreameR25]

You at that you are pulling in a string of integers, is the string comma delimited?

If so then you can use Dynamic SQL. Something like this

declare @sql varchar(2000)

set @sql  = 'UPDATE CT_Time SET Approved = -1 WHERE CONVERT(VARCHAR, THID) In (' + @TimeToApprove + ')'

exec (@sql)

 

[Modica82]

Hi All,

Thanks for responses. Thanks SQL Sister i used the split function and it works great, and also apologies for not looking at the FAQ's.

Rob