Setting Variables in SQL Functions/Probs

Setting variables in SQL functions/probs

Try
DECLARE @Date1 datetime
SET @Date1 = '2012-03-23'

Looks like you were missing the declare statement. If it doesn't like the '2012-03-23' part, you may have to cast it.

Assigning function result to SQL variable and displaying

You need to call it like this:

select @value = dbo.getNumber()

How to Set a variable inside a Function with Select SQL

Split the commands

CREATE FUNCTION GetSubTree (@SupervisorID int)
RETURNS @Result TABLE(
    Id  INT     PRIMARY KEY NOT NULL,
    HIERARCHY   HIERARCHYID    NOT NULL ,
    FirstName   NVARCHAR(50)    NOT NULL,
    LastName   NVARCHAR(50)    NOT NULL,
    NationalCode    bigint    NOT NULL,
    EmployeeRole    NVARCHAR(3) NOT NULL
)
AS
BEGIN
    DECLARE @hi HIERARCHYID;
    DECLARE @hiT TABLE(
        HI HIERARCHYID
    )

    SELECT Top 1 @hi= HIERARCHY
    FROM dbo.Company as C1
    WHERE C1.Id = @SupervisorID;

    INSERT into @hiT
    VALUES
    (@hi)
    
    INSERT INTO @Result
    SELECT * 
    FROM   dbo.Company as C
    WHERE   C.HIERARCHY.IsDescendantOf(@hi) = 1 AND C.Id != @SupervisorId;
    RETURN;

End;

Variable assignment in SQL function

Your function is just simply getting the second (.) of your url given like

thisis.my.url

it will return a result of my

As per your question

Can we re-frame the code like below?

My Answer is Yes

Microsoft t sql how to declare temporary variable within user defined function?

You are creating an Inline Table-valued Function, this kind of functions must contain only a single SELECT statement.

If you want to use variables you must create a Multi-statement Table-valued Function. Your function would be declared as:

create function someFunction(@someParam varchar(100))
returns @table (field1 type, field2 type, ...)
as 
begin
    declare @tempvar varchar(100)
    set @tempvar = ''--Set the var to something useful
    insert @table
    select * from sometable where somecolumn = (
        select top 1 someColumn
        from sometable
        where somecolumn = @tempvar
    )
    return
end

---

Related Topics