Pass table as parameter into sql server UDF

Question

I d like to pass a table as a parameter into a scaler UDF    I d also prefer to restrict the parameter to tables with only one column   optional   Is this possible   EDIT  I don t want to pass a table name  I d like to pass the table of data  as a reference I presume   EDIT  I would want my Scaler UDF to basically take a table of values and return a CSV list of the rows   IE  col1    My First Value     My Second Value       My nth Value    would return   My First Value  My Second Value     My nth Value    I d like to do some filtering on the table though  IE ensuring that there are no nulls and to ensure there are no duplicates  I was expecting something along the lines of   SELECT dbo MyFunction SELECT DISTINCT myDate FROM myTable WHERE myDate IS NOT NULL

User · Answer

create table Project  ProjectId int  Description varchar 50        insert into Project values  1   Chase tail  change directions        insert into Project values  2   ping-pong ball in clothes dryer         create table ProjectResource  ProjectId int  ResourceId int  Name varchar 15        insert into ProjectResource values  1  1   Adam        insert into ProjectResource values  1  2   Kerry        insert into ProjectResource values  1  3   Tom        insert into ProjectResource values  2  4   David        insert into ProjectResource values  2  5   Jeff          SELECT            SELECT Name       AS  text            FROM ProjectResource pr         WHERE pr ProjectId   p ProjectId         FOR XML PATH            AS ResourceList      FROM Project p  -- ProjectId    Description                        ResourceList -- 1            Chase tail  change directions      Adam Kerry Tom  -- 2            ping-pong ball in clothes dryer    David Jeff

User · Answer

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

User · Answer

Step 1  Create a Type as Table with name TableType that will accept a table having one varchar column  create type TableType as table   value  varchar 100  null    Step 2  Create a function that will accept above declared TableType as Table-Valued Parameter and String Value as Separator  create function dbo fn get string with delimeter   table TableType readonly  Separator varchar 5   returns varchar 500  As begin      declare  return varchar 500       set  return   stuff  select  Separator   value from  table for xml path      1 1          return  return  end   Step 3  Pass table with one varchar column to the user-defined type TableType and     as separator in the function  select dbo fn get string with delimeter  tab

User · Answer

you can do something like this     CREATE USER DEFINED TABLE TYPE     CREATE TYPE StateMaster AS TABLE    StateCode VARCHAR 2    StateDescp VARCHAR 250    GO     CREATE  FUNCTION WHICH TAKES TABLE AS A PARAMETER      CREATE FUNCTION TableValuedParameterExample  TmpTable StateMaster READONLY  RETURNS  VARCHAR 250  AS BEGIN  DECLARE  StateDescp VARCHAR 250   SELECT  StateDescp   StateDescp FROM  TmpTable  RETURN  StateDescp END GO     CREATE  STORED PROCEDURE WHICH TAKES TABLE AS A PARAMETER       CREATE PROCEDURE TableValuedParameterExample SP    TmpTable StateMaster READONLY   AS BEGIN  INSERT INTO StateMst    SELECT   FROM  TmpTable END GO   BEGIN    DECLARE VARIABLE OF TABLE USER DEFINED TYPE    DECLARE  MyTable StateMaster     INSERT DATA INTO TABLE TYPE    INSERT INTO  MyTable VALUES  11   AndhraPradesh   INSERT INTO  MyTable VALUES  12   Assam       EXECUTE STORED PROCEDURE    EXEC TableValuedParameterExample SP  MyTable GO   For more details check this link  http   sailajareddy-technical blogspot in 2012 09 passing-table-valued-parameter-to html

User · Answer

PASSING TABLE AS PARAMETER IN STORED PROCEDURE  Step 1   CREATE TABLE  DBO  T EMPLOYEES DETAILS   Id int  Name nvarchar 50   Gender nvarchar 10   Salary int    Step 2   CREATE TYPE EmpInsertType AS TABLE   Id int  Name nvarchar 50   Gender nvarchar 10   Salary int    Step 3       Must add READONLY keyword at end of the variable      CREATE PROC PRC EmpInsertType  EmployeeInsertType EmpInsertType READONLY AS BEGIN     INSERT INTO  DBO  T EMPLOYEES DETAILS     SELECT   FROM  EmployeeInsertType END  Step 4   DECLARE  EmployeeInsertType EmpInsertType  INSERT INTO  EmployeeInsertType VALUES 1  John   Male  50000  INSERT INTO  EmployeeInsertType VALUES 2  Praveen   Male  60000  INSERT INTO  EmployeeInsertType VALUES 3  Chitra   Female  45000  INSERT INTO  EmployeeInsertType VALUES 4  Mathy   Female  6600  INSERT INTO  EmployeeInsertType VALUES 5  Sam   Male  50000   EXEC PRC EmpInsertType  EmployeeInsertType                                           SELECT   FROM T EMPLOYEES DETAILS  OUTPUT  1   John    Male    50000  2   Praveen Male    60000  3   Chitra  Female  45000  4   Mathy   Female  6600  5   Sam Male    50000

