pass table param

• MinhL7

  Mr or Mrs. 500

  Points: 529

  More actions

  November 26, 2017 at 8:58 pm

  #326514

  How do you pass a table like this?
  ;WITH TestTbl AS
(
    SELECT * FROM myTable
)

  CREATE FUNCTION Ex( TestTbl TableType READONLY)
RETURNS VARCHAR(MAX)
AS
BEGIN
  DECLARE @name VARCHAR(MAX)

    SELECT TOP 1 @name = Field1 FROM TestTbl
  RETURN @name
END

• Thom A

  SSC Guru

  Points: 99126

  More actions

  November 27, 2017 at 1:54 am

  #1969472

  You'll need to create a user defined table type, then DECLARE and INSERT the values into the and then pass that to your Function.

  USE Sandbox;

  GO

  CREATE TYPE TestTbl AS TABLE (ID int);

  GO

  CREATE FUNCTION TestFunction (@Table TestTbl READONLY)

  RETURNS INT

  AS BEGIN

    DECLARE @Lowest int;

   

    SELECT TOP 1 @Lowest = ID

    FROM @Table
  ORDER BY ID;

    RETURN @Lowest;

  END

  GO

  DECLARE @test-2 TestTbl;

  INSERT INTO @test-2

  VALUES (1),(2),(3),(4),(5),(6);

  SELECT dbo.TestFunction(@Test) AS Lowest;

  GO

  DROP FUNCTION TestFunction

  DROP TYPE testTbl;

  A better question, however, would be why are you using a scalar function to achieve this? They are not the fastest thing. Also, not sure what you're planning to achieve with a TOP 1 with an ORDER BY clause. Unless you're planning for the return result to always be random.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• MinhL7

  Mr or Mrs. 500

  Points: 529

  More actions

  November 27, 2017 at 3:30 am

  #1969481

  This is purely hypothetical to see if it's possible to pass a table to a function created by the statement 
  ;WITH
  Is it not possible?

• Thom A

  SSC Guru

  Points: 99126

  More actions

  November 27, 2017 at 3:38 am

  #1969483

  MinhL7 - Monday, November 27, 2017 3:30 AM

  This is purely hypothetical to see if it's possible to pass a table to a function created by the statement 
  ;WITH
  Is it not possible?

  WITH is a Common Table Expression. It does not CREATE a table, it's an Expression, much like CASE is. You can't pass an expression to a Function. Your Syntax, above, would be like doing:
  CASE @ID WHEN 1 THEN TRUE ELSE FALSE END AS Boolean

  CREATE FUNCTION Example (Boolean case) ....
  That syntax is just plain wrong. 🙂

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• MinhL7

  Mr or Mrs. 500

  Points: 529

  More actions

  November 27, 2017 at 4:35 am

  #1969489

  Ok thank you