Sending Data to Stored Procedures


As an application developer or a DBA proficient in T-SQL, you need to know how to write software that can pass data (parameters) to stored procedures. Stored procedure writers, on the other hand have to know how to code stored procedures to efficiently receive and process the data received from the clients. The architecture is very similar to that of functions, procedures, and methods that receive and return values. Stored procedures can also return values, and we will discuss how and what in the next section.

When a client calls a stored procedure (using one of the methods described in the earlier section), it can pass values to the procedure through parameters. These values are then used in the stored procedure to achieve the desired result. I mentioned earlier that you can write stored procedures that can contain as many as 1,024 parameters. To expand further, each parameter can be named, associated with a data type, given a direction, and even assigned a default value (even NULL).

You can pass parameters by name and by position. The following example demonstrates passing parameters by name:

 EXEC js_newid @id1='A-1A', @id2='A-1B'

This is the more robust form of coding stored procedures because it means you can use the named parameters in a stored procedure without defining the order in which the parameters were received. The following code will get hairy if you start going wild on parameters:

 EXEC js_newid 'A-1A', 'A-1B', 'A-1C', 'A-1D'

Default Values in Stored Procedures

When you execute stored procedures that are expecting parameters, SQL Server will report an error if none is received in your connection. If your connection might not transmit a parameter, you could code default values in the stored procedure, or even use NULL. SQL Server will then make use of the default values or place NULL into the statement’s parameter placeholder instead of returning an error.

In keeping with our discussion of NULL values in the last chapter, your first choice should be to provide a default value in the parameter that makes the most sense and will enhance rather than break your code. For example, the following procedure will use your parameter values, or a default parameter:

 EXEC js_getdiscount @discount=0, @coupon=1

The following example takes NULL when and if the circumstances can live with it:

 EXEC js_getdiscount @discount=0, @coupon=NULL

The following code is a simple order-adding stored procedure demonstrating the use of parameters:

 USE rainbow   DECLARE @returncode int   DECLARE @CustID char (8)   DECLARE ©AgentID char (6)   DECLARE @Item char (50)   DECLARE @Price money, @Discount real, @Quantity int INSERT [Orders] (CustID, AgentID, Item, Quantity, Price, Discount)   VALUES (@CustID, ©AgentID, @Item, @Quantity, @Price, @Discount)

This stored procedure is kept simple to demonstrate the use of parameters. Notice that you can also group parameter declarations behind one DECLARE. The following list provides some parameter tips:

  • Use default values in your parameters in preference to NULL. Use the NULL value when it makes sense.

  • Write code that checks for inconsistent or missing values early in your stored procedure code.

  • Use easy-to-remember names that make it easier to pass the value by name rather than position.




Microsoft SQL Server 2005. The Complete Reference
Microsoft SQL Server 2005: The Complete Reference: Full Coverage of all New and Improved Features
ISBN: 0072261528
EAN: 2147483647
Year: 2006
Pages: 239

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net