CODEWITHSUNDEEP
sql-server-2008t-sqlsql-function
omkar patade
omkar patade

Reputation: 1591

SQL - function - use one column at a time which return table

I have a SQL Server function:

ALTER FUNCTION [dbo].[Element_DryScrapManualSurface]
(
    @Start_date datetime,
    @End_date datetime,
    @codeusine varchar(10),
    @codeatelier varchar(10),
    @ligne varchar(10),
    @Trialcheck bit
)
RETURNS @Result TABLE (Element_DryScrapManualSurface float,
                       Element_DryScrapManualBoards float)
AS
BEGIN
    insert into @Result
        select * from table_x

    select * from @Result
END

Now while using this function, sometimes I want to use only single column from this function, e.g. in a stored procedure.

If I use this code - it will return the whole table

declare @para float

set @para = isnull((select dbo.[Element_DryScrapManualSurface](@Start_date,@End_date,@codeusine,@codeatelier,@ligne,@Trialcheck)),0)

How to get only the Element_DryScrapManualSurface column?

Upvotes: 0

Views: 467

Answers (3)

GarethD
GarethD

Reputation: 69749

You are calling it wrong, since you have a table valued function it should be:

SELECT  Element_DryScrapManualSurface,
        Element_DryScrapManualBoards
FROM    dbo.Element_DryScrapManualSurface (@Start_date,@End_date,@codeusine,@codeatelier,@ligne,@Trialcheck);

Then you can assign a variable from it:

SELECT  TOP 1  @para = Element_DryScrapManualSurface
FROM    dbo.Element_DryScrapManualSurface (@Start_date,@End_date,@codeusine,@codeatelier,@ligne,@Trialcheck)
ORDER BY Element_DryScrapManualSurface;

I don't know if your function returns multple results, but I have added TOP 1 AND ORDER BY to ensure that the assignment is deterministic in case it does return multiple rows.

Upvotes: 2

FLICKER
FLICKER

Reputation: 6683

try this:

with cte1 as (
select dbo.[Element_DryScrapManualSurface](@Start_date
         ,@End_date,@codeusine,@codeatelier,@ligne,@Trialcheck)),0)
)
select @para = *yourfieldname*
from cte1

you also may want to use TOP 1 if needed.

Upvotes: 0

Vimal Vataliya
Vimal Vataliya

Reputation: 61

try this:

set @para = isnull((select top 1 Element_DryScrapManualBoards FROM dbo.[Element_DryScrapManualSurface](@Start_date,@End_date,@codeusine,@codeatelier,@ligne,@Trialcheck)),0)

Upvotes: 0

Related Questions