Paladin Consultants, LLC

"Good Information is just good business"

JA slide show
Home Resources Interface with Stored Procedures
Print E-mail

Stored Procedures are, in my opinion the value added to SQL Server which that makes it an excellent choice for a database for applications of almost any size. The ability to deal with parameters make s these objects flexible enough to be used as tools and re-used, rather than built as one-off items and stored in a large library. Parameters make coding much more efficient because one routine, through the use of passed parameters, can be designed to run multiple tasks.

To begin with, a sproc can handle up to 2100 parameters, which can be passed by position or by name (by name documents best). Inputs can be passed only as variables, or constants – no functions. And you can specify default parameters in the stored procedure, which are used when the keyword DEFAULT is specified in the calling command. Object names can be passed as parameters, but they must be used in a WHERE clause, or passed as a string to be used in a dynamically created SQL statement within the stored procedure.

Wild card (‘%') can be used as a parameter, in which it will behave as a default.

Output parameters are defined by the keyword OUTPUT. When a sproc is called with an OUTPUT parameter the keyword OUTPUT must appear in the calling command as well.

In addition to OUTPUT parameters, stored procedures are also able to return as status code to signal to the calling procedure and to know whether the outcome was is successful or not. A ‘)' value is returned when the execution was is successful, while other system return codes are returned for a variety of ills. In addition , you can explicitly return a value of less than -99 to signal a user-defined conclusion.

Cursors produced in stored procedures can be either local or global. Globals (the default choice) are available until the connection to the server is closed, or until they are specifically deallocated. Locals are available only until the end of the stored procedure in which they were created. These cursors may then be set into a variable or parameter of cursor type. Cursor data types used as output must be declared using the VARYING keyword in addition to the CURSOR and OUTPUT keywords.

Stored procedures may be nested up to 32 levels deep, and may be called recursively, again, up to 32 levels. When not in a nested configuration, there is no limit to the number of stored procedures that can be called from another stored procedure.

In stored procedures you can create temporary tables (tables whose names are prepended with an ‘#') to store intermediate values. Tables whose names begin with ‘##' are global temporary tables, which are available to all users. The more useful are the local tables, the ones with a single hash mark. These local tables go out of scope and are destroyed at the end of the stored procedure which that created them. Global tables hand around until they are specifically DROPped, or until the user who created it disconnects from SQL Server.

Temporary tables are to be used sparingly because all users share the same tempdb for temporary tables. Therefore heavy usage of temporary tables may result in some performance issues. The actual creation of the temporary database will result in locks being placed on the tempdb until the SQL creating the temporary table completes.

An alternative to the temporary table is the table variable, which is initiated via a DECLARE @variablename TABLE ({column definition | tableonstraint}) statement. Table variables exist in memory, and not in the tempdb, are more efficient than temporary tables. Their scope is limited to the procedure in which they were created. They cannot be used as parameters, either input or output. And you cannot create an index on them, though a primary key can be defined. Though stored procedures cannot return table variables, functions can.

Resources

Information accessing stored procedures

This is a useful resource that discusses accessing stored procedure at length.

Tutorial: Using Stored Procedure

This is a insightful tutorial on stored procedures.

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