What are Functions In MSsqlServer

/*
Hello Friend Today You Learn to MSSqlServer Function
How Can Make A function and it’s testing at back end
this can be used with asp.net
First Of All You should be aware about funcation
To the point Response about funcations
Functions are object in your database like store procedure ,table etc.
Functions Can return any vlaue like various data types
Functions Can return following data types
Text,N-Text,TimeStamp,Cursor,Image
In Function we can’t Use following.
We Cann’t Write DBCC(Database Consistancy Check) commands in Function.
We Can Use Functions Direct In Query.
There are Four types of Funcations
——USER DEFINED FUNCTIONS—————–
| 1) Table Value Funcation |
—————-Two types it’s—————
|Inline |
|MultiLine |
———————————————
|2) Scalar Value Funcation |
———————————————
———————————-
——BUILT IN FUNCTIONS——
3) Aggregate Funcation
4) System Funcation
———————————-
*/
–Create Your New Database
CREATE DATABASE  DBSESSION
GO
–Now Use Your Database
USE DBSESSION
GO
–Now Create a New Table in Your Database with given datatypes and with size
CREATE TABLE TBL_SESSION
(
department       CHAR(3),
course           INT,
description      VARCHAR(500),
max_lecturer     INT,
current_lecturer INT,
num_credits      INT,
room_id          INT
)
–Check out that Your Table and It’s Columns has been created
EXEC sp_Columns tbl_Session
–Now Insert some values in Your Table
INSERT INTO tbl_session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
VALUES (‘HIS’, 101, ‘History 101’, 1000,999, 4, 20000);
INSERT INTO tbl_session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
VALUES (‘HIS’, 301, ‘History 301’,1000,780, 4, 20004);
INSERT INTO tbl_session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
VALUES (‘CS’, 101, ‘Computer Science 101’,900,700, 4, 20001);
INSERT INTO tbl_session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
VALUES (‘ECN’, 203, ‘Economics 203’, 1000,999, 3, 20002);
INSERT INTO tbl_session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
VALUES (‘CS’, 102, ‘Computer Science 102’,1000,452, 4, 20003);
INSERT INTO tbl_session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
VALUES (‘MUS’, 410, ‘Music 410’, 500,500, 3, 20005);
INSERT INTO tbl_session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
VALUES (‘ECN’, 101, ‘Economics 101’, 1000,336, 4, 20007);
INSERT INTO tbl_session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
VALUES (‘NUT’, 307, ‘Nutrition 307’,1000, 465, 4, 20008);
INSERT INTO tbl_session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
VALUES (‘MUS’, 100, ‘Music 100’,800,652, 3, NULL);
–Check out data has been insert into your table
Select *from tbl_Session
–Now Create A funcation Which Will Return String Value to You write down given below Commands
–This is scalar value function
—Start of Funcation
CREATE FUNCTION ORIENT_ClassInfo(@p_Department VARCHAR(10),@p_Course INT)
RETURNS VARCHAR(30)
AS
BEGIN
DECLARE @studentCount  INT;
DECLARE @studentMax    INT;
DECLARE @v_PercentFull INT;
DECLARE @RETURN VARCHAR(30)
SET @studentCount=(SELECT current_lecturer FROM TBL_SESSION WHERE department = @p_Department AND course = @p_Course);
SET @studentMax =(SELECT max_lecturer FROM TBL_SESSION WHERE department = @p_Department AND course = @p_Course);
SELECT @v_PercentFull =(@studentCount/@studentMax*100);
IF (@v_PercentFull=100)
BEGIN
SET @RETURN=’FULL’;
END
IF (@v_PercentFull>80)
BEGIN
SET @RETURN=’Some Room’;
END
IF (@v_PercentFull>60)
BEGIN
SET @RETURN=’More Room’ ;
END
IF (@v_PercentFull>0)
BEGIN
SET @RETURN=’Lots of Room’;
END
RETURN @RETURN;
END
–End of Funcation
——————————————————-
–check out Your funcation has been created of Not
–With This built In Funcation
EXEC SP_HELP ‘ORIENT_ClassInfo’
–If you want to drop your funcation then can use given below query
DROP FUNCTION ORIENT_ClassInfo
—————————–
SELECT *FROM TBL_SESSION
–Check Out Funcation Output and How to Execute to custom Funcaiton
SELECT dbo.ORIENT_ClassInfo(department,course) AS RESULT,department,course FROM TBL_SESSION
—another way to execution of function
SELECT dbo.ORIENT_ClassInfo(‘ECN’,203) AS RESULT
–Be Happy and Enjoy in Sql World~~~~
–SCALAR Value Function
/*FOR THIS ATTACH NORTHWIND DATABASE OR CAN MAKE YOUR OWN TABLES IN
YOUR OWN DATABASE THIS DATABASE IS FREE FOR SQL PRACTICE CAN YOU DOWNLOAD TO IT
IF YOU GET FAILURE TO DOWNLOAD IT CAN MAIL ME FOR THIS DATABASE
*/
USE NORTHWIND
Create FUNCTION FUN_ORIENTGETCATNAME
(
@PRD VARCHAR(50)
)
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @P VARCHAR(50)
SELECT @P=CATEGORYNAME FROM PRODUCTS A,CATEGORIES B WHERE
A.CATEGORYID=B.CATEGORYID AND PRODUCTNAME=@PRD;
RETURN @P
END
—————-
SELECT DBO.FUN_ORIENTGETCATNAME(PRODUCTNAME) AS CAT,PRODUCTNAME,UNITPRICE FROM PRODUCTS
———————————————————————————-
–HOW CAN MAKE REVERSE STRING FUNCTION IN MSSQLSERVER
CREATE FUNCTION FUN_ORIENTSTRREV
(
@STR VARCHAR(50), –STRING
@SP INT–STRING PLACEMENT
)
RETURNS VARCHAR(50)
AS
BEGIN
IF ((@SP<=0) OR (@SP>=LEN(@STR)))
RETURN REVERSE(@STR)
RETURN STUFF(@STR,@SP,LEN(@STR)-@SP+1,REVERSE(SUBSTRING(@STR,@SP,LEN(@STR)-@SP+1)))
END
—————————————
DROP FUNCTION FUN_ORIENTSTRREV
SELECT DBO.FUN_ORIENTSTRREV(‘ARYAN’,1)
————————————-
–TABLE VALUE FUNCTION
———————————-INLINE
CREATE FUNCTION FUN_DATA(@CID INT )
RETURNS TABLE
AS
BEGIN
RETURN SELECT PRODUCTNAME,UNITPRICE FROM PRODUCTS WHERE CATEGORYID=@CID;
END
————
SELECT *FROM DBO.FUN_DATA(1);
——————————————————————
CREATE FUNCTION FUN_DATA1(@CNAME VARCHAR(50)) RETURNS TABLE AS
RETURN SELECT *FROM PRODUCTS
———————————–
SELECT *FROM DBO.FUN_DATA1(‘DIARY PRODUCTS’);
———————————–MULTILINE
CREATE FUNCTION FUN_DATA2(
@CN VARCHAR(50)
)
RETURNS @P TABLE(PN VARCHAR(50),PRC  INT)
—this option can use if you want to encrypt your function nobody can view it’s logic
–WITH ENCRYPTION
AS
BEGIN
INSERT INTO @P SELECT PRODUCTNAME,UNITPRICE FROM PRODUCTS WHERE DBO.FUN_ORIENTGETCATNAME(PRODUCTNAME)=@CN;
DECLARE @CNT INT
SELECT @CNT=COUNT(*) FROM @P
IF @CNT =0
BEGIN
INSERT @P VALUES(‘ABC’,555)
END
RETURN
END
—-
/*CHECK OUT FUNCTION COMPOSED IN DATABASE WITH BODY OF FUNCTION ALSO
CAN CHECK OUT UR STORE PROCEDURE*/
EXEC SP_HELPTEXT ‘FUN_DATA2’
DROP FUNCTION FUN_DATA2
————
SELECT *FROM DBO.FUN_DATA2(‘DIARY PRODUCTS’)
—————-
———-USE OF SCHEMABINDING WITH FUNCTION
CREATE TABLE TBORIENT(EMPNO INT,ENAME VARCHAR(50))
–INSERT SOME RECORDS AND THEN CREATE FUNCTION
CREATE FUNCTION FUN_ORIENT() RETURNS
TABLE
WITH SCHEMABINDING
AS
RETURNS SELECT ENAME FROM DBO.TBORIENT
——————————–
–NOW TRY TO ALTER YOUR TABLE COLUMN
ALTER TABLE TBORIENT
ALTER COLUMN ENAME VARCHAR(30)
——–
–AT THIS STAGE YOU WILL GET AN ERROR BECAUSE OF SCHEMABINDING
–first you will have to drop to function  then can alter your table
Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s