The following code sample shows how to create User Defined Function in Sql .
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.
The following stored procedure is used for calling the function.
CREATE PROCEDURE SP_TRAININGSTATUS
@USERID varchar(50)
AS
SELECT TRAININGID, TRAINING, dbo.GETTRAININGSTATUS(@USERID, @TRAININGID)
AS SKILLSTATUS
FROM
TBLTRAININGS GO
|
| Author: Kapil Dhawan 17 Jun 2008 | Member Level: Gold Points : 2 |
Hello
Nice piece of code
Thanks for sharing your knowledge with us.
I hope to see more good code from your side
This code is going to help lots of guys.
Ton Thanks to you
Regards,
Kapil
|
| Author: Roshan R Mhatre 24 Jul 2008 | Member Level: Gold Points : 1 |
Hello
Nice piece of code
Thanks for sharing your knowledge with us.
I hope to see more good code from your side
This code is going to help lots of guys.
Ton Thanks to you
Regards,
Roshan
|
| Author: Nirupa Rani Acharya 27 Jul 2008 | Member Level: Bronze Points : 1 |
really thanks 4 dis code. plez send more db code & share ur intellegence with us.
Thanks & Regards,
Nirupa Rani Acharya,
C.S.Informatics (P) Ltd., Pune
|
| Author: Nirupa Rani Acharya 27 Jul 2008 | Member Level: Bronze Points : 1 |
really thanks 4 dis code. plez send more db code & share ur intellegence with us.
Thanks & Regards,
Nirupa Rani Acharya,
C.S.Informatics (P) Ltd., Pune
|