I am trying to create a function that will take a table and return a VARCHAR(MAX).

The source table the function will be called against is not constant so I cannot hard-code a table name into the function. I know I can use a stored procedure with an output variable but I am trying to do this with a function so I can call it from within a SELECT (SELECT myfunc(...), ...).

So I figured that my function will need to accept a variable of table data. Based on what I am reading I have three two options:

  1. Use a table type parameter

    CREATE TYPE test AS TABLE ( Col1 VARCHAR(50) )
    DECLARE @t1 AS test
    INSERT INTO @t1 ...
    SELECT myFunct(@t1)
  2. Use a table variable

    DECLARE @t1 TABLE ( Col1 VARCHAR(50) )
    INSERT INTO @t1 ...
    SELECT myFunct(@t1)
  3. Use a temp table I thought I could use a temp table but, as far as I can tell, you can't send the temp table as a parameter to the function.

I have two issues and I'm not sure how to address them with either of the three two options:

  • within the function, I need to be able to get the column names of the passed in table
  • the input table may be populated with data using a normal SELECT (in my above examples) or an INSERT INTO ... EXEC stored_procedure

Any idea how to do what I need using either option (or any other option that may exist)?

  • What made you choose a function instead of a stored procedure to do this? – Erik Darling Dec 16 '17 at 15:37
  • @sp_BlitzErik I want to be able to call it in a select and get the output as a column value. – IMTheNachoMan Dec 16 '17 at 19:20
  • I'm not entirely sure I understand your goal here, in all honesty. Perhaps this might be easier if you supply some sample data and expected results sets. All we know at the moment is that you have "some table" and want to return a varchar(MAX). That doesn't give us much (anything) to go on. – Larnu Dec 17 '17 at 11:35
  • @Larnu I can, but that part is not important. The issue is that I need to create a stored procedure or function that can take in a variable that has table data and use it, like getting the column names. – IMTheNachoMan Dec 17 '17 at 20:28
  • We've used a table type parameter to pass a dataset from C# to SQL and it works fairly well. It has some performance problems if you pass too much data. I'm not sure why you can't use a temp table - the session should be the same so if you create it in the calling code, the proc or function should be able to just access it – Greg Dec 17 '17 at 21:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.