Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Table lookup (SELECT) in function?

Answered Table lookup (SELECT) in function?

  • Monday, March 18, 2013 10:46 PM
     
     
    Sign In to Vote
    0

    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

     

All Replies

  • Monday, March 18, 2013 11:23 PM
     
     Answered
    Sign In to Vote
    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, esquel@sommarskog.se
     
  • Monday, March 18, 2013 11:35 PM
     
     
    Sign In to Vote
    0

    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

     
  • Tuesday, March 19, 2013 3:12 AM
     
     
    Sign In to Vote
    0
    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

     
  • Tuesday, March 19, 2013 8:34 AM
     
     
    Sign In to Vote
    1

    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, esquel@sommarskog.se
     
  • Tuesday, March 19, 2013 5:49 PM
     
     
    Sign In to Vote
    0

    Thanks, much. This resolved the issues.

    -Jnana

    Jnana Sivananda

     
  • Tuesday, March 19, 2013 5:49 PM
     
     
    Sign In to Vote
    0

    Thanks, Tom.

    -Jnana

    Jnana Sivananda

     
  • Tuesday, March 19, 2013 5:55 PM
     
     
    Sign In to Vote
    0

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

    -Jnana

    Jnana Sivananda