User · Answer

The following will enable you to quickly remove the duplicate null values  and return only the valid one as list   CREATE TABLE DuplicateTable  Col1 INT  INSERT INTO DuplicateTable SELECT 8 UNION ALL SELECT 1--duplicate UNION ALL SELECT 2 --duplicate UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION  SELECT NULL GO  WITH CTE  COl1 DuplicateCount  AS   SELECT COl1  ROW NUMBER   OVER PARTITION BY COl1 ORDER BY Col1  AS DuplicateCount FROM DuplicateTable WHERE  col1 IS NOT NULL     SELECT COl1 FROM CTE WHERE DuplicateCount  1 GO   CTE are valid in SQL 2005   you could then store the values in a temp table and use it with your function

User · Answer

Unfortunately  there is no simple way in SQL Server 2005  Lukasz  answer is correct for SQL Server 2008 though and the feature is long overdue  Any solution would involve temp tables  or passing in xml CSV and parsing in the UDF  Example  change to xml  parse in udf  DECLARE  psuedotable xml  SELECT      psuedotable       FROM         FOR XML      SELECT     dbo MyUDF   psuedotable    What do you want to do in the bigger picture though  There may be another way to do this     Edit  Why not pass in the query as a string and use a stored proc with output parameter  Note  this is an untested bit of code  and you d need to think about SQL injection etc  However  it also satisfies your  one column  requirement and should help you along  CREATE PROC dbo ToCSV        MyQuery varchar 2000        CSVOut varchar max    AS SET NOCOUNT ON  CREATE TABLE  foo  bar varchar max    INSERT  foo EXEC   MyQuery   SELECT      CSVOut   SUBSTRING buzz  2  2000000000  FROM           SELECT          bar -- maybe CAST bar AS varchar max         FROM           foo     FOR XML PATH             fizz buzz  GO

User · Answer

Cutting to the bottom line  you want a query like SELECT x FROM y to be passed into a function that returns the values as a comma separated string   As has already been explained you can do this by creating a table type and passing a UDT into the function  but this needs a multi-line statement    You can pass XML around without declaring a typed table  but this seems to need a xml variable which is still a multi-line statement i e    DECLARE  MyXML XML    SELECT x FROM y FOR XML RAW   SELECT Dbo CreateCSV  MyXml     The  FOR XML RAW  makes the SQL give you it s result set as some xml   But you can bypass the variable using Cast     AS XML   Then it s just a matter of some XQuery and a little concatenation trick   CREATE FUNCTION CreateCSV   MyXML XML   RETURNS VARCHAR MAX  BEGIN     DECLARE  listStr VARCHAR MAX       SELECT               listStr                    COALESCE  listStr                             c value   Value 1    nvarchar max             FROM  myxml nodes   row   as T c      RETURN  listStr END GO  -- And you call it like this  SELECT Dbo CreateCSV CAST      SELECT x FROM y    FOR XML RAW  AS XML     -- Or a working example SELECT Dbo CreateCSV CAST           SELECT DISTINCT number AS Value          FROM master  spt values          WHERE type    P               AND number  lt   20     FOR XML RAW  AS XML      As long as you use FOR XML RAW all you need do is alias the column you want as Value  as this is hard coded in the function

User · Answer

