Basic operations of stored procedures (SQL Server Management studio)

Recently, I used vs2008 + SQL Server 2005 for development. To use stored procedures that have not been used before, I found some basic operations on the stored procedure and kept files for use.

Create a stored procedure

1. In the object Resource Manager, connect to the SQL Server 2005 database engine instance and expand the instance.

2. Expand"Database"The database to which the stored procedure belongs and"Programmable".

3. Right-click"Stored Procedure"And then click"Create a stored procedure".

4. In"Query"Click"Parameter value of the specified template".

5. In"Parameter value of the specified template"Dialog box,"Value"The column contains the recommended parameter values. Accept these values or replace them with new values, and then click"OK".

6. In the query Editor, replace the SELECT statement with a procedure statement.

7. To test the syntax, go"Query"Click"Analysis".

8. To create a stored procedure, go"Query"Click"Execution".

9. To save the script, go"File"Click"Save". Accept the file name or replace it with a new name, and then click"Save".

Example of creating a stored procedure

1. In the object Resource Manager, connect to the SQL Server 2005 database engine instance and expand the instance.

2. Expand"Database",AdventureworksDatabases and"Programmable".

3. Right-click"Stored Procedure"And then click"Create a stored procedure".

4. In"Query"Click"Parameter value of the specified template".

5. In"Parameter value of the specified template"In the dialog box, enter the following parameter values.

   Parameters	Value
   Author	Your name
   Create date	Today's date
   Description	Return employee data.
   Procedure_name	HumanResources. uspgetemployees
   @ Param1	@ Lastname
   @ Datatype_for_param1	Nvarchar(50)
   Default_value_for_param1	Null
   @ Param2	@ Firstname
   @ Datatype_for_param2	Nvarchar(50)
   Default_value_for_param2	Null

6. Click"OK".

7. In the query Editor, replace the SELECT statement with the following statement:

       SELECT FirstName, LastName, JobTitle, Department    FROM HumanResources.vEmployeeDepartment    WHERE FirstName = @FirstName AND LastName = @LastName;

8. To test the syntax, go"Query"Click"Analysis". If an error message is returned, compare the statements with the preceding information and make corrections as needed.

9. To create a stored procedure, go"Query"Click"Execution".

10. To save the script, go"File"Click"Save". Enter a new file name, and then click"Save".

11. To run the stored procedure, click"New query".

12. In the query window, enter the following statements:

    USE AdventureWorks;GOEXECUTE HumanResources.uspGetEmployees @FirstName = N'Diane', @LastName = N'Margheim';GO

13. In"Query"Click"Execution".

Modify Stored Procedure

1. In the object Resource Manager, connect to the SQL Server 2005 database engine instance and expand the instance.

2. Expand"Database"The database to which the stored procedure belongs and"Programmable".

3. Expand"Stored Procedure", Right-click the process to be modified, and then click"Design".

4. Modify the Stored Procedure text.

5. To test the syntax, go"Query"Click"Analysis".

6. To modify the stored procedure, go"Query"Click"Execution".

7. To save the script, go"File"Click"Save". Accept the file name or replace it with a new name, and then click"Save".

Rename a stored procedure

1. In the object Resource Manager, connect to the SQL Server 2005 database engine instance and expand the instance.

2. Expand"Database"The database to which the stored procedure belongs and"Programmable".

3. Determine the dependency of the stored procedure.

4. Expand"Stored Procedure", Right-click the process to rename, and then click"RENAME".

5. Modify the name of a stored procedure.

6. Modify the name of a stored procedure referenced in the relevant object or script

 

Many system stored procedures, system functions, and directory views provide information about stored procedures. You can use these system stored procedures to view the definition of the stored procedure, that is, the transact-SQL statement used to create the stored procedure. This may be useful if no Transact-SQL script file is used to create a stored procedure.

Use
SYS. SQL _modules

1. In the object Resource Manager, connect to the SQL Server 2005 database engine instance and expand the instance.

2. On the toolbar, click"New query".

3. In the query window, enter the following statements. Change the Database Name and stored procedure name to reference the required database and stored procedure.

   Copy

   USE AdventureWorks;GOSELECT definitionFROM sys.sql_modulesWHERE object_id = (OBJECT_ID(N'AdventureWorks.dbo.uspLogError'));

Use
Object_definition

1. In the object Resource Manager, connect to the SQL Server 2005 database engine instance and expand the instance.

2. On the toolbar, click"New query".

3. In the query window, enter the following statements. Change the Database Name and stored procedure name to reference the required database and stored procedure.

   Copy

   USE AdventureWorks;GOSELECT OBJECT_DEFINITION (OBJECT_ID(N'AdventureWorks.dbo.uspLogError')); 

Use
Sp_helptext view Stored Procedure Definition

1. In the object Resource Manager, connect to the SQL Server 2005 database engine instance and expand the instance.

2. On the toolbar, click"New query".

3. In the query window, enter the following statements. Change the Database Name and stored procedure name to reference the required database and stored procedure.

   USE AdventureWorks;GOEXEC sp_helptext N'AdventureWorks.dbo.uspLogError';

View the dependency of a stored procedure

 
 
 

  
  
In the object Resource Manager, connect to the SQL Server 2005 database engine instance and expand the instance.
  
  
Expand"Database"The database to which the stored procedure belongs and"Programmable".
  
  
Expand"Stored Procedure", Right-click the process, and then click"View dependency".
  
  
View the list of objects dependent on stored procedures.
  
  
View the list of objects that a stored procedure depends on.
  
  
Click"OK".
 
 
 
Delete stored procedure

 
 
 
 

  
  
In the object Resource Manager, connect to the SQL Server 2005 database engine instance and expand the instance.
  
  
Expand"Database"The database to which the stored procedure belongs and"Programmable".
  
  
Expand"Stored Procedure", Right-click the process to be deleted, and then click"Delete".
  
  
To view stored procedure-based objects, click"Show dependency".
  
  
Confirm that you have selected the correct stored procedure, and then click"OK".
  
  
Delete the stored procedure name from the dependent object and script.
This topic describes how to use the object Resource Manager in SQL Server Management studio to grant Stored Procedure permissions. You can grant permissions to existing users, database roles, or application roles in the database.
 
 
 
Grant Stored Procedure Permissions
 Grant Stored Procedure Permissions
 
 

  
  
In the object Resource Manager, connect to the SQL Server 2005 database engine instance and expand the instance.
  
  
Expand"Database"The database to which the stored procedure belongs and"Programmable".
  
  
Expand"Stored Procedure", Right-click the process for which you want to grant permissions, and then click"Attribute".
  
  
In"Stored Procedure attributes", Select"Permission"Page.
  
  
To grant permissions to a user, database role, or application role, click"Add".
  
  
In"Select User or role", Click"Object type"To add or remove users and roles.
  
  
In"Explicit permission"In the grid, select the permissions to be granted to the specified user or role. For more information about permissions, see permissions.
Select"Authorize"Indicates that you want to grant the specified permissions to the grantee. Select"Permission granted"Indicates that the authorized person can grant the specified permission to another subject.