Programming Considerations

This section describes some programming considerations that you will need to keep in mind when writing Open Data Services routines that users will access as extended stored procedures in SQL Server (as opposed to routines that users will access on an Open Data Services server).

When standard stored procedures are added to SQL Server, an administrator defines their expected parameters so that the system can validate user-specified parameters and return syntax errors. With extended stored procedures, it is up to the application to validate any user-specified parameters.

When using the srv_senddone function to send a results completion message to the client, always set the completion status flag to SRV_DONE_MORE instead of to SRV_DONE_FINAL. The client receives SRV_DONE_FINAL from SQL Server.

Each extended stored procedure function that you create should follow these recommendations for accepting and responding to user input:

1. Check that the caller of the procedure has provided all of the required parameters and that each parameter is of the appropriate datatype. Return an appropriate message if not.
2. Define the columns for returning a result set.
3. Create each record for returning to the caller.
4. Set up any output parameters and return statuses used by the procedure.
5. When finished returning results, send the results completion message using srv_senddone with the SRV_DONE_MORE status flag.
6. Return from the procedure with the desired Transact-SQL return status.

A single DLL file can contain multiple extended stored procedures. And SQL Server supports the use of multiple DLL files.