Sql Stored Proc

In: Computers and Technology

Submitted By mayansingh
Words 1521
Pages 7
SQL_StoredProcBasics Updated 10/05/2001

BUSINESS & COMPUTERS, Inc.
13839 Mur-Len Rd, Suite M OLATHE, KANSAS 66062
We Translate Business Processes from the Mind to the Computer to the Bottom Line.

Phone: (913) 764-2311 Fax: 764 7515 larryg@kcnet.com

Stored Procedures for SQL Server The Basics
Copyright® 2001 Business & Computers, Inc. A note – the below is my humble opinion – with testing – If you use my ideas please test them and if you have problems or learn more let me know.

#1 - Stored Procedures (SPs) Inside SQL Server * Stored Procedures are precompiled Transact-SQL statements stored in a SQL Server database. * Stored Procedures are one of the most powerful pieces of programming you will ever see. When you start out, you will see them as a way to return a record set, or do some small update on your data. As you learn more about SPs you will understand why there are entire books written on the subject. SQL Server compiles the Proc so that when you run it, it runs as fast as possible. Once you write a couple of complicated SPs, you will be convinced. This paper only covers the tip of the Stored Procedure iceberg. * I will refer to Stored Procedures in this document as SP and Proc - get use to it. * Stored Procedures return read only data and can have > Input parameters > Output parameters > Parameters that are both input and output > Can have 1 or more recordsets

Business & Computers, Inc.

Page 1

#2 - Simple Recordset with a Input Parameter * Figure –2-1 shows a simple stored procedures with that has in input parameter and returns a recordset. When we run it from the Query Analyzer (Figure 2–2) we get the following results.

Figure 2-1 Stored Procedure with input parameter & recordset

Stored Procedure Name

Parameter Check Syntax

Run Button

Database running against

Result set

Figure -2-2 Running a procedure on Query…...

Similar Documents

Oracle Sql Tutorial

...Oracle/SQL Tutorial1 Michael Gertz Database and Information Systems Group Department of Computer Science University of California, Davis gertz@cs.ucdavis.edu http://www.db.cs.ucdavis.edu This Oracle/SQL tutorial provides a detailed introduction to the SQL query language and the Oracle Relational Database Management System. Further information about Oracle and SQL can be found on the web site www.db.cs.ucdavis.edu/dbs. Comments, corrections, or additions to these notes are welcome. Many thanks to Christina Chung for comments on the previous version. Recommended Literature George Koch and Kevin Loney: Oracle8 The Complete Reference (The Single Most Comprehensive Sourcebook for Oracle Server, Includes CD with electronic version of the book), 1299 pages, McGraw-Hill/Osborne, 1997. Michael Abbey and Michael Corey: Oracle8 : A Beginner’s Guide [A Thorough Introduction for First-time Users], 767 pages, McGraw-Hill/Osborne, 1997. Steven Feuerstein, Bill Pribyl, Debby Russell: Oracle PL/SQL Programming (2nd Edition), O’Reilly & Associates, 1028 pages, 1997. C.J. Date and Hugh Darwen: A Guide to the SQL Standard (4th Edition), Addison-Wesley, 1997. Jim Melton and Alan R. Simon: Understanding the New SQL: A Complete Guide (2nd Edition, Dec 2000), The Morgan Kaufmann Series in Data Management Systems, 2000. 1 revised Version 1.01, January 2000, Michael Gertz, Copyright 2000. Contents 1. SQL – Structured Query Language 1.1. Tables 1.2. Queries (Part I) 1.3. Data......

Words: 21631 - Pages: 87

Sql for Beginners

