Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2010
    Posts
    75

    Unanswered: Can a Table Valued Function also return a flag?

    This would save me a lot of extra programming, if a table-valued function could return some kind of integer flag (or something) as well as the table its supposed to return.

    We already have a table-valued-function thats used everywhere, and suddenly we need a flag to indicate something about the type of data thats in the table.

    I tried passing an OUT parameter to the function but either my syntax was wrong, or thats just not allowed.

    Help please

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You could add a new column with the flag, I suppose. That may devolve into some...interesting problems. How would this be used? Did you want to check the flag, before running the table valued function?

  3. #3
    Join Date
    Feb 2010
    Posts
    75
    a new column flag would be ok, except that if the function returnes a table of 5000 rows, then the flag is repeated 5000 times which seems kind of silly. Also, I cant be certain that changing the schema of the returned table wont break anything.

    the table returns data from a fairly intense calculation. There are different paths that the calculation can take to generate the data, and now we need to know what path the calculation took.

    Definetly not the best way to do this I know, but the function is already being used in MANY different places so im looking for the easiest way to add this new flag. no time to re-design the entire thing

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Adding an extra column "Z" to an existing table will never break anything, ... unless "SELECT * FROM ..." was used in production code. All code that uses "SELECT A, B, C FROM ... " can not be affected by the presence of the new column Z.

    Dropping an existing column is of coarse a totally different matter.
    a new column flag would be ok, except that if the function returnes a table of 5000 rows, then the flag is repeated 5000 times....

    the table returns data from a fairly intense calculation. There are different paths that the calculation can take to generate the data, and now we need to know what path the calculation took.
    Do you mean that ALL those records will be calculated in exactly the same way, or is it possible that 1000 records are calculated by following execution path A, 500 by path B, 1500 with path C, ....?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Feb 2010
    Posts
    75
    Ok I'm assuming that SQL Server cant do exactly what I'm asking. So I need a workaround that accomplishes the same thing, and hopefully something easy that doesnt change too much existing code.


    Adding an extra column "Z" to an existing table will never break anything, ... unless "SELECT * FROM ..." was used in production code. All code that uses "SELECT A, B, C FROM ... " can not be affected by the presence of the new column Z.
    Yes I know. Personally I dont use select *, but its a very big system and there are a lot of developers working on it, and I dont know how everyone else is making calls to the DB or what theyre doing. I have to assume that adding a new column would break things. Also as I mentioned it doesnt make sense to have the flag appear once for each row in the returned table.


    Do you mean that ALL those records will be calculated in exactly the same way, or is it possible that 1000 records are calculated by following execution path A, 500 by path B, 1500 with path C, ....?
    Nevermind what I said about different execution paths. Thats not quite true, I was just trying to simplify things so I wouldnt have to give a big explanation of what the function does. Its not important. All you need to know is this:

    I currently have a SQL table valued function that returns a table. I now need to return that table PLUS one other piece of information. It could be an integer, a string, or even a boolean, I dont care. It seems that Table valued functions cant have OUTPUT parameters, which really sucks because that would have been ideal. So I'm looking for a workaround that is going to have no effects on the existing code.

  6. #6
    Join Date
    Jun 2005
    Posts
    319
    can you create a #temp table with 1 column, call your UDF and within the UDF add 1 row to the aforementioned #temp table? then outside of the UDF do what you please with the value in the #temp table? You could check for existence within the UDF (in case some foreign piece of code you have not found calls the UDF) and the UDF could create the #temp table (which would not be in the scope of the calling piece of code).

    Not the prettiest solution but it should solve your problem.

  7. #7
    Join Date
    Feb 2010
    Posts
    75
    Thanks Gagnon!

    I had thought of that and I wanted to avoid it because it seems so hack-y. But its at least good to know that someone else came up with the same thing... simply because it looks like there doesnt seem to be a better alternative

Posting Permissions

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