Return Varchar from Custom T-SQL Functions [closed]

Question

i would like to return a simple varchar value from a function that i define as follow :

CREATE FUNCTION getCustomerType (@orderNumber INT)
    RETURNS VARCHAR(30)
AS
Return 
    (
    select custtype from Customer c 
        join order o on o.customerid =  c.customerid
    where o.orderNumber = @orderNumber 
 )
GO

select getCustType(1063609) 

supposed to return customer type of customer who made order#1063609 but fails

Answer 1

I would alter the syntax of your function to the following:

CREATE FUNCTION dbo.getCustomerType (@orderNumber INT)
    RETURNS VARCHAR(30)
AS
begin
  declare @custtype varchar(30)
  SET @custtype = (select custtype 
                      from dbo.Customer c 
                      join dbo.[order] o 
                        on o.customerid =  c.customerid
                      where o.orderNumber = @orderNumber)

  return @custtype
end 

Then when you call the function using the schema (dbo. below), you will use:

select dbo.getCustomerType(10636909)

See SQL Fiddle with Demo

The function can also be written as (thanks @MartinSmith):

CREATE FUNCTION getCustomerType (@orderNumber INT)
    RETURNS VARCHAR(30)
AS
begin
  return (select custtype 
                      from dbo.Customer c 
                      join dbo.[order] o 
                        on o.customerid =  c.customerid
                      where o.orderNumber = @orderNumber)
end 

See SQL Fiddle with Demo