...An Training Guide SQL & SQL*Plus for Beginners Training Guide SQL & SQL*Plus for Beginners www.appltop.com info@appltop.com Course Introduction Copyright © 1999 – 2004 ApplTop Solutions Limited 2 Training Guide SQL & SQL*Plus for Beginners www.appltop.com info@appltop.com Course Objectives This course is designed to give each delegate a basic understanding/awareness of the following... • Oracle SQL • Oracle SQL*Plus Copyright © 1999 – 2004 ApplTop Solutions Limited 3 Training Guide SQL & SQL*Plus for Beginners www.appltop.com info@appltop.com Course Objectives This course is designed to give each delegate a basic understanding of the following topics... • Oracle SQL. You will learn how to use the industry standard tool for working with a Relational Database. You will also learn many other things, from constructing simple queries to creating your own tables. • Oracle SQL*Plus. After the course you should have a good understanding of SQL*Plus, from starting it, to creating simple reports on it. During the course there will be several exercises to complete. Copyright © 1999 – 2004 ApplTop Solutions Limited 4 Training Guide SQL & SQL*Plus for Beginners www.appltop.com info@appltop.com Note that this guide is meant only as an introduction to SQL and SQL*Plus and therefore much of the newer, more advanced features available in Oracle databases 8i, 9i and 10g are not covered. Copyright © 1999 – 2004 ApplTop......

Words: 19188 - Pages: 77

Database - Sql

...in Table B that are linked to it will also be modified accordingly. 24. What is SQL? * The language that is most commonly used in relational database to define database odjects (DDL) and to manipulate data (DML) 25. What is the difference between the data definition and data manipulation portion of SQL? * Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples: * CREATE - to create objects in the database * ALTER - alters the structure of the database * DROP - delete objects from the database * TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed * COMMENT - add comments to the data dictionary * RENAME - rename an object * Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples: * SELECT - retrieve data from the a database * INSERT - insert data into a table * UPDATE - updates existing data within a table * DELETE - deletes all records from a table, the space for the records remain * MERGE - UPSERT operation (insert or update) * CALL - call a PL/SQL or Java subprogram * EXPLAIN PLAN - explain access path to data * LOCK TABLE - control concurrency 26. What are data types? * The column specification that determines what kind of data can be stored in that column, character versus numeric or date. 27. Name at least three data......

Words: 2656 - Pages: 11

Sql Tutorial

...SQL Tutorial SQL TUTORIAL Simply Easy Learning by tutorialspoint.com tutorialspoint.com i ABOUT THE TUTORIAL SQL Tutorial SQL is a database computer language designed for the retrieval and management of data in relational database. SQL stands for Structured Query Language. This tutorial will give you quick start with SQL. Audience This reference has been prepared for the beginners to help them understand the basic to advanced concepts related to SQL languages. Prerequisites Before you start doing practice with various types of examples given in this reference, I'm making an assumption that you are already aware about what is database, especially RDBMS and what is a computer programming language. Copyright & Disclaimer Notice All the content and graphics on this tutorial are the property of tutorialspoint.com. Any content from tutorialspoint.com or this tutorial may not be redistributed or reproduced in any way, shape, or form without the written permission of tutorialspoint.com. Failure to do so is a violation of copyright laws. This tutorial may contain inaccuracies or errors and tutorialspoint provides no guarantee regarding the accuracy of the site or its contents including this tutorial. If you discover that the tutorialspoint.com site or this tutorial content contains some errors, please contact us at webmaster@tutorialspoint.com TUTORIALS POINT Simply Easy Learning Table of Content SQL Tutorial .................................................................

Words: 39505 - Pages: 159

Sql Book

