I have this SQL table Invoice. What I'm trying to achieve is to write a function that you send a primary key value to to get a particular invoice. Then I want to check a field of the Invoice 'Received' This is a bit field. If is 1 I want to return from the function 'Received'. If it is 0 I want to return 'Not Received'.

I had a go at it myself:

ALTER FUNCTION dbo.InvoiceStatus(@InvoiceId INT)
RETURNS VARCHAR
BEGIN
declare @Status varchar(100)

    SELECT * 
      FROM [Invoices_Prod].[dbo].[Invoice]
      Where InvoiceId = @InvoiceId

      ...

      How to check the received value?

      ...

    return @Status

END
GO

So I'm passing the InvoiceId in and getting a particular Invoice with the select statement. What I'm missing is how to checked the Received field and set the status value to be returned.

Can anyone help me with this?

• sql

Check out CASE.

Select [foo], [bar],
  case when [received] = 1 
    then 'Received'
    else 'Not received'
  end as Status
From [Invoice]
Where [InvoiceId] = @InvoiceId
....

I do, however, recommend to keep this kind of presentation-layer-logic out of your UDF's and even completely far away from the entire data-layer/RDBMS and thus handle the presentation where it's supposed to be done: in the presentation-layer/logic.