CODEWITHSUNDEEP
sql
Mic
Mic

Reputation: 1

SQL PROCEDURE into SQL FUNCTION

I need to convert this procedure : 

CREATE PROCEDURE GETIT @ID_JOBS INT
AS
SELECT  TOP 1
CASE WHEN CHARINDEX('/', CCTRef ) <= 1 then ' '
ELSE
  SUBSTRING(CCTRef, CHARINDEX('/',CCTRef)+1, CHARINDEX('/',CCTRef,  CHARINDEX('/',CCTRef)+1) -CHARINDEX('/',CCTRef)-1)
  END
FROM Shared.dbo.JobsSparesALL
WHERE ID_Jobs = @ID_JOBS
ORDER BY LEN(CCTRef) DESC
GO

INTO function... i dont know how. any help would be great

Upvotes: 0

Views: 32

Answers (1)

Atk
Atk

Reputation: 752

Scalar function

CREATE FUNCTION YourFunction
(
    -- Add the parameters for the function here
    @ID_JOBS INT
)
RETURNS VARCHAR(MAX)
AS
    BEGIN

    Declare @RESULT VARCHAR(MAX)

    SET @RESULT=(SELECT  TOP 1
    CASE WHEN CHARINDEX('/', CCTRef ) <= 1 then ' '
    ELSE
    SUBSTRING(CCTRef, CHARINDEX('/',CCTRef)+1, CHARINDEX('/',CCTRef,  CHARINDEX('/',CCTRef)+1) -CHARINDEX('/',CCTRef)-1)
    END
    FROM Shared.dbo.JobsSparesALL
    WHERE ID_Jobs = @ID_JOBS
    ORDER BY LEN(CCTRef) DESC)

    -- Return the result of the function
    RETURN @RESULT

END
GO

Table value function

CREATE FUNCTION YourFunction
(   
    -- Add the parameters for the function here
    @ID_JOBS INT
)
RETURNS TABLE 
AS
RETURN 
(
    -- Add the SELECT statement with parameter references here
    SELECT  TOP 1
    CASE WHEN CHARINDEX('/', CCTRef ) <= 1 then ' '
    ELSE
    SUBSTRING(CCTRef, CHARINDEX('/',CCTRef)+1, CHARINDEX('/',CCTRef,  CHARINDEX('/',CCTRef)+1) -CHARINDEX('/',CCTRef)-1)
    END
    FROM Shared.dbo.JobsSparesALL
    WHERE ID_Jobs = @ID_JOBS
    ORDER BY LEN(CCTRef) DESC
)
GO

Upvotes: 2

Related Questions