... A GUIDE TO SQL Eighth Edition This page intentionally left blank A G U I D E TO S Q L Eighth Edition Philip J. Pratt Grand Valley State University Mary Z. Last University of Mary Hardin-Baylor Australia • Brazil • Japan • Korea • Mexico • Singapore • Spain • United Kingdom • United States A Guide to SQL, Eighth Edition Philip J. Pratt, Mary Z. Last Vice President, Publisher: Jack Calhoun Editor-in-Chief: Alex von Rosenberg Senior Acquisitions Editor: Charles McCormick, Jr. Product Manager: Kate Hennessy Development Editor: Jessica Evans Editorial Assistant: Bryn Lathrop Marketing Director: Brian Joyner Marketing Manager: Bryant Chrzan Marketing Communications Manager: Libby Shipp Marketing Coordinator: Suellen Ruttkay Content Project Manager: Matt Hutchinson Art Director: Stacy Jenkins Shirley, Marissa Falco Cover Designer: Joseph Sherman Cover Image: Getty Images/Taxi/Chris Bell Manufacturing Coordinator: Denise Powers © 2009 Course Technology, Cengage Learning ALL RIGHTS RESERVED. No part of this work covered by the copyright hereon may be reproduced, transmitted, stored, or used in any form or by any means graphic, electronic, or mechanical, including but not limited to photocopying, recording, scanning, digitizing, taping, Web distribution, information networks, or information storage and retrieval systems, except as permitted under Section 107 or 108 of the 1976 United States Copyright Act, without the prior written permission of the publisher...

Words: 48772 - Pages: 196

Sql Database

...dwqwdlqkdmqwkldnqwkldnwkdnwqdldkwd Solutions To Workshop Exercises Chapter 1: SQL and Data 4 Chapter 2: SQL: The Basics 13 Chapter 3: The WHERE and ORDER BY Clauses 20 Chapter 4: Character, Number, and Miscellaneous Functions 27 Chapter 5: Date and Conversion Functions 42 Chapter 6: Aggregate Functions, GROUP BY and HAVING 57 Chapter 7: Equijoins 73 Chapter 8: Subqueries 108 Chapter 9: Set Operators 126 Chapter 10: Complex Joins 137 Chapter 11: Insert, Update, and Delete 164 Chapter 12: Create, Alter, and Drop Tables 178 Chapter 13: Indexes, Sequences, and Views 188 Chapter 14: The Data Dictionary, Scripting, and Reporting 197 Chapter 15: Security 213 Chapter 16: Regular Expressions and Hierarchical Queries 222 Chapter 17: Exploring Data Warehousing Features 235 Chapter 18: SQL Optimization 248 Chapter 1: SQL and Data In this chapter, you learned about data, how data is organized in tables, and how the relationships among the tables are depicted in a schema diagram. Based on your newly acquired knowledge, design a schema diagram based on the fictional ACME Construction Company. Draw on your own work experience to design the following components. 1. Draw boxes for these three tables: EMPLOYEE, POSITION, and DEPARTMENT. Solution: See the solution for Exercise 3. 2. Create at least three columns for each of the tables and designate a primary key for each table. Solution: See the solution for Exercise 3. 3. Create relationships among......

Words: 6586 - Pages: 27

Sql Fundamentals

...SQL Fundamentals The Structured Query Language (SQL) comprises one of the fundamental building blocks of modern database architecture. SQL defines the methods used to create and manipulate relational databases on all major platforms. At first glance, the language may seem intimidating and complex but it's really not all that bad. In a series of articles over the next few weeks we'll explore the inner workings of SQL together. By the time we're through, you'll have the fundamental knowledge you need to go out there and start working with databases! This week, our first article in the SQL series provides an introduction to the basic concepts behind SQL and we'll take a brief look at some of the main commands used to create and modify databases. Throughout this article, please keep our goal in mind: we're trying to get the "big picture" of SQL -- not a mastery of the individual commands. We'll provide a few examples for illustrative purposes and explain the theory behind them, but don't feel frustrated if you can't write your own SQL commands after reading this article. We'll cover each of the major commands in detail in future weekly installments. If you'd like a reminder in your e-mail inbox each week when the next article is posted, please take a moment and subscribe to our newsletter. By the way, the correct pronunciation of SQL is a contentious issue within the database community. In their SQL standard, the American National Standards Institute declared that the......

