Table lookup (SELECT) in function?

Question

I'm attempting to create a function that takes several variables and uses them in a query to look up a value keyed on those variables and return the looked value. If more than one row would be returned (determined by doing a count(*)), '1 to Many Err' is returned, If not found, 'OTHER' is to be returned.

Issues- Error messages:

1) CREATE FUNCTION must be the only statement in the batch.

2) Invalid Object Name DP.tbl_NSW_SUB_CAT

3) Select statements included within a function cannot return data to a client

Is this the wrong methodology to get the results I'm seeking?

If not, how do I resolve these errors?

Thank you.

Jnana

-------------------------------------------------

USE [DPIMDW]
GO
/****** Object:  UserDefinedFunction [DP].[fn_NSW_SUB_CAT]    Script Date: 3/18/2013 1:16:50 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE Function [DP].[fn_NSW_SUB_CAT]
(@Prod_Engn_Cd nvarchar(5), @Glbl_Cat_Sum_Cd nvarchar(5), @Reg_Long_Rng_Sesn_Mktg_Clsfcn_Desc_2 nvarchar(5))
Returns nvarchar(5)
AS
BEGIN

Declare @NSW_SUB_CAT nvarchar(15)

IF @Prod_Engn_Cd = '10'      
   AND @Glbl_Cat_Sum_Cd = '1006'      
   BEGIN
     IF  (SELECT count(*) from DP.tbl_NSW_SUB_CAT nsc
           WHERE nsc.Prod_Engn_Cd = @Prod_Engn_Cd
             AND nsc.Glbl_Cat_Sum_Cd = @Glbl_Cat_Sum_Cd
                    AND nsc.Reg_Long_Rng_Sesn_Mktg_Clsfcn_Desc_2 = @Reg_Long_Rng_Sesn_Mktg_Clsfcn_Desc_2
                 ) > 1
                      SET @NSW_SUB_CAT = '1 to Many Err'
            ELSE SET @NSW_SUB_CAT= ISNULL (SELECT nsc.NSW_SUB_CAT from DP.tbl_NSW_SUB_CAT nsc
                            WHERE nsc.Prod_Engn_Cd = @Prod_Engn_Cd
                                AND nsc.Glbl_Cat_Sum_Cd = @Glbl_Cat_Sum_Cd
                                               AND nsc.Reg_Long_Rng_Sesn_Mktg_Clsfcn_Desc_2 = @Reg_Long_Rng_Sesn_Mktg_Clsfcn_Desc_2),'OTHER')
     END

ELSE @NSW_SUB_CAT = 'N/A'

END

RETURN @NSW_SUB_CAT
End

---

Jnana Sivananda

Answer 1

I cannot obviously not say why you get "Invalid Object Name DP.tbl_NSW_SUB_CAT", or raher I don't know you what you think is wrong with message. But a hint: If SQL Server says that a table exists, it's probably right. Computers are good at such things. Maybe you are in the wrong database.

When I try the code you posted, I get these errors:

Msg 156, Level 15, State 1, Procedure fn_NSW_SUB_CAT, Line 18
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Procedure fn_NSW_SUB_CAT, Line 21
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Procedure fn_NSW_SUB_CAT, Line 29
Incorrect syntax near 'End'.

There is a left paren missing after "isnull(" - the subquery must be in parentheses. When I fix this, I get:

Msg 102, Level 15, State 1, Procedure fn_NSW_SUB_CAT, Line 24
Incorrect syntax near '@NSW_SUB_CAT'.
Msg 102, Level 15, State 1, Procedure fn_NSW_SUB_CAT, Line 29
Incorrect syntax near 'End'.

Which is dues to this line:

ELSE @NSW_SUB_CAT = 'N/A'

Which is not legal SQL; you need SET or SELECT here. Fixing that I get:

Msg 156, Level 15, State 1, Procedure fn_NSW_SUB_CAT, Line 28
Incorrect syntax near the keyword 'RETURN'.
Msg 102, Level 15, State 1, Procedure fn_NSW_SUB_CAT, Line 29
Incorrect syntax near 'End'.

Which makes sense, the END that follows the ELSE has no match. So let's remove that. Now I get:

Msg 2760, Level 16, State 1, Procedure fn_NSW_SUB_CAT, Line 28
The specified schema name "DP" either does not exist or you do not have permission to use it.

Which is not surprising at all, since I don't have that schema at all.

So one of your problems is that you are giving up too soon. Getting compilation errors is just part of the faily chores in the programmer's trade.

But there is also another problem, and that is the idea of the function itself. You should avoid scalar functions with data access, because they can often cause performance problems, since SQL Server never lines them, but they get called for every row.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

In addition to Erland's comments, your function header says the function will return varchar(5), but you declare @NSW_SUB_CAT as nvarchar(15).  That is not the cause of any of your current compile errors, but it will cause you problems when you begin using the function.  If you take the branch that puts "1 to Many Err" into @NSW_SUB_CAT, the return statement will truncate that to 5 characters and return "1 to".

Tom

Answer 3

We hate UDF's. They do not optimize, they do not port, and they are bitch to maintain because of side effects. When we write them, we avoid all local variable – local variables are procedural coding. Unfortunately, T-SQL function syntax is based on 1970's hardware and assembly language. The result has to be loaded into a local variable, then returned to mimic the registers in very old IBM hardware. 

Where is the DDL? 

You also do not know ISO-11179 rules and are “tibbling” --that is the design flaw of putting “tbl-” on table names, or “fn_” on function names. It mixes data and meta data. We also truly hate if-then-else and while control flow logic; SQL is a set-oriented declarative language. 

Without specs, my thought was a simple query that return the search value(s) and the count for multiple matches. 

CREATE PROCEDURE Nsw_Sub_Cat
(@in_prod_engn_code NVARCHAR(5), 
 @in_glbl_cat_sum_code NVARCHAR(5), 
 @in_reg_long_rng_sesn_mktg_clsfcn_desc_2 NVARCHAR(5))

SELECT NSC.nsw_sub_cat,
       CASE WHEN @in_prod_engn_code = '10' 
             AND @in_glbl_cat_sum_code = '1006'
            THEN 'okay' ELSE 'other' END AS weird_flg,
       COUNT(*) OVER() AS search_cnt
  FROM NSW_Sub_Cat AS NSC
 WHERE NSC.prod_engn_code = @in_prod_engn_code
   AND NSC.glbl_cat_sum_code = @in_glbl_cat_sum_code
   AND NSC.reg_long_rng_sesn_mktg_clsfcn_desc_2 
       = @in_reg_long_rng_sesn_mktg_clsfcn_desc_2; 

Between the count(*) and the weird flag, the presentation layer should be able display the messages you embedded in the database layer. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 4

We hate UDF's. They do not optimize, they do not port, and they are bitch to maintain because of side effects.

"do not optimize" is true for scalar function as well as multi-statement table functions, but not inline table function. No one here but you cares whether they port. The last part is completely untrue for functions in SQL Server; Microsoft is very restrictive with what they permit in user-defined tunctions.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 5

Thanks, much. This resolved the issues.

-Jnana

---

Jnana Sivananda

Answer 6

Thanks, Tom.

-Jnana

---

Jnana Sivananda

Answer 7

Thanks Celko. This is certainly elegent and fat-free. I appreciate the updated style.

-Jnana

---

Jnana Sivananda