Good day.

I created a function, declare a variable in it, but a syntax error occurs, which I am wrong about:

USE [MYTABLE] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[func] (@users nvarchar(4)) RETURNS TABLE AS DECLARE @i int; RETURN (SELECT * FROM MYTABLE.dbo.users WHERE users = @users) GO

    3 answers 3

    Something seems to me that is not enough

     AS BEGIN ... END

    And something else

    UPD1 :
    If you need to programmatically fill the table, then you need to do something like this:

     CREATE FUNCTION dbo.fn_FindReports (@InEmpID INTEGER) RETURNS @retFindReports TABLE ( EmployeeID int primary key NOT NULL, Name nvarchar(255) NOT NULL, ) AS BEGIN ... операции по заполению временной таблицы @retFindReports /* и НЕ ЗАБЫТЬ*/ RETURN END; GO
    • Understood, thanks. At random I learned that variables can be declared before the function block. - Afipsky 2011

    While writing the answer somewhere wrong AS in

     DECLARE @i AS int;

    :-)
    However, the problem with the code is that there are two types of table-valued UDFs. And you need to follow either one syntactic rule or another, but not a mixture of them.

      The returned parvmeter may have to be OUTPUT

      • This is not true. OUTPUT is used in stored procedures, not in functions. - msi