Function Returns Data Type Error

I am writing my first function and it should be be a very simple one but
I am getting the error:
Server: Msg 245, Level 16, State 1, Procedure InvTypeUSR, Line 9
Syntax error converting the varchar value 'N' to a column of data type int.
Below is the funtion and then the sleect statement that causes the error
----
CREATE FUNCTION InvTypeOther (@.InvoiceID int)
RETURNS varchar(3)
AS
BEGIN
DECLARE @.Type varchar(3)
select @.Type=
(Case InvoiceType.Name
When 'IN' Then 'N'
Else 0
End)
FROM Invoice
INNER JOIN InvoiceType ON Invoice.InvoiceTypeID = InvoiceType.ID
WHERE (Invoice.ID = @.InvoiceID)
Return @.Type
END
--
Select dbo.InvTypeOther(ID) from Invoice where id = 2525Try to replace the line with this:
Else '0'
HTH, Jens Suessmeyer.|||Try to replace the line with this:
Else '0'
HTH, Jens Suessmeyer.|||your CASE expression is using type precedence to try to convert 'N' to
the 0 in the else.
not sure what it should be, but it shouldn't be an int. :)
either '0' or '' perhaps [or null]
Mike Harbinger wrote:
> I am writing my first function and it should be be a very simple one but
> I am getting the error:
> Server: Msg 245, Level 16, State 1, Procedure InvTypeUSR, Line 9
> Syntax error converting the varchar value 'N' to a column of data type int
.
> Below is the funtion and then the sleect statement that causes the error
> ----
> CREATE FUNCTION InvTypeOther (@.InvoiceID int)
> RETURNS varchar(3)
> AS
> BEGIN
> DECLARE @.Type varchar(3)
> select @.Type=
> (Case InvoiceType.Name
> When 'IN' Then 'N'
> Else 0
> End)
> FROM Invoice
> INNER JOIN InvoiceType ON Invoice.InvoiceTypeID = InvoiceType.ID
> WHERE (Invoice.ID = @.InvoiceID)
> Return @.Type
> END
> --
> Select dbo.InvTypeOther(ID) from Invoice where id = 2525
>|||That was it, thanks guys!
I am used to another programming language where numbers do not have to be
quoted when used in string variables.
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:OyGttQyEGHA.524@.TK2MSFTNGP09.phx.gbl...
>I am writing my first function and it should be be a very simple one but
> I am getting the error:
> Server: Msg 245, Level 16, State 1, Procedure InvTypeUSR, Line 9
> Syntax error converting the varchar value 'N' to a column of data type
> int.
> Below is the funtion and then the sleect statement that causes the error
> ----
> CREATE FUNCTION InvTypeOther (@.InvoiceID int)
> RETURNS varchar(3)
> AS
> BEGIN
> DECLARE @.Type varchar(3)
> select @.Type=
> (Case InvoiceType.Name
> When 'IN' Then 'N'
> Else 0
> End)
> FROM Invoice
> INNER JOIN InvoiceType ON Invoice.InvoiceTypeID = InvoiceType.ID
> WHERE (Invoice.ID = @.InvoiceID)
> Return @.Type
> END
> --
> Select dbo.InvTypeOther(ID) from Invoice where id = 2525
>