Is it possible to use a Function directly as a Input Parameter for a Inline UDF

20 Jan 2006 9:47 AM

Franz

In a Scalar-valued Function i can give a other Function (System or UDF)
directly as Input Parameter. In a Inline Function and Table-valued
Function it doesn't work.

See the samples:

-- Create this Function as a UDF Inline Function
ALTER FUNCTION dbo.TestFKT2
(
@strSUSER varchar(256)
)
RETURNS @retTable TABLE (ID_C varchar(128))
AS
BEGIN
-- Its only a Test i will give the Parameter in a SubQuery
INSERT INTO @retTable SELECT @strSUSER
RETURN
END

-- 1 Call this in Query Analayser
SELECT * FROM dbo.TestFKT2(SUSER_SNAME())

-- Output Error
Server: Nachr.-Nr. 170, Schweregrad 15, Status 1, Zeile 1
Line 1: Incorrect syntax near '('.

-- 2 Call this in Query Analayser
SELECT * FROM dbo.TestFKT2('Test')
-- Output 'Test' It works

-- 3 Call this in Query Analayser
DECLARE @strSUSER varchar(128)
SET @strSUSER = SUSER_SNAME()
select * FROM dbo.TestFKT2(@strSUSER)
-- Output 'DOMAIN\User' It works

What ist the different? And wy is it so?

Thank you for Help

20 Jan 2006 9:51 AM

Uri Dimant

Franz

Look, it is the same as you type SUSER_SNAME() in QA and press run

You will have issue SELECT SUSER_SNAME() just think about it

Show quote

"Franz" <f*@sunrisesoftware.at> wrote in message
news:1137750428.995894.265090@z14g2000cwz.googlegroups.com...
> In a Scalar-valued Function i can give a other Function (System or UDF)
> directly as Input Parameter. In a Inline Function and Table-valued
> Function it doesn't work.
>
> See the samples:
>
> -- Create this Function as a UDF Inline Function
> ALTER FUNCTION dbo.TestFKT2
> (
> @strSUSER varchar(256)
> )
> RETURNS @retTable TABLE (ID_C varchar(128))
> AS
> BEGIN
> -- Its only a Test i will give the Parameter in a SubQuery
> INSERT INTO @retTable SELECT @strSUSER
> RETURN
> END
>
> -- 1 Call this in Query Analayser
> SELECT * FROM dbo.TestFKT2(SUSER_SNAME())
>
> -- Output Error
> Server: Nachr.-Nr. 170, Schweregrad 15, Status 1, Zeile 1
> Line 1: Incorrect syntax near '('.
>
> -- 2 Call this in Query Analayser
> SELECT * FROM dbo.TestFKT2('Test')
> -- Output 'Test' It works
>
> -- 3 Call this in Query Analayser
> DECLARE @strSUSER varchar(128)
> SET @strSUSER = SUSER_SNAME()
> select * FROM dbo.TestFKT2(@strSUSER)
> -- Output 'DOMAIN\User' It works
>
> What ist the different? And wy is it so?
>
> Thank you for Help
>

20 Jan 2006 10:16 AM

Razvan Socol

Hi, Franz

The values for the parameters of table-valued UDF-s can be only
constants or variables, not expressions. When you invoke another
function, that's an expression, and this is (currently) not allowed.

Razvan