Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2003
    Posts
    3

    Unanswered: Inline Table-Valued Functions

    I have a stored proc that takes parameters from an ASP page and using a cursors, creates a dynamic crosstab query. I would like to turn this dynamic crosstab query into a function that returns a table. I've searched the internet and found numerous examples, but I keep getting syntax errors. I build the SELECT statement as a string.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Send the code, we'll figure it out

  3. #3
    Join Date
    Aug 2003
    Posts
    3

    Inline Table-Valued functions

    Originally posted by rdjabarov
    Send the code, we'll figure it out


    see attached
    Attached Files Attached Files

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Your CREATE FUNCTION statement looks like Inline Function which should start with RETURN SELECT ... after AS. But the contents of the function ... You realize you can't use EXECUTE in there, right? But, you can use OPENROWSET. Maybe, if you write a stored procedure that does the transformation and then do SELECT * from OPENROWSET(...) within your function?

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    adavis,

    I don't think your inline function can take a string variable as a select statement. Have you seen examples of this on the internet? I'd be interested in seeing them if you have. I can't find any in SQL Server documentation.

    This is from Books On Line:
    The RETURN clause contains a single SELECT statement in parentheses. The result set of the SELECT statement forms the table returned by the function. The SELECT statement used in an inline function is subject to the same restrictions as SELECT statements used in views.

    If you are trying to pass a variable to the return statement, it has no way to verify that it meets the restrictions or anticipate the result format.

    If you have a defined set of column headers, then you should ultimately be able to find a way to get the results you want. Otherwise you are limited to what you can jam into a single pre-defined select statement.

    blindman

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Actually, selecting from OPENROWSET will allow adavis to successfully get the desired result, WITHOUT any mentioned limitations.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Example please?

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    create function dbo.fn_CrossTab(@Tbl VARCHAR (255),@Row VARCHAR(255),@Col VARCHAR(255),@Data VARCHAR (255),@Agg VARCHAR(255)) returns table as
    return (select * from openrowset('sqloledb', 'server_name';'uid';'pwd', 'exec database_name.dbo.sp_CrossTab')
    )

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I see the function take five variables and do nothing with them. How do you get those to the stored procedure?

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Good eye, bm! Well, for this we will probably need to use a parameter table that will acquire the values of the passed parameters. This way we will not have to do anything with parameters passed other than storing them to the parameter table. We also need to convert the function from inline to table-valued to accomplish this.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •