================================================ SEARCHDATABASE.COM'S Database Developer Tips August 15, 2001 ================================================ Welcome to the searchDatabase.com Database Developer Tips newsletter! Today's tip, "Using stored procedures in SQL Server" can also be viewed online at: http://www.searchDatabase.com/tip/1,289483,sid13_gci761002,00.html Don't forget about our collection of over 200 DBA, developer, Oracle, DB2, and SQL Server tips FREE to searchDatabase members: http://searchdatabase.techtarget.com/tips/0,289484,sid13_tax281808,00.html ******** Storage Decisions 2001 *********** Storage Decisions 2001, an exclusive conference focused on storage, is brought to you by our sister site searchStorage.com. The event takes place in Chicago from September 26 through 28. Unlike any other storage conference, this event is FREE to searchDatabase.com members who manage storage in enterprise environments and face storage decisions in the upcoming months and year. Other events merely identify the issues you face, SD2001 tackles them head on with specific how-to-advice to set strategy, make key decisions and manage storage effectively. Benefit from the expertise of top storage analysts like Gartner Group's Nick Allen, seasoned experts like Steve Duplessie of ESG and storage technologists independent expert and best selling author John William Toigo. ************************************************* "Using stored procedures in SQL Server" By Barrie Sosinsky When you are leery of having users directly access tables or any other objects in your files, SQL Server lets you safeguard these objects by setting up stored procedures. While the system stored procedures are created automatically when you install SQL Server, and are used to interact with system tables and other administrative tasks. SQL Server lets you create user-defined stored procedures for any task that is comprised of multiple statements and conditions. The calling application can then execute the stored procedure instead of the whole set of statements individually. You can create the stored procedures by using the CREATE PROCEDURE statement. SQL Server will then store them in the current database. Each name must be unique within the database as well as unique to the user who creates them. You can also create stored procedures in the master database. Names created in the master database with the sp_ prefix can be accessed by any other database, a very handy feature. SQL Server will look for it first in the current database. If it's not there, it look in the master database when it is called by other databases. You can create and store procedures in the tempdb database. They will be automatically dropped by SQL Server unless you issue a DROP PROCEDURE statement. To create a temporary object use the # prefix for local and ## for global temporary stored procedures. These function the same way as other user-defined stored procedures except that they will be dropped when the connection that creates them is broken. A temporary stored procedure can be called from any database. When a stored procedure is created, its properties are stored in the sysobjects system table. All of its definitions will be stored in the syscomments system table. Since a stored procedure is stored in the current database you will have to create a stored procedure in other databases by making them the current database. This can be done by using the USE statement. After a user-defined stored procedure is created, you can view parameters and definitions using the sp_helptext system stored procedure. In addition, you can view its properties using sp_help. The main advantage of creating stored procedures is that they can be used to keep users from using tables directly. You must assign execute permission to users on the stored procedures. They are then able to run the stored procedure without having direct permissions on the table or object that is referenced by the stored procedure. This keeps your tables and objects safe. ABOUT THE AUTHOR Barrie Sosinsky ([EMAIL PROTECTED]) is president of consulting company Sosinsky and Associates (Medfield MA). He has written extensively on a variety of computer topics. His company specializes in custom software (database and Web related), training and technical documentation. SUBMIT YOUR OWN TIP AND WIN FREE TECHNICAL BOOKS! http://searchdatabase.techtarget.com/tipsPrize/0,289492,sid13_prz520733_cts520732,00.html ================================================ MORE GREAT STUFF ON SEARCHDATABASE.COM! ================================================ Featured Topic this week: "Database performance tuning" http://searchdatabase.techtarget.com/featuredTopic/0,290042,sid13_gci760320,00.html A slow database is not only annoying to users, it can also affect your company's bottom line. Our collection of tuning tips, tutorials, and expert advice will help you achieve maximal speed and reliability for your DBMS. Let's hear your feedback in the forums! What do you think of the recent MySQL fuss? Will it drive people away from the burgeoning open-source movement? Do any of you use open-source for mission-critical applications anyway? http://searchdatabase.techtarget.com/forums/0,289802,sid13_fid1,00.html Over 200 DBA, developer, Oracle, DB2, and SQL Server tips FREE to searchDatabase members: http://searchdatabase.techtarget.com/tips/0,289484,sid13_tax281808,00.html NEW: Industry gadfly Fabian Pascal's latest column about XML (part III in a series): http://searchdatabase.techtarget.com/tip/0,289483,sid13_gci759453,00.html Pascal writes: "If XML is just a physical format for data exchange, why does it incorporate logical constructs such as data model components?" Hundreds of hand-picked and reviewed resources about every aspect of database development, including Java, SQL, ASP, PERL, and much more: http://searchdatabase.techtarget.com/bestWebLinks/0,289521,sid13_tax281563,00.html Ask the Expert category of the day: "SQL" http://searchdatabase.techtarget.com/ateAnswers/0,289620,sid13_cid376817_tax285649,00.html SQL guru extraordinaire Rudy Limeback is waiting to tackle your toughest SQL scripting problems. Three archived audio presentations with leading experts are now available: * "Data mining for fun and profit," by Shaku Atre http://searchdatabase.techtarget.com/onlineEventsTranscriptSecurity/1,289693,sid13_gci754454,00.html * "Database replication," by Mike Hotek http://searchdatabase.techtarget.com/onlineEventsTranscriptSecurity/1,289693,sid13_gci749642,00.html * "SQL for Smarties," by Joe Celko http://searchdatabase.techtarget.com/onlineEventsTranscriptSecurity/1,289693,sid13_gci558024,00.html ======================================= LEARNING ZONE FEATURED BOOK OF THE WEEK ======================================= "Oracle 9i SQLJ Programming" By Nirva Morisseau-Leroy Take advantage of greater programming capabilities with SQLJ--a standard language specification that embeds SQL statements in Java--in the Oracle 9i environment. Officially authorized by Oracle Corporation, this authoritative resource explains how to develop hosted applications, achieve remarkable scalability, implement real-time business intelligence features, and reach a new level of programming functionality. Get comprehensive coverage of SQLJ programming techniques, including SQL programming and development, SQLJ stored programs, triggers, and object-relational processing. Oracle9i SQLJ Programming will help you control each piece of the development process. Discover the benefits of Oracle's highly developed SQL-embedded Java programming standard. http://www.digitalguru.com/dgstore/product.asp?isbn=0072190930&ac_id=58 ================================================ SUBMIT A TECHNICAL TIP AND WIN A FREE BOOKS! ================================================ Do you have a time-saving shortcut, trick, or script that you want to share with other database pros? The first fifty individuals who submit a tip will receive a free searchDatabase.com hat. The highest rated tips each month will win our "Tip of the Month" contest and receive a high-quality searchDatabase.com denim shirt AND a free book of your choice from Wrox Press. We're accepting short, focused tips or code snippets on topics of interest to DBA's and database developers, such as Oracle, DB2, SQL Server, database design, SQL, performance tuning, etc. Click here for more info and to submit your tip: http://searchdatabase.techtarget.com/tipsPrize/0,289492,sid13_prz520733_cts520732,00.html This will be a great way to share your knowledge, cement your status as an industry expert, and maybe win a prize. Send us your tip today! ================================================ If you would like to sponsor this or any TechTarget newsletter, please contact Gabrielle DeRussy at [EMAIL PROTECTED] ================================================ If you no longer wish to receive this newsletter simply reply to this message with "REMOVE" in the subject line. Or, visit http://searchDatabase.techtarget.com/register and adjust your subscriptions accordingly. If you choose to unsubscribe using our automated processing, you must send the "REMOVE" request from the email account to which this newsletter was delivered. Please allow 24 hours for your "REMOVE" request to be processed.
