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