Return Values: Getting Something Back from Your SQL Server Stored Procedures

Appendix:  Sample Database DDL

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'ArticleTestDB')

            DROP DATABASE [ArticleTestDB]

GO

CREATE DATABASE [ArticleTestDB]  ON (NAME = N'ArticleTestDB_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\ArticleTestDB_Data.MDF' , SIZE = 1, FILEGROWTH = 10%) LOGON (NAME = N'ArticleTestDB_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\ArticleTestDB_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)

 COLLATE SQL_Latin1_General_CP1_CI_AS

GO

exec sp_dboption N'ArticleTestDB', N'autoclose', N'false'

GO

exec sp_dboption N'ArticleTestDB', N'bulkcopy', N'false'

GO

exec sp_dboption N'ArticleTestDB', N'trunc. log', N'false'

GO

exec sp_dboption N'ArticleTestDB', N'torn page detection', N'true'

GO 

exec sp_dboption N'ArticleTestDB', N'read only', N'false'

GO

exec sp_dboption N'ArticleTestDB', N'dbo use', N'false'

GO

exec sp_dboption N'ArticleTestDB', N'single', N'false'

GO

exec sp_dboption N'ArticleTestDB', N'autoshrink', N'false'

GO

exec sp_dboption N'ArticleTestDB', N'ANSI null default', N'false'

GO

exec sp_dboption N'ArticleTestDB', N'recursive triggers', N'false'

GO

exec sp_dboption N'ArticleTestDB', N'ANSI nulls', N'false'

GO

exec sp_dboption N'ArticleTestDB', N'concat null yields null', N'false'

GO

exec sp_dboption N'ArticleTestDB', N'cursor close on commit', N'false'

GO

exec sp_dboption N'ArticleTestDB', N'default to local cursor', N'false'

GO

exec sp_dboption N'ArticleTestDB', N'quoted identifier', N'false'

GO

exec sp_dboption N'ArticleTestDB', N'ANSI warnings', N'false'

GO

exec sp_dboption N'ArticleTestDB', N'auto create statistics', N'true'

GO

exec sp_dboption N'ArticleTestDB', N'auto update statistics', N'true'

GO

if( ( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) ) or ( (@@microsoftversion / power(2, 24) = 7) and (@@microsoftversion & 0xffff >= 1082) ) )

            exec sp_dboption N'ArticleTestDB', N'db chaining', N'false'

GO

use [ArticleTestDB]

GO

About the Author

Tom O'Neill is a Senior Consultant in the Solutions/IDI practice at Deloitte & Touche. Tom's areas of expertise center web application development, database architecture, and marketing automation. Tom O’Neill can be reached at 617-437-2945 or at tomoneill@deloitte.com.

Solutions/IDI, of Deloitte & Touche, is a technology consulting practice that provides various technology and IT-related services worldwide.

Published with the express written permission of the author. Copyright 2003.