CODEWITHSUNDEEP
sqlsql-server-2008user-defined-functions
Dru
Dru

Reputation: 73

SQL 2008 user defined function not recognized

This is my first attempt at a user defined function so I am having some issues identifying what I am doing incorrectly. My goal is to create a function that will accept a part id as a paramater and then processes a series of if conditions that analyze the second character in the ID to determine product type based on standard naming conventions. I have made a few tweaks in the script, I have ran each version in master as well as the database. I have not received any failure errors when creating the function however each attempt at using it results in the following error. 'get_product_type' is not a recognized built-in function name.

Current version of this function is bellow.

create function get_product_type (@part nvarchar(30))
returns nvarchar(30)
with execute as caller
as 
begin
    DECLARE @product nvarchar(30);
    if SUBSTRING(@part,1,1) = 'A' 
        set @product = 'ELLIPTICAL';
    else if SUBSTRING(@part,1,1) = 'B' 
        set @product = 'F&D SPHERICAL';
    else if SUBSTRING(@part,1,1) = 'C' 
        set @product = 'F&D SPHERICAL';
    else if SUBSTRING(@part,1,1) = 'D' 
        set @product = 'HEMISPHERICAL';
    else if SUBSTRING(@part,1,1) = 'E' 
        set @product = 'CONICAL';
    else if SUBSTRING(@part,1,1) = 'F' 
        set @product = 'FLANGED ONLY';
    else if SUBSTRING(@part,1,1) = 'G' 
        set @product = 'DISHED ONLY';
    else if SUBSTRING(@part,1,1) = 'H' 
        set @product = 'TULIP BOWL';
    else if SUBSTRING(@part,1,1) = 'I' 
        set @product = 'TESTING/COUPONS/CHARPIES';
    else if SUBSTRING(@part,1,1) = 'J' 
        set @product = 'FLARED/DISHED';
    else if SUBSTRING(@part,1,1) = 'K' 
        set @product = 'HEAD BRACES';
    else if SUBSTRING(@part,1,1) = 'L' 
        set @product = 'MISCELLANEOUS';
    else if SUBSTRING(@part,1,1) = 'M' 
        set @product = 'HEAD PAD EXTENSIONS';
    else if SUBSTRING(@part,1,1) = 'N' 
        set @product = 'HEAD PADS';
    else if SUBSTRING(@part,1,1) = 'O' 
        set @product = 'MISCELLANEOUS';
    else if SUBSTRING(@part,1,1) = 'P' 
        set @product = 'HUBS';
    else if SUBSTRING(@part,1,1) = 'Q' 
        set @product = 'FLANGED/FLUED';
    else if SUBSTRING(@part,1,1) = 'R' 
        set @product = 'FLUED/PLATE';
    else if SUBSTRING(@part,1,1) = 'S' 
        set @product = 'SILL PAD';
    else if SUBSTRING(@part,1,1) = 'T' 
        set @product = 'TOOLING';
    else if SUBSTRING(@part,1,1) = 'U' 
        set @product = 'CYLENDAR REPAD';
    else if SUBSTRING(@part,1,1) = 'V' 
        set @product = 'ROLLING CANS';
    else if SUBSTRING(@part,1,1) = 'W' 
        set @product = 'HEAT TREAT';
    else if SUBSTRING(@part,1,1) = 'X' 
        set @product = 'SPHERE';
    else if SUBSTRING(@part,1,1) = 'Y' 
        set @product = 'BLASTING';
    else if SUBSTRING(@part,1,1) = 'Z' 
        set @product = 'MISCELLANEOUS';
    else set @product = 'MISCELLANEOUS';

    return @product
end

If the issue is in how I have been trying to call the function, all of my attempst have been,

get_product_type(cl.PART_ID),

If anyone can provide a push in the right direction it will be greatly appreciated.

Thanks everyone.

Upvotes: 0

Views: 66

Answers (1)

Hogan
Hogan

Reputation: 70513

I wouldn't do it like this -- it is very slow and you can use SQL to make this process fast using a left join

First define a table with two columns, a one character code and a description

It would look something like this 

 CREATE TABLE LOOKUP (
    CODE CHAR(1),
    DESC VARCHAR(100)
 )

Make the CODE field the primary key.

Then you can "call" your function like this:

 SELECT 
   COALESCE(LOOKUP.DESC,'MISCELLANEOUS') AS DESC
 FROM TABLENAMEHERE
 LEFT JOIN LOOKUP ON LOOKUP.CODE =  SUBSTRING(TABLENAMEHERE.PRODUCTCODE,1,1)

Upvotes: 1

Related Questions