Words: 1859 - Pages: 8

Sql Quiz

...minutes | 7 out of 10 | Score for this quiz: 7 out of 10 Submitted Mar 19 at 11:58pm This attempt took 36 minutes.   Question 1 1 / 1 pts Parameters for stored procedures and functions can be of any valid SQL Server data type except Parameters for stored procedures and functions can be of any valid SQL Server data type except   date/time Correct!   table   xml   numeric   Question 2 0 / 1 pts Skip to question text. Code a statement that calls the following stored procedure and passes the values ‘2011-12-01’ and 122 to its input parameters. Pass the parameters by name in the reverse order that they’re declared in the procedure. CREATE PROC spInvoiceTotal1        @DateVar smalldatetime,        @VendorID int AS SELECT SUM(InvoiceTotal) FROM Invoices WHERE VendorID = @VendorID AND InvoiceDate >= @DateVar; ______________________________________________________________________________ You Answered Correct Answers EXEC spInvoiceTotal1 @VendorID = 122, @DateVar = '2011-12-01'; EXEC spInvoiceTotal1 @VendorID = 122, @DateVar = '2011-12-01'   Question 3 0 / 1 pts Before you can pass a table to a stored procedure or a function as a parameter, you must use the ______________________ statement to create a user-defined table type. Before you can pass a table to a stored procedure or a function as a parameter, you must use the ______________________ statement to create a user-defined table type. You Answered Correct......

Words: 645 - Pages: 3

Proc 5820

...Supply Chain Network Analysis and Design: Location Analysis PROC 5820 7 May 2014 Summary The principal thesis of this paper is to discuss location analysis and its role in supply chain network analysis and design. The emphasis of site location is of strategic importance to all organizations seeking to maximize profits and minimize costs. The logistics/supply chain network design has six major planning steps in the process of designing a comprehensive logistics/supply chain network: The first step is to define the logistics/supply chain design process. The second step is to perform a logistics/supply chain audit. The third step is to examine the logistics/supply chain network alternatives. The fourth step is to conduct a facility location analysis, followed by the fifth step, which is to make decisions regarding network and facility location. The sixth and final step is to develop an implementation plan. Location analysis is also discussed in terms of the advantages and disadvantages of globalization and major location site factors. The discussion then describes the methods for evaluating major location site factors and making location decisions based on the evaluation. The paper concludes with discussing different modeling approaches and the need for comprehensive planning. (Reid and Sanders, 2010) (Coyle, J., Langley, C., Novak, R., Gibson, B. 2013) Introduction As stated by Jeff......

Words: 3038 - Pages: 13

Basic Database Notes (Sql)

...DATABASE DECLARE VARIABLES USE ADVENTUREWORKS2008; DECLARE @TEST MONEY; SET @TEST = MAX (UNITPRICE) FROM [OREDER DETAILS]; SELECT @TEST; COMMON SYSTEM FUNCTIONS @@DATEFIRST RETURNS WHAT IS CURRENTLY SET AS THE FIRST DAY OF THE WEEK. @@ERROR RETURNS THE ERROR NUMBER OF THE LAST SQL STATEMENT EXECUTED ON THE CURRENT CONNECTION. RETURNS 0 IF NO ERROR. USE CAKEBOSS GO UPDATE EMPLOYEES SET CITY = ‘DURBAN’ WHERE EMPID = ‘1’ IF @@ERROR =547 PRINT ‘A CHECK CONSTRAINT VIOLATION OCCURRED.’; GO @@IDENTITY RETURNS THE LAST IDENTIFY VALUE USED IN INSERT OR SELECT STAMENT SELECT @@IDENTITY FROM CUSTOMERS @@ROWCOUNT RETURNS THE NUMBER OF ROWS AFFECTED BY THE LAST STATEMNET SELECT @@ROWCOUNT FROM CUSTOMERS COMMIT TO SAVE CHANGES OF A QUERY BEGIN TRASACTION; GO DELETE FROM ROOMS WHERE ROOMSIZE =’KING’; GO COMMIT TRANSACTION; ROLLBACK TO REVERT WHAT WAS SAVED WHEN COMMIT WAS USED BEGIN TRANSACTION; ROLLBACK TRASNACTION; BATCHES A BATCH IS A GROUPING OF SQL STATEMENTS IN ONE LOGICAL UNIT. TO SEPARATE A SCRIPT INTO MULTIPLE BATCHES USE THE GO STATEMENT. IT MUST BE ON ITS OWN LINE. SELECT* FROM CUSTOMERS WHERE CUSTID=1 GO EXEC COMMAND CREATE PROC ABC AS SELECT * FROM INVOICES THEN SELECT * FROM EMPLOYEES EXEC ABC GO IF ELSE IF EXISTS (SELECT * FROM EMPLOYEES WHERE AGE >50) SELECT EMPLNAME, AGE FROM EMPLOYEES ELSE SELECT AGE, YEARS FROM EMPLOYEES CASE USE CAKEBOSS GO SELECT CASE WHEN AGE = 36 TEHN......

Words: 1804 - Pages: 8

Sql & Qbe

...SQL & QBE There are three typical operations that are done in querying databases (relational algebra): (1) Project—select columns [SELECT in SQL] (2) Restrict—select rows [WHERE] (3) Join—select columns and merge on rows that meet conditions [FROM] & [WHERE] Relational algebra is not used in current systems. It is a conceptual/theoretical way to manipulate RDBs. Structured Query Language (SQL) is a widely used language that retrieves and updates data in tables and views (manipulate RDBs). QBE is a user interface that simplifies SQL procedures. Other than some minor syntax differences, SQL is standardized. It is very powerful—i.e. you can do almost anything with data tables that you want. It is also simple to use. SQL is set based—returns a subset of tables referenced. Action queries enable user to change, insert, create, and delete data sets (tables). Selection queries retrieve and display data. Parameter queries prompt for input information. Dynasets are temporary tables that Access uses to store data resulting from a query. Tables must be related if used in a query. Natural join (equijoin or inner join)—most common kind of join. Two tables are joined on the common (join) column. The WHERE (=) statement specifies the join column(s) in which the rows have to match. Outer join (full)—all rows from both tables are included in output table (left and right outer joins would include all rows in one table but only the matches from the other). ...

Words: 1410 - Pages: 6

No Sql Databases

...particular NoSQL solution on the above mentioned factors. 1. Introduction Until recently, Relational database systems have been on the forefront of data storage and management operations. The advent of mobile applications that requires real time analysis like GPS based services, banking and social media has led to huge unstructured data being produced every second. Traditional RDBMS systems have found it difficult to cater to these huge chunks of unstructured data, as RDBMS mainly stores structured data in tabular format. Also, the unstructured data being mapped to a relational database results in increase in complexity as it uses expensive infrastructure to model the same. Also, even if the data model fits into SQL, platter of features provided by SQL becomes an overhead. Relational schema becomes a burden on applications which are trying to store data in multiple forms like videos, blogs and images etc. A new methodology for data management was introduced for the management of unstructured data known as NoSQL (Not Only Structured Query Language). NoSQL covers a broader topic of data structuring, storage and aggregation via various implementation approaches. It can store unstructured data and provide real time analysis to back up the web service applications. It gives up on conventional benchmarking of database management principles like Atomicity, Consistency, Isolation and Durability, to attain flexible data handling. Also, it provides inbuilt data......

Words: 4246 - Pages: 17

Sql Injection.

...due to a SQL injection flaw in a web application that communicate with a database. Over ten years have passed since a famous hacker coined the term “SQL injection” and it is still considered one of the major application threats. A lot has been said on this vulnerability, but not all of the aspects and implications have been uncovered, yet. This paper aim is to collate some of the existing knowledge, introduce new techniques and demonstrate how to get complete control over the database management system's underlying operating system, file system and internal network through SQL injection vulnerability in over-looked and theoretically not exploitable scenarios. This paper also discuss about the prevention from the SQL Injection, not only in ORACLE but also in PHP, C#, JAVA and other languages. INDEX ABSTRACT………………………………………………………………………………….....02 INTRODUCTION……………….…………………………….…….………………………….04 BLIND SQL INJECTION…………………………………….………………………………..05 SQL INJECTION OVERVIEW…………………………….………………………………....06 CATEGORIES OF SQL INJECTION ATTACKS…………………………………………..07 WHAT’S VULNERABLE…………………………………………………………..…………08 WHAT’S NOT VULNERABLE…………………………………………………….………….08 SQL INJECTION METHODS……………………………………….……………….……….09 SQL MANIPULATION………………………………………………………..……………….09 CODE INJECTION……………………………………………………….……………………10 FUNCTION CALL INJECTION……………………………………………………………….11 BUFFER OVERFLOWS………………………………………………………………………13 SQL INJECTION TESTING METHODOLOGY………………………………………….....14 PREVENTING SQL......

Words: 3449 - Pages: 14

Sql Plus

...Introduction to Oracle9i: SQL Instructor Guide • Volume 1 40049GC11 Production 1.1 October 2001 D33993 Authors Copyright © Oracle Corporation, 2000, 2001. All rights reserved. Nancy Greenberg Priya Nathan This documentation contains proprietary information of Oracle Corporation. It is provided under a license agreement containing restrictions on use and disclosure and is also protected by copyright law. Reverse engineering of the software is prohibited. If this documentation is delivered to a U.S. Government Agency of the Department of Defense, then it is delivered with Restricted Rights and the following legend is applicable: Technical Contributors and Reviewers Josephine Turner Martin Alvarez Anna Atkinson Don Bates Marco Berbeek Andrew Brannigan Restricted Rights Legend Use, duplication or disclosure by the Government is subject to restrictions for commercial computer software and shall be deemed to be Restricted Rights software under Federal law, as set forth in subparagraph (c)(1)(ii) of DFARS 252.227-7013, Rights in Technical Data and Computer Software (October 1988). This material or any portion of it may not be copied in any form or by any means without the express prior written permission of Oracle Corporation. Any other copying is a violation of copyright law and may result in civil and/or criminal penalties. Laszlo Czinkoczki Michael Gerlach Sharon Gray Rosita Hanoman Mozhe Jalali Sarah Jones Charbel......

Words: 33197 - Pages: 133

Sql Defrag

...WHITEPAPER By Juan Rogers With an Introduction to SQL defrag manager SQL SERVER FRAGMENTATION EXPLAINED TM SUMMARY approach to identifying and resolving index fragmentation in SQL Server Warning: This white paper will get a bit technical as it is intended for DBAs who want to truly understand the details and key components of fragmentation in SQL Server. » How to judge the improvements gained by defragmenting your server » » The pros and cons of various approaches to managing fragmentation » The mechanics behind performance robbing data voids » How fragmentation affects performance » The difference between disk and SQL Server internal and external fragmentation The following is a summary of the key topics covered in this paper: monitoring and managing index fragmentation. This technical white paper will help you understand SQL Server fragmentation OVERVIEW As the data in Microsoft SQL Server tables changes their indexes change. Over time these indexes become fragmented. This fragmentation will adversely affect performance. This technical white paper provides information to help you understand the detailed mechanics behind fragmentation. It will also help you understand the methods and approaches for performing defragmentation so you can improve your SQL Server’s......

Words: 3053 - Pages: 13

Pushbullet PRO v17.7.1 patched apk | All Popular | The.Walking.Dead.S08.E12.WEBHDRip.GERMAN.AC3D.5.1.x264-PRETAiL