Showing posts with label Stored Procedures. Show all posts

System Stored Procedures in SQL Server

Posted on Wednesday, 30 May 2012 by SQL Developer

Posted in | Leave a comment

sp_rename

by SQL Developer

sp_rename is a system stored procedure which is used to rename the database objects in sql server.

Using sp_rename any database object (table, stored procedure, view, column etc.,) can be rename .

Syntax for sp_rename is:

sp_rename <old database object Name> , <New database object Name>

                                         (or)

sp_rename @objname = <object old name>,
                @newname =<object new name>,
                @objtype = <Type of the database object>

@objtype in the above syntax represents database object type . By default this is NULL.


Example to Rename database 'Example' to 'Sample' following query can be used.

sp_rename 'Example','Sample','DATABASE'







Posted in | Leave a comment

sp_password

by SQL Developer

sp_password is a system stored procedure which is used to change the password.

syntax for sp_password is :

sp_password <old password > , <New Password > 

                               (or)

sp_password @old = <old password >, @new =  <New Password>

Example:

sp_password 'MY_OLD_PWD','MY_NEW_PWD'

Posted in | Leave a comment

sp_stored_procedures

by SQL Developer


sp_stored_procedures is a system stored procedure which will result in all the stored procedures that are present in a particular database.

That is sp_stored_procedures results in all system stored procedures, user defined stored procedures and extended stored procedures information in a database.

Syntax for sp_stored_procedures is:

sp_stored_procedures

Example :

Following picture represents the execution result  of sp_stored_procedures stored procedure in EXAMPLE database.





Posted in | Leave a comment

sp_tables

by SQL Developer

sp_tables is a system stored procedure which will result in all the table names and view names in a particular database:

Syntac for sp_tables is :

sp_tables

Example:

I executed sp_tables stored procedure in example database. Below picture represents the execution result for this.





Posted in | Leave a comment

sp_datatypeinfo

by SQL Developer

sp_datatypeinfo is a system stored procedure which result in all data types that are accepted by you sql server version on their corresponding data types, precision and etc, information.

syntax for sp_datatypeinfo is:

sp_datatypeinfo <data type>

If we need data type information for all the data types in sql server syntax will be:

sp_datatypeinfo

Example:

Following picture shows the result of sp_datatypeinfo


Posted in | Leave a comment

sp_databases

by SQL Developer

sp_databases is a system stored procedure which will return all the databases in a SERVER along with the database sizes:

syntax for sp_databases stored procedure is:

  • sp_databases : This will return all databases information.
  • sp_databases <database Name >: This will return only 1 particular Database information.

Example:

Following picture represents the database information for all the databases in SQL Server .


 


Posted in | Leave a comment

sp_depends

by SQL Developer

sp_depends is a system stored procedure which will be used to find out the dependent objects of a particular database object.

Syntax for sp_depends is:

sp_depdens <database object Name>

Example:

Here is an example for sp_depends in case of stored procedure:

Syntax foe finding out dependent database objects on a stored procedure is :

sp_depends < stored procedure Name>

This will return all the tables, stored procedures, function , views etc., database objects which are used to build stored procedure.

Below picture shows the dependent database object on USP_CLASS_OF_STUD stored procedure:

 



Posted in | Leave a comment

sp_helptext

by SQL Developer

sp_helptext is a system stored procedure in sql server which is used to find the definition or structure of a stored procedure.

syntax for sp_helptext is:

sp_helptext <stored procedure Name>


Example:

Here is an example for the usage of sp_helptext to find out the definition of USP_CLASS_OF_STUD stored procedure.


 



Posted in | Leave a comment

sp_help

by SQL Developer

sp_help is a system stored procedure in sql server which is used to find the structure of the database objects.

syntax for sp_help is :

sp_help < database object Name>

Example : sp_help will retun all the following information in case  tables:
  1. Table Name , database owner and table created time.
  2. columns and their corresponding data types 
  3. Identity column if any
  4. constraints like primary key etc if any.
Below picture shows the execution result of sp_help system stored procedure for tableSTUDENT.



