Paladin Consultants, LLC

"Good Information is just good business"

JA slide show
Home Resources SQL Server Stored Procedures
Print E-mail

        
[ VARYING ] [ = default ] [ OUTPUT ]
    
] [ , ... n ]

[ WITH
    { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

[ FOR REPLICATION ]

AS sql_statement [ ... n ]

Stored Procedures (Sprocs) are executed either by invoking it by name, and or supplying the required parameters (if any). If the procedure is not the first command in a batch, you must precede the procedure name with ‘EXEC'. The reason for this requirement is that in batch mode, SQL Server looks for statements by finding keywords.

A stored procedure at creation time may reference a table which does not exist, but not another stored procedure refer a table, which does not exist. However, it cannot refer a stored procedure that does not exist at all. At rMay 23, 2006ge will be issued. You can get a list of sprocs which reference a specific table or objects referenced by a specific sproc by issuing the command

sp_depends (table name|procedure name)

And you can also use the Enterprise Manager by right-clicking the object and choosing Display Dependencies in the All Tasks Menu.

Sprocs can be viewed from the Enterprise Manager by right-click, Properties, by querying the system table, syscomments, or by using the system stored Procedure, sp_helptext followed by the name of the stored procedure you wish to view. The best way to edit the stored procedure is through the Enterprise Manager, but they can be edited programmatically using the ALTER PROCEDURE command:

ALTER PROC [ EDURE ] procedure_name [ ; number ]
    [ { @ parameter data_type }
        [ VARYING ] [ = default ] [ OUTPUT ]
    ] [ , ... n ]
[ WITH
    { RECOMPILE | ENCRYPTION
        | RECOMPILE , ENCRYPTION
    }
]
[ FOR REPLICATION ]
AS
     sql_statement [ ... n ]

An efficient way to create or edit a stored procedure is to use the Query Analyzer, which can create a script for dropping an existing sproc, and creating a new one. The new one can be created using the View editor to click and drag to create a SELECT statement. Then that SQL statement can be cut and pasted into the Query Analyzer, where it can then be tested and debugged.

Stored procedures can be debugged in the Query Analyzer by opening the object browser and right clicking on a stored procedure and choosing Debug.

 

Resources

•  Information on NCSU ACS Guidelines for Stored Procedures

This resource discusses NCSU ACS guidelines for stored procedures.

•  Information on embedded SQL, library SQL and stored procedures in Sybase

This resource discusses information on embedded SQL and stored procedure in Sybase.

Metro NY / NJ SQL Server Consultants

We specialize is custom database software. Call us for a free consultation (973) 635 0080

 

Computer Consultants: Database Development
Computer Programming: Web Site Services
Computer Programming: Custom Software
Computer Consulting: IT Consulting
Paladin Consultants, LLC Home Page
Computer Consulting: Contact Us

 

 
Bookmark and Share