none
SQL: Having Issues with creating a user-defined function that returns a table variable RRS feed

  • Question

  • SQL Version:  SQL2008 R2

    I have created a user-defined function that requires no input parameters and returns @Table1 a table variable.  The code in the function runs fine, but I keep getting a message saying that there is incorrect near the beginning of the code, 'AS' .  Here is code which illustrates my approach:

    Sample Code:

    USE Viewpoint

    GO

    Create [dbo].[udFunction1]()

    AS

    BEGIN

    DECLARE @TABLE1 TABLE(ID int, Name varchar(50))

    --Values inserted into @TABLE1 here

    RETURN @TABLE1

    END

    Any ideas where I'm going wrong here.  I've used ud functions before and haven't had this issue??  Can I have a ud function with no parameters?

    ...bob sutor


    Bob Sutor

    Friday, November 1, 2013 10:06 PM

Answers

  • If you mean this to be a multistatement table valued function, then it must look like

    Create Function [dbo].[udFunction1]()
    Returns @TABLE1 TABLE(ID int, Name varchar(50))
    AS
    
    BEGIN
    
    
    --Values inserted into @TABLE1 here
    
    RETURN 
    
    END

    Tom

    • Marked as answer by ConstPM Saturday, November 2, 2013 12:04 AM
    Friday, November 1, 2013 10:38 PM

All replies

  • If you mean this to be a multistatement table valued function, then it must look like

    Create Function [dbo].[udFunction1]()
    Returns @TABLE1 TABLE(ID int, Name varchar(50))
    AS
    
    BEGIN
    
    
    --Values inserted into @TABLE1 here
    
    RETURN 
    
    END

    Tom

    • Marked as answer by ConstPM Saturday, November 2, 2013 12:04 AM
    Friday, November 1, 2013 10:38 PM
  • Thanks.  I'll give that a try.

    ...bob


    Bob Sutor

    Friday, November 1, 2013 10:56 PM
  • Your suggested syntax worked great.  Thanks

    Bob Sutor

    Saturday, November 2, 2013 12:05 AM
  • >> I have created a user-defined function that requires no input parameters and returns @Table1 a table variable. <<

    This is a really bad way to abuse SQL and tell the world that you have no idea how RDBMS works. Oh, putting that silly “uf_” as a prefix is called tibbling; the mix of data and meta data in a data element name. 

    While not wrong, VARCHAR(50) is a symptom of a lack of design or thought. There is no vague generic id, or vague generic name in RDBMS. We use logic and set theory so we follow the law of identity (to be is to be something in particular). 

    You want a table constant. You do this with declarative code, not procedures!! 

    CREATE VIEW Foobar (vague_generic_id, vague_generic_name)
    AS
    SELECT X.*
     FROM (VALUES (CAST (0 AS INTEGER), CAST ('' AS VARCHAR(50))),
                   (..), (..) ) AS X (vague_generic_id, vague_generic_name)

    >> Any ideas where I'm going wrong here. <<

    Yes, your whole approach to SQL is fundamentally wrong. Bob, you are not writing SQL yet; this is bad BASIC or COBOL using T-SQL.

    --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

    Sunday, November 3, 2013 11:25 PM