In case of stored procedures sp_help will return the following information:
  • Stored procedure , database object owner and procedure created time
  • Input and output parameters used in stored procedure if any.
Following picture represents the output of the sp_help for a stored procedure:




Posted in | Leave a comment

sp_addserver

Posted on Friday, 25 May 2012 by SQL Developer

sp_addserver is a system stored procedure which is used to add a server in sql server.

Syntax for sp_addserver is:

sp_addserver 'Server New Name', 'Actual Server'

sp_addserver is generally used to rename a server.


Posted in | Leave a comment

sp_dropserver

by SQL Developer

sp_dropserver is a system stored procedure which is used to drop a server from a sql server.

Syntax for this system stored procedure is:

sp_dropserver <Server Name>

This stored procedure is generally used to rename the server.

Posted in | Leave a comment

sp_helplanguage

by SQL Developer

sp_helplanguage is a system stored procedure which will display a;; the languages supported by the sql server version that is installed on your machine.




Posted in | Leave a comment

System Stored Procedures

by SQL Developer

Many administrative activities are performed inside the sql server using system stored procedure.

Following are some of the frequently used system stored procedures in sql server:




Posted in | Leave a comment

What are the checks performed by the SQL Server while compiling stored procedures?

by SQL Developer

SQL Server will parse the queries that are present in stored procedure and checks for the following:
  • Check whether all the queries are syntactically correct or not.
  • Check whether all the tables listed in stored procedures exists in database or not. If some tables are internally created as part of stored procedure, then temporary tables will be created fro time being and these tables will be used while compiling the query. These tables are not permanent. They will not be present in the database after stored procedure compilation. This type of compilation is known as Deferred Name Resolution.
  • Check whether all the columns which are used in stored procedure exists in data base in appropriate tables.




Posted in | Leave a comment

Stored procedures with input and output parameters

by SQL Developer

Stored procedures can be used to return values. Stored procedures can return int, char, varchar etc., data type values. But, then can not return table variables.

Syntax for the stored procedures with input and output parameters is:

           CREATE PROC <Procedure Name>
           @Input_Var     <Data Type>,
           @Output_Var  <Data Type>  OUTPUT
           AS
           BEGIN
                      <T-SQL Commands>
           END

Note:  Any number of input and output commands can be passed to the stored procedure till it reaches maximum parameters limit.

Example: Let’s create a stored procedure to find out to which class a student belongs to if student id is provided.

In this scenario student Id will be an input parameter and class will be output parameter.

Stored procedure:

           CREATE PROC USP_CLASS_OF_STUD
           @STUDENT_ID INT,
           @CLASS_ID   INT OUTPUT
           AS
           BEGIN
                      SELECT @CLASS_ID = CLASS_ID  
                       FROM STUDENT_CLASS  
                      WHERE STUDENT_ID      =@STUDENT_ID


                      RETURN @CLASS_ID
           END


Execute the above stored procedure to find out the class of a student whose id is 11.

DECLARE @CLASS INT
EXEC USP_CLASS_OF_STUD 11, @CLASS OUTPUT
SELECT @CLASS



Posted in | Leave a comment

Stored procedures with input parameters and without output parameters

by SQL Developer

Input parameters can be passed to stored procedures as and when required. Here is the syntax for the stored procedure with input parameters and without output parameters.

Syntax:

              CREATE PROC <Procedure Name>
              @Input1 <Data type>,
              @Input2 <Data type>,
 
              @InputN <Data type>,
               AS
               BEGIN
                          <T-SQL commands>
               END

Here is an example for the stored procedure with input parameters. In this Scenario, I would like to know the students who are in a particular class.
For this case, we will be passing Class Id as input parameter.

Stored procedure for the above scenario is:

Stored Procedure:

              CREATE PROC USP_STUDENTS_IN_CLASS
              @CLASSS_ID  INT
              AS
              BEGIN
                   --Select the student deatils whose class id is @CLASSS_ID
                   SELECT
                          S.STUDENT_ID,
                          S.FIRST_NAME,
                          S.LAST_NAME
                   FROM STUDENT      S
                   INNER JOIN STUDENT_CLASS      SC
                          ON    S.STUDENT_ID =SC.STUDENT_ID
                          AND SC.CLASS_ID= @CLASSS_ID
              END

