Post reply

Function error

  • April 18, 2012 at 1:54 am

    #267726

    Dear All,

    I have a function that accepts a string as it's parameter as following script.

    Create FUNCTION [dbo].[GetQty]

    (

    @ItemCodes as varchar(15)

    )

    RETURNS Money

    AS

    Begin

    Declare @Qty Money

    SELECT @Qty = SUM(item_qty )

    FROM TRANS where item_code in ( @ItemCodes )

    return @Qty

    End

    when i try to run it

    select dbo.GetQty('141,142')

    i get error

    Msg 245, Level 16, State 1, Line 1

    Conversion failed when converting the varchar value '141,142' to data type int.

    Thanks in advance

    Nader

  • April 18, 2012 at 4:51 am

    #1475252

    Because IN does not work the way you think it does.

    Column IN (@Var1) is the same as Column = @Var1, and the value that you have for your variable cannot be converted to an int. To have a matuh to a range of variables, it would have to be

    Column IN (@Var1,@Car2,@Var3, ...)

    You need either dynamic SQL or a split function to do what you are trying.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 18, 2012 at 5:07 am

    #1475254

    Thanks Gail for your reply i appreciate it.

    Nader

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply