Stored Procedures as Parameterized Views : Procedure « Procedure Function « SQL Server / T-SQL Tutorial

Home
SQL Server / T-SQL Tutorial
1.Query
2.Insert Delete Update
3.Table
4.Table Join
5.Data Types
6.Set Operations
7.Constraints
8.Subquery
9.Aggregate Functions
10.Date Functions
11.Math Functions
12.String Functions
13.Data Convert Functions
14.Analytical Functions
15.Sequence Indentity
16.View
17.Index
18.Cursor
19.Database
20.Transact SQL
21.Procedure Function
22.Trigger
23.Transaction
24.XML
25.System Functions
26.System Settings
27.System Tables Views
28.User Role
29.CLR
SQL Server / T-SQL Tutorial » Procedure Function » Procedure 




3>
4>
5>
6CREATE TABLE Product(
7>     ProductID               int                NOT NULL,
8>     Name                    nvarchar(25)       NOT NULL,
9>     ProductNumber           nvarchar(25)               ,
10>      Color                   nvarchar(15)       NULL,
11>      StandardCost            money              NOT NULL,
12>      Size                    nvarchar(5)        NULL,
13>      Weight                  decimal(82)      NULL,
14>      ProductLine             nchar(20)           NULL,
15>      SellStartDate           datetime           NOT NULL,
16>      SellEndDate             datetime           NULL
17>  )
18>  GO
1insert into Product values(1,'Product A', '1','Red',123.123,'1',1,'ProductLine A','1999-03-22','2000-03-22');
2> GO

(rows affected)
1>
2>
3insert into Product values(2,'Product B', '2','Yellow',234.234,'1',3,'ProductLine B','2000-03-22','2001-03-22');
4> GO

(rows affected)
1>
2>
3insert into Product values(3,'Product C', '3','Pink',345.345,'1',3,'ProductLine V','2001-09-22','2006-02-22');
4> GO

(rows affected)
1>
2>
3insert into Product values(4,'Product D', '4','White',456.456,'1',4,'ProductLine D','2002-08-22','2006-03-22');
4> GO

(rows affected)
1>
2>
3insert into Product values(5,'Product E', '5','Black',567.567,'1',5,'ProductLine E','2003-01-22','2003-04-22');
4> GO

(rows affected)
1>
2>
3insert into Product values(6,'Product F', '6','Blue',678.678,'1',6,'ProductLine W','2004-02-22','2005-05-22');
4> GO

(rows affected)
1>
2>
3insert into Product values(7,'Product G', '7','Drak',789.789,'1',7,'ProductLine Q','2005-03-22','2006-03-22');
4> GO

(rows affected)
1>
2>
3insert into Product values(8,'Product H', '8','Gray',234.123,'1',8,'ProductLine F','2006-04-22','2006-09-22');
4> GO

(rows affected)
1>
2>
3insert into Product values(9,'Product I', '9','Red',543.123,'1',9,'ProductLine R','2007-05-22','2008-03-22');
4> GO

(rows affected)
1>
2>
3insert into Product values(0,'Product J', '0','Gold',765.123,'1',0,'ProductLine J','2008-06-22','2009-03-22');
4> GO

(rows affected)
1>
2>
3>
4>
5>
6CREATE VIEW vProductCosts
7> AS
8SELECT ProductID, Name, ProductNumber, StandardCost
9FROM Product
10> GO
1>
2CREATE PROCEDURE spProductCosts
3> AS
4SELECT ProductID, Name, ProductNumber, StandardCost
5FROM Product
6> GO
1>
2> EXECUTE spProductCosts
3> GO
ProductID   Name                      ProductNumber             StandardCost
----------- ------------------------- ------------------------- ---------------------
          Product A                 1                                      123.1230
          Product B                 2                                      234.2340
          Product C                 3                                      345.3450
          Product D                 4                                      456.4560
          Product E                 5                                      567.5670
          Product F                 6                                      678.6780
          Product G                 7                                      789.7890
          Product H                 8                                      234.1230
          Product I                 9                                      543.1230
          Product J                 0                                      765.1230

(10 rows affected)
1>
2> drop PROCEDURE spProductCosts;
3> GO
1>
2> drop VIEW vProductCosts;
3> GO
1>
2> drop table Product;
3> GO














21.8.Procedure
21.8.1.calls the procedure
21.8.2.Declare a variable in a procedure
21.8.3.Stored Procedures as Parameterized Views
21.8.4.PRINT information out of a PROCEDURE
21.8.5.Procedure as a view
21.8.6.Return a value out of a procedure
21.8.7.Returning Values from a View as Stored Procedure Output Parameters
21.8.8.Filtering for Null Values with a Stored Procedure
21.8.9.Managing a Transaction Inside a Stored Procedure
21.8.10.Encrypting a Stored Procedure
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.