Query to fetch the students who are in 5th class, following query can be executed. That is following query will be used to run the above stored procedure with the input parameter 5.


              EXEC USP_STUDENTS_IN_CLASS 5






Posted in | Leave a comment

Stored procedures without input and output parameters

by SQL Developer

Stored procedures can be created without input parameters. Here is the syntax for the stored procedure without input parameters.

                   CREATE PROC < Procedure Name>
                   AS
                   BEGIN
                         < T-SQL Commands>
                   END

Here is a simple stored procedure to retrieve data from STUDENT table:


CREATE PROC STUDENT_DATA
AS
BEGIN
      SELECT * FROM STUDENT
END

 Uses of creating stored procedure in this scenario is:

  • Instead of running the entire set of T-SQL Commands which are inside the stored procedure, we can just run the stored procedure using the following syntax to get the same result.
 
Result obtained by running the T-SQL Commands inside the stored procedure:



Same result should be obtained even by running the stored procedure.

Here is the syntax to Execute a stored procedure:


Syntax:

EXEC <Procedure Name>

 Result obtained by running Stored procedure is :



Posted in | Leave a comment

Advantages and Disadvantages of Stored Procedures

by SQL Developer

Stored procedures are special Sql server objects which will be stored in database.

Advantages of  stored procedures:

  • Script re-usability: Script complied once can be reused as many times as needed.  
  • Execution Plan Retention and Re-usability:  Execution plan will be stored in the database for a stored procedure and same plan will be executed when ever a stored procedure runs.
  • Encapsulation of complete business logic in a unit:  Stored procedure encapsulates the complete process business logic inside the stored procedure. Instead of executing all the queries, it’s easy and safe to execute the stored procedure.
  • Safety and Security: Stored procedure can be granted with access. This will allow only intended resources to utilize or run the stored procedure. Security feature in stored procedures shield the user from directly accessing the tables in a database.
  • Easier to call from external application:  Stored procedures can be easily invoked from external applications like C#.net , Asp.Net and web services.
  • Client- Server traffic will be reduced by using stored procedures since this will use lesser bandwidth compared to the queries.
  • Temporary stored procedures are also supported in SQL Server. This feature will allow stored procedures to be created in temp db just like temporary tables. That is # and ## stored procedures can be created in SQL SERVER. They will automatically be dropped when you disconnect from the server.
  • Simple and Easy to use: When a process needs to be executed or invoked from some external application, we need not transfer all sql queries involved in the process. Instead we can just call the stored procedure.

Disadvantages of stored procedures:

  • Execution plan of the stored procedure is cashed.  Same Execution plan will be used and executed whenever a stored procedure is executed. Some times old execution plan may not be using the latest indexes. In those cases execution plan will not be correctly optimized. This will hinder the performance of the stored procedure.

Posted in | Leave a comment

Stored Procedures in SQL Server

by SQL Developer

As the Name implies stored procedures are data base objects which store the business logic or procedure in a complied script. These are special data base objects. They are pre-compiled T-SQL statements/queries.

Entire business logic of a process can be encapsulated in a stored procedure and this can be used when ever needed. This will reduce the overhead of writing the same queries again and again. Executing stored procedures is comparatively faster than executing the queries independently. Also, stored procedures use less bandwidth. That is the reasons stored procedures are most preferred for the cross applications.

Syntax for the stored procedures is:

CREATE PROCEDURE <Procedure Name>
<Input Parameters comma separated> , <Output parameters comma separated>
AS
BEGIN
< T-SQL Statements >
END

Stored procedures can be classified broadly into 3 categories:

  1. System stored procedures
  2. User Defined stored procedures
  3. Extended stored procedures

User defined stored procedures are again classified into the following categories:

  1. Stored procedures without input and output parameters (Simple stored procedures)
  2. Stored procedures with input parameters and without output parameters
  3. Stored procedures with input and output parameters.

We will discuss in detail about all these stored procedure types in the coming sessions.

Posted in | Leave a comment