CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
cooper_milton
cooper_milton

Reputation: 87

Calling a scalar function with table param from inside another select

I am working with SQL Server 2008.

I have a custom function called fn_takeTableArg and I need to be able to use it like this:

 select dbo.fn_takeTableArg(<a union of multiple selects>) as MyField from MyTable

The custom table arg is:

 CREATE TYPE [dbo].[TableArg] AS TABLE(
  Value VARCHAR(2000),
  RepOrApp CHAR
  SortOrder INT
  )

Function is something like:

CREATE FUNCTION dbo.fn_takeTableArg
(
  @t [dbo].[TableArg] READONLY
)
RETURNS VARCHAR(max)
AS
     ... Do something over the passed in table with a with clause, etc.

But, seems like this can't be done as I get the "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS." error.

Presumably because I am expected to create a temp table variable first and then call my function with that variable?

But, I can't do that because I have to call this function from inside another select.

Upvotes: 2

Views: 2252

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

I really doubt, that your approach is the best you can choose, but to answer your question: You have to fill a typed parameter like any other table first and pass it in:

USE master;
GO
CREATE DATABASE testdb;
GO
USE testdb;
GO
CREATE TYPE [dbo].[TableArg] AS TABLE(
   [Value] VARCHAR(2000)
  ,RepOrApp CHAR
  ,SortOrder INT
  );
GO
CREATE FUNCTION dbo.fn_takeTableArg
(
  @t [dbo].[TableArg] READONLY
)
RETURNS VARCHAR(max)
AS
BEGIN
RETURN (SELECT TOP 1 [Value] FROM @t);
END
GO

--declare the parameter with your type
DECLARE @TheParameter dbo.TableArg;
--... and fill it
INSERT INTO @TheParameter([Value],RepOrApp,SortOrder)
SELECT o.[name],'x',1 
FROM sys.objects o;

--The parameter is like a table
SELECT * FROM @TheParameter;
--...and can be passed as the funcion's argument
SELECT dbo.fn_takeTableArg(@TheParameter);
GO
USE master;
GO
DROP DATABASE testdb;

UPDATE

Just saw your remark

Presumably because I am expected to create a temp table variable first and then call my function with that variable?

But, I can't do that because I have to call this function from inside another select.

There is a trick for this: You can wrap a SELECT ... FOR XML in paranthesis and handle the returned XML similar to a scalar value.

CREATE FUNCTION dbo.fn_takeTableArg
(
  @t XML --You function accepts an XML
)
RETURNS VARCHAR(max)
AS
BEGIN
--do something with the XML, in this case: return the first attribute you find
RETURN @t.value(N'(//@*)[1]','varchar(max)');
END
GO

--The function can take the generic `SELECT` as argument  
--(not really, but the full result-set translated to an XML)
SELECT dbo.fn_takeTableArg(
                            (
                              SELECT [name]
                                    ,'x' AS RepOrApp
                                    ,1 AS SortOrder 
                              FROM sys.objects
                              FOR XML AUTO)
                            );
GO

Within the function the XML looks like this:

<sys.objects name="sysrscols" RepOrApp="x" SortOrder="1" />
<sys.objects name="sysrowsets" RepOrApp="x" SortOrder="1" />
<sys.objects name="sysclones" RepOrApp="x" SortOrder="1" />
...

All namings ('sys.objects', 'name' etc.) can be controlled with the aliases you choose.

Remark

Scalar functions are bad in performance. A scalar function dealing with an XML, which has to be created for each and any row will be very slow... There are much better approaches assumably...

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can pass value in scalar function as 

select dbo.fn_takeTableArg(t.col) as MyField 
from (<a union of multiple selects>) t;

Upvotes: 0

Related Questions