Wednesday, January 02, 2008

SQL Server Creating User Defined Functions

Here is the basic way to create a User defined Function in SQL Server:

CREATE FUNCTION GETTRAININGSTATUS
(
@USERID VARCHAR(25),
@TRAININGID INT
)
RETURNS INT
AS
BEGIN
DECLARE @COUNT INT
DECLARE @RETURNVALUE INT
SET @COUNT = (SELECT COUNT(*) FROM TBLTRAININGS
WHERE USERID = @USERID AND TRAININGID = @TRAININGID)
IF(@COUNT = '')
SET @RETURNVALUE = 0
ELSE IF (@COUNT = 1)
SET @RETURNVALUE = 1
ELSE IF (@COUNT = 0)
SET @RETURNVALUE = 1

RETURN @RETURNVALUE
END


The function will fetch equivalent value for the element in the table. The same can be achieved with the help of cursors, by I wanted to try it with functions hence the code.

To use this function I write the following SP,


CREATE PROCEDURE SP_TRAININGSTATUS
@USERID varchar(50)
AS

SELECT TRAININGID, TRAINING,
dbo.GETTRAININGSTATUS(@USERID, @TRAININGID) AS SKILLSTATUS
FROM TBLTRAININGS
GO

No comments: