SQL Server/T-SQL Tutorial/Procedure Function/Utility Procedure
Содержание
A script that creates a stored procedure that copies a table
14>
15> create table Billings (
16> BankerID INTEGER,
17> BillingNumber INTEGER,
18> BillingDate datetime,
19> BillingTotal INTEGER,
20> TermsID INTEGER,
21> BillingDueDate datetime ,
22> PaymentTotal INTEGER,
23> CreditTotal INTEGER
24>
25> );
26> GO
1>
2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321);
3> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321.);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321);
2> GO
(1 rows affected)
1>
2> create table BillingCopy (
3> BankerID INTEGER,
4> BillingNumber INTEGER,
5> BillingDate datetime,
6> BillingTotal INTEGER,
7> TermsID INTEGER,
8> BillingDueDate datetime ,
9> PaymentTotal INTEGER,
10> CreditTotal INTEGER
11>
12> );
13> GO
1>
2> IF OBJECT_ID("spCopyBillings") IS NOT NULL
3> DROP PROC spCopyBillings
4> GO
1>
2> CREATE PROC spCopyBillings
3> AS
4> IF OBJECT_ID("BillingCopy") IS NOT NULL
5> DROP TABLE BillingCopy
6> SELECT *
7> INTO BillingCopy
8> FROM Billings
9>
10> drop table BillingCopy;
11> drop table Billings;
12> GO
A stored procedure that tests for a valid foreign key
13> create table Billings (
14> BankerID INTEGER,
15> BillingNumber INTEGER,
16> BillingDate datetime,
17> BillingTotal INTEGER,
18> TermsID INTEGER,
19> BillingDueDate datetime ,
20> PaymentTotal INTEGER,
21> CreditTotal INTEGER
22>
23> );
24> GO
1>
2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321);
3> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321.);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321);
2> GO
(1 rows affected)
1>
2>
3> CREATE PROC spInsertBilling
4> @BankerID int, @BillingNumber varchar(50),
5> @BillingDate smalldatetime, @BillingTotal money,
6> @TermsID int, @BillingDueDate smalldatetime
7> AS
8> IF EXISTS(SELECT * FROM Bankers WHERE BankerID = @BankerID)
9> BEGIN
10> INSERT Billings (BankerID)
11> VALUES (@BankerID)
12> END
13> ELSE
14> BEGIN
15> RAISERROR("Not a valid BankerID!",1,1)
16> RETURN -100
17> END
18> GO
1>
2>
3> drop PROC spInsertBilling;
4> GO
1>
2>
3> drop table Billings;
4> GO
Create procedure to drop foreign key
4>
5>
6>
7> CREATE PROCEDURE prDropForeignKeys
8> @chvReferencedTable VARCHAR(30)
9> AS
10> DECLARE @chvTableWithForeignKey VARCHAR(30),
11> @chvForeignKey VARCHAR(30),
12> @chvSQL VARCHAR(255)
13> DECLARE cuFKs CURSOR
14> FOR
15> SELECT tb.name,
16> fk.name
17> FROM ((sysobjects tb INNER JOIN sysreferences r ON tb.id = r.fkeyid)
18> INNER JOIN sysobjects fk ON r.constid = fk.id)
19> INNER JOIN sysobjects refd_tb ON refd_tb.id = r.rkeyid
20> WHERE refd_tb.name = @chvReferencedTable
21> OPEN cuFKs
22> FETCH NEXT FROM cuFKs INTO
23> @chvTableWithForeignKey, @chvForeignKey
24> WHILE (@@fetch_status <> -1)
25> BEGIN
26> SELECT @chvSQL = "ALTER TABLE " + @chvTableWithForeignKey
27> + " DROP CONSTRAINT " + @chvForeignKey
28> EXEC (@chvSQL)
29> FETCH NEXT FROM cuFKs INTO
30> @chvTableWithForeignKey, @chvForeignKey
31> END
32> DEALLOCATE cuFKs
33> GO
1>
2> drop PROCEDURE prDropForeignKeys;
3> GO
Spelling single digits.
4>
5> CREATE PROC pr_single @chrDigit CHAR(1), @chrSd VARCHAR(6) OUTPUT
6> AS
7> IF @chrDigit = "1"
8> BEGIN
9> SELECT @chrSd = "One"
10> RETURN
11> END
12> ELSE
13> IF @chrDigit = "2"
14> BEGIN
15> SELECT @chrSd = "Two"
16> RETURN
17> END
18> ELSE
19> IF @chrDigit = "3"
20> BEGIN
21> SELECT @chrSd = "Three"
22> RETURN
23> END
24> ELSE
25> IF @chrDigit = "4"
26> BEGIN
27> SELECT @chrSd = "Four"
28> RETURN
29> END
30> ELSE
31> IF @chrDigit = "5"
32> BEGIN
33> SELECT @chrSd = "Five"
34> RETURN
35> END
36> ELSE
37> IF @chrDigit = "6"
38> BEGIN
39> SELECT @chrSd = "Six"
40> RETURN
41> END
42> ELSE
43> IF @chrDigit = "7"
44> BEGIN
45> SELECT @chrSd = "Seven"
46> RETURN
47> END
48> ELSE
49> IF @chrDigit = "8"
50> BEGIN
51> SELECT @chrSd = "Eight"
52> RETURN
53> END
54> ELSE
55> IF @chrDigit = "9"
56> BEGIN
57> SELECT @chrSd = "Nine"
58> RETURN
59> END
60> ELSE
61> SELECT @chrSd = ""
62> GO
1>
2> DROP PROC pr_single
3> GO
1>
2>
3>
uses system tables to determine the datatype of a field.
3>
4>
5> CREATE PROCEDURE prNeedsQuotes
6> @chvTable VARCHAR(30),
7> @chvField VARCHAR(30),
8> @chvNeedsQuotes CHAR(1) OUTPUT
9> AS
10> DECLARE @chvDataType VARCHAR(30), @intUserType INT
11> SELECT @chvDataType = LOWER(st.name), @intUserType = st.usertype
12> FROM (sysObjects so INNER JOIN sysColumns sc ON so.id = sc.id)
13> INNER JOIN sysTypes st ON sc.usertype = st.usertype
14> WHERE so.type = "U"
15> AND so.name = @chvTable
16> AND sc.name = @chvField
17> IF @intUserType > 100
18> BEGIN
19> SELECT @chvDataType = LOWER(st2.name)
20> FROM sysTypes st1 INNER JOIN sysTypes st2 ON st1.Type = st2.Type
21> WHERE st2.userType < 100
22> AND st2.userType NOT IN (18, 80)
23> AND st1.usertype = @intUserType
24> END
25> SELECT @chvNeedsQuotes =
26> CASE @chvDataType
27> WHEN "char" THEN "y"
28> WHEN "datetime" THEN "y"
29> WHEN "datetimn" THEN "y"
30> WHEN "smalldatetime" THEN "y"
31> WHEN "text" THEN "y"
32> WHEN "timestamp" THEN "y"
33> WHEN "varchar" THEN "y"
34> ELSE "n"
35> END
36> GO
1>
2> drop PROCEDURE prNeedsQuotes ;
3> GO