Function Returns Data Type Error

7 Jan 2006 12:48 AM

Mike Harbinger

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

7 Jan 2006 12:59 AM

Jens

Try to replace the line with this:

Else '0'

HTH, Jens Suessmeyer.

7 Jan 2006 12:59 AM

Jens

Try to replace the line with this:

Else '0'

HTH, Jens Suessmeyer.

7 Jan 2006 1:15 AM

Trey Walpole

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:
Show quote

> 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
>
>

7 Jan 2006 8:29 PM

Mike Harbinger

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.

Show quote

"Mike Harbinger" <Mi***@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
>