I ve been dealing with a very similar problem and have been able to achieve what I was looking for  even though I m using SQL Server 2000  I know it is an old question  but think its valid to post here the solution since there should be others like me that use old versions and still need help   Here s the trick  SQL Server won t accept passing a table to a UDF  nor you can pass a T-SQL query so the function creates a temp table or even calls a stored procedure to do that  So  instead  I ve created a reserved table  which I called xtList  This will hold the list of values  1 column  as needed  to work with   CREATE TABLE  dbo   xtList        List   varchar  1000  NULL   ON  PRIMARY    Then  a stored procedure to populate the list  This is not strictly necessary  but I think is very usefull and best practice   --                                               -- Author       Zark Khullah -- Create date  20 06 2014 --                                               CREATE PROCEDURE  dbo   xpCreateList       ListQuery varchar 2000  AS BEGIN     SET NOCOUNT ON     DELETE FROM xtList    INSERT INTO xtList     EXEC  ListQuery  END   Now  just deal with the list in any way you want  using the xtList  You can use in a procedure  for executing several T-SQL commands   scalar functions  for retrieving several strings  or multi-statement table-valued functions  retrieves the strings but like it was inside a table  1 string per row   For any of that  you ll need cursors   DECLARE  Item varchar 100  DECLARE cList CURSOR DYNAMIC   FOR  SELECT   FROM xtList WHERE List is not NULL    OPEN cList  FETCH FIRST FROM cList INTO  Item WHILE   FETCH STATUS   0 BEGIN     lt  lt  desired action with values  gt  gt   FETCH NEXT FROM cList INTO  Item END CLOSE cList DEALLOCATE cList   The desired action would be as follows  depending on which type of object created   Stored procedures  --                                               -- Author       Zark Khullah -- Create date  20 06 2014 --                                               CREATE PROCEDURE  dbo   xpProcreateExec         Cmd varchar 8000        ReplaceWith varchar 1000    AS BEGIN   DECLARE  Query varchar 8000      lt  lt  cursor start  gt  gt      SET  Query   REPLACE  Cmd  ReplaceWith  Item      EXEC  Query     lt  lt  cursor end  gt  gt  END     EXAMPLES     List A B C     Query    SELECT x FROM table      with EXEC xpProcreateExec Query  x   turns into   SELECT A FROM table   SELECT B FROM table   SELECT C FROM table    Cmd    EXEC procedure   arg    --whatchout for wrong quotes  since it executes as dynamic SQL     with EXEC xpProcreateExec Cmd  arg   turns into   EXEC procedure  A    EXEC procedure  B    EXEC procedure  C        Scalar functions  --                                               -- Author       Zark Khullah -- Create date  20 06 2014 --                                               CREATE FUNCTION  dbo   xfProcreateStr         OriginalText varchar 8000        ReplaceWith varchar 1000    RETURNS varchar 8000  AS BEGIN     DECLARE  Result varchar 8000     SET  Result         lt  lt  cursor start  gt  gt      SET  Result    Result   REPLACE  OriginalText  ReplaceWith  Item    char 13    char 10     lt  lt  cursor end  gt  gt       RETURN  Result END     EXAMPLE     List A B C     Text    Access provided for user x      with  SELECT dbo xfProcreateStr Text  x    turns into    Access provided for user A   Access provided for user B   Access provided for user C        Multi-statement table-valued functions  --                                               -- Author       Zark Khullah -- Create date  20 06 2014 --                                               CREATE FUNCTION  dbo   xfProcreateInRows         OriginalText varchar 8000        ReplaceWith varchar 1000    RETURNS   Texts TABLE        Text varchar 2000    AS BEGIN    lt  lt  cursor start  gt  gt        INSERT INTO  Texts VALUES REPLACE  OriginalText  ReplaceWith  Item      lt  lt  cursor end  gt  gt  END     EXAMPLE     List A B C     Text    Access provided for user x      with  SELECT   FROM dbo xfProcreateInRow Text  x    returns rows    Access provided for user A     Access provided for user B     Access provided for user C

User · Answer

To obtain the column count on a table  use this   select count id  from syscolumns where id   object id  tablename     and to pass a table to a function  try XML as show here   create function dbo ReadXml   xmlMatrix xml  returns table as return   select t value     Salary    integer   as Salary  t value     Age    integer   as Age from  xmlMatrix nodes    row   x t    go  declare  source table   Salary integer  age tinyint   insert into  source select 10000  25 union all select 15000  27 union all select 12000  18 union all select 15000  36 union all select 16000  57 union all select 17000  44 union all select 18000  32 union all select 19000  56 union all select 25000  34 union all select 7500  29 --select   from  source  declare  functionArgument xml  select  functionArgument     select Salary as  row  Salary   Age as  row  Age  from  source for xml path       --select  functionArgument as   functionArgument   select   from readXml  functionArgument      -------- Sample Output  -------- Salary Age ----------- ----------- 10000 25 15000 27 12000 18 15000 36 16000 57 17000 44 18000 32 19000 56 25000 34 7500 29

[sql] Pass table as parameter into sql server UDF

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to parameters

Examples related to user-defined-functions