SQL Server/T-SQL Tutorial/Procedure Function/Utility Procedure

A script that creates a stored procedure that copies a table

   <source lang="sql">

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</source>

A stored procedure that tests for a valid foreign key

   <source lang="sql">

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</source>

Create procedure to drop foreign key

   <source lang="sql">

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</source>

Spelling single digits.

   <source lang="sql">

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></source>

uses system tables to determine the datatype of a field.

   <source lang="sql">

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</source>