views:

3608

answers:

4

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)
A: 

The answer is no and we just answered it here:

http://stackoverflow.com/questions/1607918/is-it-possible-to-pass-a-table-name-into-a-stored-proc-and-use-it-without-the-exe/1607957#1607957

[edit portion]

You can use it if you plan on using the keyword EXEC, but you have to be careful of SQL Injection. Still not a prefered method.

JonH
I'm not requesting to pass a table name, I'd like to pass the entire table of data
Nathan Koop
you should be more specific.
Devtron
@koop in the future be more specific with your details instead of negating help from people. It helps to understand what you want, then once you clarify you can rank down an answer. I personnally find it funny you negating two RIGHT answers when the question was in itself not complete.
JonH
@JonH I didn't dv you, **I** thought the question was clear and concise. If it wasn't then you can request further information in the comments. I did monitor this question closely and made edits when it appeared that the question was misinterpreted. I truly appreciate any effort that you have made to assist me in resolving my issue.
Nathan Koop
+2  A: 

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 funcion 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 of 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
Lukasz Lysik
Only for SQL Server 2008
gbn
Ahhh, this is exactly what I want, but it appears as though this is available for SqlServer 2008. I've tried in sqlserver 2005 and I'm getting syntax errors and the documentation mentions specifically 2008. I looked at the SqlServer 2005 documentation for Create Type and it does not have TABLE listed in the options (http://msdn.microsoft.com/en-us/library/ms175007(SQL.90).aspx)
Nathan Koop
A: 

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
*/
Devtron
I'm not trying to get the count of the columns on the table.
Nathan Koop
if you are trying to restrict them to tables with only one column, then yes you are.
Devtron
I am trying to pass a table that contains only one column, I'd like it to throw an error if I pass it a table with more than one column. Just like if I passed an int to a UDF that expected a float
Nathan Koop
^ so what youre saying is, you do need to query the syscolumns table, if you want to do this. how else would you do this?
Devtron
+3  A: 

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
gbn
see my edit.....
Nathan Koop