Stored Procedures:
Procedure is a self contained program or predefined program, which does not return value.
SQL server supports to work with the following types of procedures;
i. Predefined Procedures
sp_help
sp_helpdb
sp_spaceused
ii. User Defined Procedures
1.It is a stored block database object, which resides in database server.
2.It is a self contained program or predefined program which does not return value.
3.It can be created with and without arguments.
4.Data can be sent to procedures to process it using Input Arguments and few values can be taken back to the main program using OUTPUT arguments.
5.A procedure can be set with 1024 Input and 1024 Output Arguments.
6.When a procedure is set with OUTPUT arguments then it is essential that at sending argument OUTPUT should be specified.
7.Procedure set with input arguments will work for call by value and output arguments will work for call by reference.
Syntax:
CREATE PROC / PROCEDURE proc_name([list_of_args])
[WITH Recompile]
AS
BEGIN
[declaration block]
execution block
END
A Procedure can be executed in 2 ways;
1. using EXEC command
Syntax:
EXEC proc_name [list_of_args]
2. using Anonymous block (or main program)
EXAMPLES:
1. Create a procedure with no arguments and retrieve the data of emp table?
CREATE PROC ERET
AS
BEGIN
SELECT * FROM EMP
END
EXEC ERET
2. Create a procedure with 2 input arguments and find sum of 2 values?
CREATE PROC ADDT(@A INT,@B INT)
AS
BEGIN
DECLARE @C INT
Select @C=@A+@B
PRINT 'SUM IS.....'+@
END
execution:
EXEC ADDT 17,38
DECLARE @X INT,@Y INT
SET @X=89
SET @Y=56
EXEC ADDT @X,@Y
3. Create a procedure with 2 input arguments and 1 output argument that finds sum of 2 values?
CREATE PROC SUMT(@A INT,@B INT,@C INT OUTPUT)
AS
BEGIN
select @C=@A+@B
END
DECLARE @X INT,@Y INT,@Z INT
Select @X=92
Select @Y=84
EXEC SUMT @X,@Y,@Z OUTPUT
PRINT 'ADDITION IS ....'+@
4. Create a procedure that takes empno as input and displays ename,job,sal,deptno of that employee?
CREATE PROC ERET1(@ENO INT)
AS
BEGIN
SELECT ENAME,JOB,SAL,DEPTNO FROM EMP WHERE EMPNO=@ENO
END
EXEC ERET1 1001
EXEC ERET1 1002
OR
CREATE PROC ERET2(@ENO INT)
AS
BEGIN
DECLARE @EN VARCHAR(12),@J VARCHAR(14),@PAY INT,@DNO INT
SELECT @EN=ENAME,@J=JOB,@PAY=SAL,@DNO=DEPTNO FROM EMP WHERE EMPNO=@ENO
PRINT @ENO
PRINT @EN
PRINT @J
PRINT @PAY
PRINT @DNO
END
EXEC ERET2 7788
OR
CREATE PROC ERET3(@ENO INT,@EN VARCHAR(12) OUTPUT,@J VARCHAR(12) OUTPUT,@PAY INT OUTPUT,@DNO INT OUTPUT)
AS
BEGIN
SELECT @EN=ENAME,@J=JOB,@PAY=SAL,@DNO=DEPTNO FROM EMP WHERE EMPNO=@ENO
END
DECLARE @EC INT,@N VARCHAR(12),@D VARCHAR(12),@P INT,@DC INT
Select @EC=7902
EXEC ERET3 @EC,@N OUTPUT,@D OUTPUT,@P OUTPUT,@DC OUTPUT
PRINT @EC
PRINT @N
PRINT @D
PRINT @P
PRINT @DC
SET NOCOUNT ON STATEMENT:
SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a T-SQL statement.
5. Create a procedure to insert a new record into emp table for empno,ename,sal and deptno by checking all validations?
CREATE PROC INSREC(@ENO INT,@EN VARCHAR(12),@PAY INT,@DNO INT)
AS
BEGIN
SET NOCOUNT ON
DECLARE @I INT,@J INT
SELECT @I=COUNT(*) FROM EMP WHERE EMPNO=@ENO
SELECT @J=COUNT(*) FROM DEPT WHERE DEPTNO=@DNO
IF @ENO IS NULL
PRINT 'CANT INSERT NULL VALUE INTO EMPNO'
ELSE
IF @I>0
PRINT 'EMPLOYEE ALREADY EXISTS'
ELSE
IF @J=0
PRINT 'INVALID DEPTNO'
ELSE
IF @PAY<800
PRINT 'INVALID PAY'
ELSE
BEGIN
BEGIN TRAN
INSERT EMP(EMPNO,ENAME,SAL,DEPTNO) VALUES(@ENO,@EN,@PAY,@DNO)
COMMIT
PRINT 'RECORD INSERTED SUCCESSFULLY'
END
END
EXEC INSREC 7788,'AHMED',2500,30 -- EMPLOYEE ALREADY EXISTS
EXEC INSREC NULL,'AHMED',2500,30 -- CANT INSERT NULL VALUE IN EMPNO
EXEC INSREC 1001,'AHMED',700,20 -- INVALID PAY
EXEC INSREC 1001,'AHMED',3400,80 -- INVALID DEPTNO
EXEC INSREC 1001,'AHMED',3400,30 -- Record inserted successfully
EXEC INSREC 1002,'HARISH',4500,20 -- Record inserted successfully
EXEC INSREC 1003,'RAJ',2300,30 -- Record inserted successfully
6. Create a procedure to Update the salary of employee if it is valid increment of more than 500?
CREATE PROC UPDROW(@ENO INT,@INCR INT)
AS
BEGIN
SET NOCOUNT ON
DECLARE @I INT
SELECT @I=COUNT(*) FROM EMP WHERE EMPNO=@ENO
IF @I=0
PRINT 'EMPLOYEE DOES NOT EXIST'
ELSE
IF @INCR<500
PRINT 'INVALID INCREMENT'
ELSE
BEGIN
BEGIN TRAN
UPDATE EMP SET SAL=SAL+@INCR WHERE EMPNO=@ENO
COMMIT
PRINT 'ROW UPDATED SUCCESSFULLY'
END
END
EXEC UPDROW 7788,200 -- INVALID INCREMENT
EXEC UPDROW 2001,1200 -- EMPLOYEE DOES NOT EXIST
EXEC UPDROW 7788,1000 -- ROW UPDATED SUCCESSFULLY
7. Create a procedure to delete the rows from a table based on deptno?
CREATE PROC DELREC(@DNO INT)
AS
BEGIN
SET NOCOUNT ON
DECLARE @I INT,@J INT
SELECT @J=COUNT(*) FROM DEPT WHERE DEPTNO=@DNO
SELECT @I=COUNT(*) FROM EMP WHERE DEPTNO=@DNO
IF @J=0
PRINT 'INVALID DEPTNO'
ELSE
IF @I=0
PRINT 'EMPLOYEES DOES NOT EXIST'
ELSE
BEGIN
BEGIN TRAN
DELETE FROM EMP WHERE DEPTNO=@DNO
COMMIT
PRINT CAST(@I AS VARCHAR(5))+' ROWS ARE DELETED'
END
END
EXEC DELREC 10
EXEC DELREC 80
EXEC DELREC 10
8. Create a procedure that performs transactions related to bank for depostis, withdrawls of amount with necessary validations.
CREATE TABLE BANK
(ACNO INT,AHN VARCHAR(12) CONSTRAINT ACNO_PK PRIMARY KEY,ADDRESS VARCHAR(15),BAL INT)
INSERT BANK VALUES(1001,'HARI','AMRPT',12340)
INSERT BANK VALUES(1007,'KIRAN','BHEL',12900)
INSERT BANK VALUES(1002,'RAJ','ECIL',15400)
INSERT BANK VALUES(1009,'KARAN','AMRPT',23800)
INSERT BANK VALUES(1004,'SUNIL','ABIDS',34900)
BANK
ACNO AHN ADDRESS BAL
1001 HARI AMRPT 12340
1007 KIRAN BHEL 12900
1002 RAJ ECIL 15400
1009 KARAN AMRPT 23800
1004 SUNIL ABIDS 34900
CREATE TABLE TRANS
(ANO INT,TT VARCHAR(5),AMOUNT INT,DAT SMALLDATETIME)
TRANS
ANO TT AMOUNT DAT
CREATE PROC BTRAN(@ACC INT,@TRTY VARCHAR(5),@AMT INT)
AS
BEGIN
SET NOCOUNT ON
DECLARE @I INT,@B INT
SELECT @I=COUNT(*) FROM BANK WHERE ACNO=@ACC
SELECT @B=BAL FROM BANK WHERE ACNO=@ACC
IF @I=0
PRINT 'INVALID ACCOUNT NUMBER'
ELSE
IF @TRTY='D'
BEGIN
BEGIN TRAN
INSERT TRANS VALUES(@ACC,@TRTY,@AMT,GETDATE())
UPDATE BANK SET BAL=BAL+@AMT WHERE ACNO=@ACC
COMMIT
PRINT 'ROW INSERTED AND UPDATED SUCCESSFULLY'
END
ELSE
BEGIN
IF @TRTY='W'
BEGIN
IF @AMT>@B
PRINT 'OVER DRAFT OF AMOUNT'
ELSE
IF @AMT>@B-500
PRINT 'INVALID TRANS - MIN BAL SHOULD BE 500'
ELSE
IF @AMT<100
PRINT 'INVALID AMOUNT'
ELSE
BEGIN
BEGIN TRAN
INSERT TRANS VALUES(@ACC,@TRTY,@AMT,GETDATE())
UPDATE BANK SET BAL=BAL-@AMT WHERE ACNO=@ACC
COMMIT
PRINT 'ROW INSERTED AND UPDATED SUCCESSFULLY'
END
END
ELSE
PRINT 'INVALID TRANSACTION TYPE'
END
END
DYNAMIC QUERIES:
A query in a block is provided with input at the execution of a program,so that dynamically changes can be made to the query,and will generate the different output.
1. Retrieving the data from any table:
CREATE PROC RETTAB(@TN VARCHAR(12))
AS
BEGIN
EXEC ('SELECT * FROM '+@TN)
END
EXEC RETTAB EMP
EXEC RETTAB DEPT
2. Create a table using procedure with passing table names at runtime:
CREATE PROC CT(@TN VARCHAR(12))
AS
BEGIN
EXEC ('CREATE TABLE '+@TN+'(ENO INT,EN VARCHAR(12))')
END
EXEC CT E1
EXEC CT E2
3. Creating a table with table names, column names, data types dynamically?
CREATE PROC CT1(@TN VARCHAR(12),@COL VARCHAR(12),@DT VARCHAR(12))
AS
BEGIN
EXEC ('CREATE TABLE '+@TN+'('+@COL+' '+@DT+')')
END
EXEC CT1 'T1','ENO','INT'
EXEC CT1 'T2','RNO','SMALLINT'
4. Adding a new column in a table?
CREATE PROC AT1(@TN VARCHAR(12))
AS
BEGIN
EXEC ('ALTER TABLE '+@TN+' ADD NAME VARCHAR(12)')
END
EXEC AT1 T1
5. Altering the size of existing column:
CREATE PROC AT2(@TN VARCHAR(12))
AS
BEGIN
EXEC ('ALTER TABLE '+@TN+' ALTER COLUMN NAME VARCHAR(20)')
END
EXEC AT2 T1
6. Droping a table:
CREATE PROC DT(@TN VARCHAR(12))
AS
BEGIN
EXEC ('DROP TABLE '+@TN)
END
EXEC DT E1
EXEC DT E2
MODIFYING THE PROCEDURE:
ALTER PROC/PROCEDURE PROC_NAME([list_of_Args])
[WITH ENCRYPTION]
AS
BEGIN
STATEMENTS
END
DROPING THE PROCEDURE:
DROP PROC / PROCEDURE PROC_NAME
declare @a int,@b int,@c int,@d int
select @a=100,@b=20,@c=20,@d=@a*@b*@c
print @d
Here are some instruction when creating a store procedure to increase speed:
* Use SQL keyword in capital letters to increase readability.
* Use few possible variables to increase cache memory.
* Try to avoid dynamic queries if we are not using dynamic query
there is no recompilation of execution plan but on the other side
if we are using dynamic query every time we need recompile of plan.
* Use SET NOCOUNT ON this will helps us to get number of row
effected without network traffic.
* In Select and Set use select to assign value to variable
it is much faster than multiple set statement.
* In CAST and CONVERT always try to use CAST
* Avoid Distinct and Order by clause.These class needs extra space.
* Avoid cursor so use while loop for that and temparory tables.
* Avoid correlated sub queries.
* Avoid select * into for large tables it locks the system objects.
* Avoid temporary tables because it will recompile the procedure.
To display all predefined, userdefined stored procedures:
SP_STORED_PROCEDURES
To display all the created user defined procedure names:
SELECT NAME FROM SYSOBJECTS WHERE XTYPE='P'
To display the code of a user defined stored procedure:
SP_HELPTEXT 'proc_name'
To display the information about procedure:
SP_HELP 'proc_name'
sp_depends 'tablename'
iii)Extended Stored Procedures:
A stored procedure contains T-SQL statements and C/C++ code compiled into .dll file is called extended stored procedure.
Every extended stored procedure starts with XP_
All extended stored procedures are stored in MASTER database.
Extended procedures are used to perform operations in OS and Mail Server.
XP_CMDSHELL 'os command' ---- It is used to communicate
with operating system to execute commands.
To work with this extended procedure, it is essential that it should be enabled with the following procedure;
Start--->Programs--->Microsoft SQL Server 2005---->
Configuration Tools---->Sql Server Surface Area Configuration---->
Surface Area configuration for Features---->expand database engine---->
Select xp_cmdshell and then check to enable xp_cmdshell
Example:
EXEC XP_CMDSHELL 'MD C:\SQLS'
EXEC XP_CMDSHELL 'DIR C:\'
EXEC XP_SENDMAIL |
EXEC XP_READMAIL | to interact with mail server.
xp_cmdshell 'dir c:\'
xp_cmdshell 'md c:\peers'
how to check the drive free space?
xp_fixeddrives
Procedure is a self contained program or predefined program, which does not return value.
SQL server supports to work with the following types of procedures;
i. Predefined Procedures
sp_help
sp_helpdb
sp_spaceused
ii. User Defined Procedures
1.It is a stored block database object, which resides in database server.
2.It is a self contained program or predefined program which does not return value.
3.It can be created with and without arguments.
4.Data can be sent to procedures to process it using Input Arguments and few values can be taken back to the main program using OUTPUT arguments.
5.A procedure can be set with 1024 Input and 1024 Output Arguments.
6.When a procedure is set with OUTPUT arguments then it is essential that at sending argument OUTPUT should be specified.
7.Procedure set with input arguments will work for call by value and output arguments will work for call by reference.
Syntax:
CREATE PROC / PROCEDURE proc_name([list_of_args])
[WITH Recompile]
AS
BEGIN
[declaration block]
execution block
END
A Procedure can be executed in 2 ways;
1. using EXEC command
Syntax:
EXEC proc_name [list_of_args]
2. using Anonymous block (or main program)
EXAMPLES:
1. Create a procedure with no arguments and retrieve the data of emp table?
CREATE PROC ERET
AS
BEGIN
SELECT * FROM EMP
END
EXEC ERET
2. Create a procedure with 2 input arguments and find sum of 2 values?
CREATE PROC ADDT(@A INT,@B INT)
AS
BEGIN
DECLARE @C INT
Select @C=@A+@B
PRINT 'SUM IS.....'+@
END
execution:
EXEC ADDT 17,38
DECLARE @X INT,@Y INT
SET @X=89
SET @Y=56
EXEC ADDT @X,@Y
3. Create a procedure with 2 input arguments and 1 output argument that finds sum of 2 values?
CREATE PROC SUMT(@A INT,@B INT,@C INT OUTPUT)
AS
BEGIN
select @C=@A+@B
END
DECLARE @X INT,@Y INT,@Z INT
Select @X=92
Select @Y=84
EXEC SUMT @X,@Y,@Z OUTPUT
PRINT 'ADDITION IS ....'+@
4. Create a procedure that takes empno as input and displays ename,job,sal,deptno of that employee?
CREATE PROC ERET1(@ENO INT)
AS
BEGIN
SELECT ENAME,JOB,SAL,DEPTNO FROM EMP WHERE EMPNO=@ENO
END
EXEC ERET1 1001
EXEC ERET1 1002
OR
CREATE PROC ERET2(@ENO INT)
AS
BEGIN
DECLARE @EN VARCHAR(12),@J VARCHAR(14),@PAY INT,@DNO INT
SELECT @EN=ENAME,@J=JOB,@PAY=SAL,@DNO=DEPTNO FROM EMP WHERE EMPNO=@ENO
PRINT @ENO
PRINT @EN
PRINT @J
PRINT @PAY
PRINT @DNO
END
EXEC ERET2 7788
OR
CREATE PROC ERET3(@ENO INT,@EN VARCHAR(12) OUTPUT,@J VARCHAR(12) OUTPUT,@PAY INT OUTPUT,@DNO INT OUTPUT)
AS
BEGIN
SELECT @EN=ENAME,@J=JOB,@PAY=SAL,@DNO=DEPTNO FROM EMP WHERE EMPNO=@ENO
END
DECLARE @EC INT,@N VARCHAR(12),@D VARCHAR(12),@P INT,@DC INT
Select @EC=7902
EXEC ERET3 @EC,@N OUTPUT,@D OUTPUT,@P OUTPUT,@DC OUTPUT
PRINT @EC
PRINT @N
PRINT @D
PRINT @P
PRINT @DC
SET NOCOUNT ON STATEMENT:
SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a T-SQL statement.
5. Create a procedure to insert a new record into emp table for empno,ename,sal and deptno by checking all validations?
CREATE PROC INSREC(@ENO INT,@EN VARCHAR(12),@PAY INT,@DNO INT)
AS
BEGIN
SET NOCOUNT ON
DECLARE @I INT,@J INT
SELECT @I=COUNT(*) FROM EMP WHERE EMPNO=@ENO
SELECT @J=COUNT(*) FROM DEPT WHERE DEPTNO=@DNO
IF @ENO IS NULL
PRINT 'CANT INSERT NULL VALUE INTO EMPNO'
ELSE
IF @I>0
PRINT 'EMPLOYEE ALREADY EXISTS'
ELSE
IF @J=0
PRINT 'INVALID DEPTNO'
ELSE
IF @PAY<800
PRINT 'INVALID PAY'
ELSE
BEGIN
BEGIN TRAN
INSERT EMP(EMPNO,ENAME,SAL,DEPTNO) VALUES(@ENO,@EN,@PAY,@DNO)
COMMIT
PRINT 'RECORD INSERTED SUCCESSFULLY'
END
END
EXEC INSREC 7788,'AHMED',2500,30 -- EMPLOYEE ALREADY EXISTS
EXEC INSREC NULL,'AHMED',2500,30 -- CANT INSERT NULL VALUE IN EMPNO
EXEC INSREC 1001,'AHMED',700,20 -- INVALID PAY
EXEC INSREC 1001,'AHMED',3400,80 -- INVALID DEPTNO
EXEC INSREC 1001,'AHMED',3400,30 -- Record inserted successfully
EXEC INSREC 1002,'HARISH',4500,20 -- Record inserted successfully
EXEC INSREC 1003,'RAJ',2300,30 -- Record inserted successfully
6. Create a procedure to Update the salary of employee if it is valid increment of more than 500?
CREATE PROC UPDROW(@ENO INT,@INCR INT)
AS
BEGIN
SET NOCOUNT ON
DECLARE @I INT
SELECT @I=COUNT(*) FROM EMP WHERE EMPNO=@ENO
IF @I=0
PRINT 'EMPLOYEE DOES NOT EXIST'
ELSE
IF @INCR<500
PRINT 'INVALID INCREMENT'
ELSE
BEGIN
BEGIN TRAN
UPDATE EMP SET SAL=SAL+@INCR WHERE EMPNO=@ENO
COMMIT
PRINT 'ROW UPDATED SUCCESSFULLY'
END
END
EXEC UPDROW 7788,200 -- INVALID INCREMENT
EXEC UPDROW 2001,1200 -- EMPLOYEE DOES NOT EXIST
EXEC UPDROW 7788,1000 -- ROW UPDATED SUCCESSFULLY
7. Create a procedure to delete the rows from a table based on deptno?
CREATE PROC DELREC(@DNO INT)
AS
BEGIN
SET NOCOUNT ON
DECLARE @I INT,@J INT
SELECT @J=COUNT(*) FROM DEPT WHERE DEPTNO=@DNO
SELECT @I=COUNT(*) FROM EMP WHERE DEPTNO=@DNO
IF @J=0
PRINT 'INVALID DEPTNO'
ELSE
IF @I=0
PRINT 'EMPLOYEES DOES NOT EXIST'
ELSE
BEGIN
BEGIN TRAN
DELETE FROM EMP WHERE DEPTNO=@DNO
COMMIT
PRINT CAST(@I AS VARCHAR(5))+' ROWS ARE DELETED'
END
END
EXEC DELREC 10
EXEC DELREC 80
EXEC DELREC 10
8. Create a procedure that performs transactions related to bank for depostis, withdrawls of amount with necessary validations.
CREATE TABLE BANK
(ACNO INT,AHN VARCHAR(12) CONSTRAINT ACNO_PK PRIMARY KEY,ADDRESS VARCHAR(15),BAL INT)
INSERT BANK VALUES(1001,'HARI','AMRPT',12340)
INSERT BANK VALUES(1007,'KIRAN','BHEL',12900)
INSERT BANK VALUES(1002,'RAJ','ECIL',15400)
INSERT BANK VALUES(1009,'KARAN','AMRPT',23800)
INSERT BANK VALUES(1004,'SUNIL','ABIDS',34900)
BANK
ACNO AHN ADDRESS BAL
1001 HARI AMRPT 12340
1007 KIRAN BHEL 12900
1002 RAJ ECIL 15400
1009 KARAN AMRPT 23800
1004 SUNIL ABIDS 34900
CREATE TABLE TRANS
(ANO INT,TT VARCHAR(5),AMOUNT INT,DAT SMALLDATETIME)
TRANS
ANO TT AMOUNT DAT
CREATE PROC BTRAN(@ACC INT,@TRTY VARCHAR(5),@AMT INT)
AS
BEGIN
SET NOCOUNT ON
DECLARE @I INT,@B INT
SELECT @I=COUNT(*) FROM BANK WHERE ACNO=@ACC
SELECT @B=BAL FROM BANK WHERE ACNO=@ACC
IF @I=0
PRINT 'INVALID ACCOUNT NUMBER'
ELSE
IF @TRTY='D'
BEGIN
BEGIN TRAN
INSERT TRANS VALUES(@ACC,@TRTY,@AMT,GETDATE())
UPDATE BANK SET BAL=BAL+@AMT WHERE ACNO=@ACC
COMMIT
PRINT 'ROW INSERTED AND UPDATED SUCCESSFULLY'
END
ELSE
BEGIN
IF @TRTY='W'
BEGIN
IF @AMT>@B
PRINT 'OVER DRAFT OF AMOUNT'
ELSE
IF @AMT>@B-500
PRINT 'INVALID TRANS - MIN BAL SHOULD BE 500'
ELSE
IF @AMT<100
PRINT 'INVALID AMOUNT'
ELSE
BEGIN
BEGIN TRAN
INSERT TRANS VALUES(@ACC,@TRTY,@AMT,GETDATE())
UPDATE BANK SET BAL=BAL-@AMT WHERE ACNO=@ACC
COMMIT
PRINT 'ROW INSERTED AND UPDATED SUCCESSFULLY'
END
END
ELSE
PRINT 'INVALID TRANSACTION TYPE'
END
END
DYNAMIC QUERIES:
A query in a block is provided with input at the execution of a program,so that dynamically changes can be made to the query,and will generate the different output.
1. Retrieving the data from any table:
CREATE PROC RETTAB(@TN VARCHAR(12))
AS
BEGIN
EXEC ('SELECT * FROM '+@TN)
END
EXEC RETTAB EMP
EXEC RETTAB DEPT
2. Create a table using procedure with passing table names at runtime:
CREATE PROC CT(@TN VARCHAR(12))
AS
BEGIN
EXEC ('CREATE TABLE '+@TN+'(ENO INT,EN VARCHAR(12))')
END
EXEC CT E1
EXEC CT E2
3. Creating a table with table names, column names, data types dynamically?
CREATE PROC CT1(@TN VARCHAR(12),@COL VARCHAR(12),@DT VARCHAR(12))
AS
BEGIN
EXEC ('CREATE TABLE '+@TN+'('+@COL+' '+@DT+')')
END
EXEC CT1 'T1','ENO','INT'
EXEC CT1 'T2','RNO','SMALLINT'
4. Adding a new column in a table?
CREATE PROC AT1(@TN VARCHAR(12))
AS
BEGIN
EXEC ('ALTER TABLE '+@TN+' ADD NAME VARCHAR(12)')
END
EXEC AT1 T1
5. Altering the size of existing column:
CREATE PROC AT2(@TN VARCHAR(12))
AS
BEGIN
EXEC ('ALTER TABLE '+@TN+' ALTER COLUMN NAME VARCHAR(20)')
END
EXEC AT2 T1
6. Droping a table:
CREATE PROC DT(@TN VARCHAR(12))
AS
BEGIN
EXEC ('DROP TABLE '+@TN)
END
EXEC DT E1
EXEC DT E2
MODIFYING THE PROCEDURE:
ALTER PROC/PROCEDURE PROC_NAME([list_of_Args])
[WITH ENCRYPTION]
AS
BEGIN
STATEMENTS
END
DROPING THE PROCEDURE:
DROP PROC / PROCEDURE PROC_NAME
declare @a int,@b int,@c int,@d int
select @a=100,@b=20,@c=20,@d=@a*@b*@c
print @d
Here are some instruction when creating a store procedure to increase speed:
* Use SQL keyword in capital letters to increase readability.
* Use few possible variables to increase cache memory.
* Try to avoid dynamic queries if we are not using dynamic query
there is no recompilation of execution plan but on the other side
if we are using dynamic query every time we need recompile of plan.
* Use SET NOCOUNT ON this will helps us to get number of row
effected without network traffic.
* In Select and Set use select to assign value to variable
it is much faster than multiple set statement.
* In CAST and CONVERT always try to use CAST
* Avoid Distinct and Order by clause.These class needs extra space.
* Avoid cursor so use while loop for that and temparory tables.
* Avoid correlated sub queries.
* Avoid select * into for large tables it locks the system objects.
* Avoid temporary tables because it will recompile the procedure.
To display all predefined, userdefined stored procedures:
SP_STORED_PROCEDURES
To display all the created user defined procedure names:
SELECT NAME FROM SYSOBJECTS WHERE XTYPE='P'
To display the code of a user defined stored procedure:
SP_HELPTEXT 'proc_name'
To display the information about procedure:
SP_HELP 'proc_name'
sp_depends 'tablename'
iii)Extended Stored Procedures:
A stored procedure contains T-SQL statements and C/C++ code compiled into .dll file is called extended stored procedure.
Every extended stored procedure starts with XP_
All extended stored procedures are stored in MASTER database.
Extended procedures are used to perform operations in OS and Mail Server.
XP_CMDSHELL 'os command' ---- It is used to communicate
with operating system to execute commands.
To work with this extended procedure, it is essential that it should be enabled with the following procedure;
Start--->Programs--->Microsoft SQL Server 2005---->
Configuration Tools---->Sql Server Surface Area Configuration---->
Surface Area configuration for Features---->expand database engine---->
Select xp_cmdshell and then check to enable xp_cmdshell
Example:
EXEC XP_CMDSHELL 'MD C:\SQLS'
EXEC XP_CMDSHELL 'DIR C:\'
EXEC XP_SENDMAIL |
EXEC XP_READMAIL | to interact with mail server.
xp_cmdshell 'dir c:\'
xp_cmdshell 'md c:\peers'
how to check the drive free space?
xp_fixeddrives
No comments:
Post a Comment