Pass table as parameter into sql server UDF

by

You can, however no any table. From documentation:

For Transact-SQL functions, all data
types, including CLR user-defined
types and user-defined table types,
are allowed except the timestamp data
type.

You can use user-defined table types.

Example of user-defined table type:

CREATE TYPE TableType 
AS TABLE (LocationName VARCHAR(50))
GO 

DECLARE @myTable TableType
INSERT INTO @myTable(LocationName) VALUES('aaa')
SELECT * FROM @myTable

So what you can do is to define your table type, for example TableType and define the function which takes the parameter of this type. An example function:

CREATE FUNCTION Example( @TableName TableType READONLY)
RETURNS VARCHAR(50)
AS
BEGIN
    DECLARE @name VARCHAR(50)

    SELECT TOP 1 @name = LocationName FROM @TableName
    RETURN @name
END

The parameter has to be READONLY. And example usage:

DECLARE @myTable TableType
INSERT INTO @myTable(LocationName) VALUES('aaa')
SELECT * FROM @myTable

SELECT dbo.Example(@myTable)

Depending on what you want achieve you can modify this code.

EDIT:
If you have a data in a table you may create a variable:

DECLARE @myTable TableType

And take data from your table to the variable

INSERT INTO @myTable(field_name)
SELECT field_name_2 FROM my_other_table

More Related Contents:

  1. Execute Stored Procedure from a Function
  2. Is it possible to write single query on two table which are not connected to each other?
  3. SQL update query using joins
  4. How to update two tables in one statement in SQL Server 2005?
  5. Referring to a Column Alias in a WHERE Clause
  6. INSERT statement conflicted with the FOREIGN KEY constraint – SQL Server
  7. SQL query to select dates between two dates
  8. Create a date from day month and year with T-SQL
  9. Selecting COUNT(*) with DISTINCT
  10. Querying data by joining two tables in two database on different servers
  11. Are there any disadvantages to always using nvarchar(MAX)?
  12. SQL – Subtracting a depleting value from rows
  13. Turn off constraints temporarily (MS SQL)
  14. Get the week start date and week end date from week number
  15. What’s the best way to select the minimum value from several columns?
  16. How to insert a blob into a database using sql server management studio
  17. SQL Server 2005 ROW_NUMBER() without ORDER BY
  18. Can an INNER JOIN offer better performance than EXISTS
  19. How to check which locks are held on a table
  20. Execution sequence of Group By, Having and Where clause in SQL Server?
  21. How to find sum of multiple columns in a table in SQL Server 2005?
  22. SQL Script to alter ALL Foreign Keys to add ON DELETE CASCADE
  23. Is it possible to execute a text file from SQL query?
  24. Query times out from web app but runs fine from management studio
  25. Cumulative Total in MS Sql server [duplicate]
  26. SQL query to group by day
  27. Export table data from one SQL Server to another
  28. SQL can I have a “conditionally unique” constraint on a table?
  29. Memory effective way to read BLOB data in C#/SQL 2005
  30. SQL, How to Concatenate results?

Leave a Comment