SQL Server stored procedures provide you with a powerful mechanism to perform routine database maintenance. There are different ways to create and launch stored procedures. For details, refer to your Microsoft SQL Server documentation.
To create a stored procedure, run Microsoft SQL Server Query Analyzer. Select the SQL Server you want. The Query Window is displayed. Specify the database you want, and supply the query text. Click Parse Query to validate the query text, if necessary.
Execute the query. The procedure you entered will be created on the SQL Server. If there are no errors, the The command(s) completed successfully message is displayed. Otherwise, check user permissions. At this point, you can quit Query Analyzer.
The next time you start Query Analyzer, just enter exec PROCEDURE_NAME to run the procedure.
You can schedule your procedure to run recurrently. Run SQL Server Enterprise Manager, locate the Server you want, select Management, and go to the SQL Server Agent. Check if the SQL Server Agent is active. If it is not active, select Start. Point to Jobs and select New Job. In the space provided, supply the job Name. Check if the Enabled option is selected. If the Enabled option is not selected, your job won't be launched.
Select the Steps tab and click New. Supply the Step Name and set the Type to TSQL (default). Specify the database you want, and in the Command field enter exec PROCEDURE_NAME. Specify the parameters, if necessary. Go to the Schedules tab and click New to add a schedule. Modify the schedule, if necessary. If you want notifications, supply the necessary parameters on the Notifications tab.