SQL Server/T-SQL Tutorial/Transact SQL/Dynamic SQL

Материал из SQL эксперт
Перейти к: навигация, поиск

A stored procedure with dynamic execution.

   <source lang="sql">

4> 5> 6> CREATE TABLE authors( 7> au_id varchar(11), 8> au_lname varchar(40) NOT NULL, 9> au_fname varchar(20) NOT NULL, 10> phone char(12) NOT NULL DEFAULT ("UNKNOWN"), 11> address varchar(40) NULL, 12> city varchar(20) NULL, 13> state char(2) NULL, 14> zip char(5) NULL, 15> contract bit NOT NULL 16> ) 17> GO 1> insert authors values("1", "Joe", "Abra", "111 111-1111", "6 St.", "Berkeley", "CA", "11111", 1) 2> insert authors values("2", "Jack", "Majo", "222 222-2222", "3 St.", "Oakland" , "CA", "22222", 1) 3> insert authors values("3", "Pink", "Cherry", "333 333-3333", "5 Ln.", "Vancouver", "BC", "33333", 1) 4> insert authors values("4", "Blue", "Albert", "444 444-4444", "7 Av.", "Vancouver", "BC", "44444", 1) 5> insert authors values("5", "Red", "Anne", "555 555-5555", "6 Av.", "Regina", "SK", "55555", 1) 6> insert authors values("6", "Black", "Michel", "666 666-6666", "3 Pl.", "Regina", "SK", "66666", 1) 7> insert authors values("7", "White", "Sylvia", "777 777-7777", "1 Pl.", "Rockville", "MD", "77777", 1) 8> insert authors values("8", "Yellow","Heather","888 888-8888", "3 Pu", "Vacaville", "CA", "88888", 0) 9> insert authors values("9", "Gold", "Dep", "999 999-9999", "5 Av.", "Oakland", "CA", "99999", 0) 10> insert authors values("10", "Siler", "Dean", "000 000-0000", "4 Av.", "Oakland", "CA", "00000", 1) 11> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> CREATE PROC prExecute 3> @chvExec varchar(255) 4> AS 5> EXEC (@chvExec) 6> GO 1> 2> DECLARE @chvDoThis varchar(255) 3> SELECT @chvDoThis = "DELETE AUTHORS" 4> EXEC prExecute @chvDoThis 5> 6> 7> drop PROC prExecute  ; 8> GO (10 rows affected) 1> 2> drop table authors; 3> GO</source>


Build a dynamic sql statement from schema name, table name and column name

   <source lang="sql">

3> CREATE TABLE Orders ( 4> OrderID int IDENTITY (1, 1) NOT NULL , 5> CustomerID nchar (5) NULL , 6> EmployeeID int NULL , 7> OrderDate datetime NULL , 8> RequiredDate datetime NULL , 9> ShippedDate datetime NULL , 10> ShipVia int NULL , 11> Freight money NULL DEFAULT (0), 12> ShipName nvarchar (40) NULL , 13> ShipAddress nvarchar (60) NULL , 14> ShipCity nvarchar (15) NULL , 15> ShipRegion nvarchar (15) NULL , 16> ShipPostalCode nvarchar (10) NULL , 17> ShipCountry nvarchar (15) NULL 18> ) 19> GO 1> 2> 3> DECLARE 4> @schemaname AS NVARCHAR(128), 5> @tablename AS NVARCHAR(128), 6> @colname AS NVARCHAR(128), 7> @sql AS NVARCHAR(825), 8> @cnt AS INT; 9> 10> SET @schemaname = N"dbo"; 11> SET @tablename = N"Orders"; 12> SET @colname = N"CustomerID"; 13> SET @sql = N"INSERT INTO #T(cnt) SELECT COUNT(DISTINCT " 14> + QUOTENAME(@colname) + N") FROM " 15> + QUOTENAME(@schemaname) 16> + N"." 17> + QUOTENAME(@tablename) 18> + N";"; 19> 20> CREATE TABLE #T(cnt INT); 21> EXEC(@sql); 22> SET @cnt = (SELECT cnt FROM #T); 23> SELECT @cnt; 24> DROP TABLE #T; 25> GO


         0

1> 2> drop table orders; 3> GO</source>


Change database dynamically

   <source lang="sql">

4> USE Northwind; 5> DECLARE @db AS NVARCHAR(258); 6> SET @db = QUOTENAME(N"pubs"); 7> EXEC(N"USE " + @db + N"; EXEC(""SELECT DB_NAME();"");"); 8> GO</source>


create a dynamic sql statement and save it in a varchar type variable

   <source lang="sql">

3> CREATE TABLE Employees ( 4> EmployeeID int NOT NULL , 5> LastName nvarchar (20) NOT NULL , 6> FirstName nvarchar (10) NOT NULL , 7> Title nvarchar (30) NULL , 8> TitleOfCourtesy nvarchar (25) NULL , 9> BirthDate datetime NULL , 10> HireDate datetime NULL , 11> Address nvarchar (60) NULL , 12> City nvarchar (15) NULL , 13> Region nvarchar (15) NULL , 14> PostalCode nvarchar (10) NULL , 15> Country nvarchar (15) NULL , 16> HomePhone nvarchar (24) NULL , 17> Extension nvarchar (4) NULL , 18> Photo image NULL , 19> Notes ntext NULL , 20> ReportsTo int NULL , 21> PhotoPath nvarchar (255) NULL 22> 23> ) 24> GO 1> 2> 3> DECLARE @InVar varchar(200) 4> 5> SET @InVar = "DECLARE @OutVar varchar(50) 6~ SELECT @OutVar = FirstName FROM Employees WHERE EmployeeID = 1 7~ SELECT ""The Value Is "" + @OutVar" 8> 9> EXEC (@Invar) 10> GO


NULL (1 rows affected) 1> 2> drop table Employees; 3> GO</source>


Pass parameters into dynamic sql statement

   <source lang="sql">

11> 12> CREATE TABLE Orders ( 13> OrderID int IDENTITY (1, 1) NOT NULL , 14> CustomerID nchar (5) NULL , 15> EmployeeID int NULL , 16> OrderDate datetime NULL , 17> RequiredDate datetime NULL , 18> ShippedDate datetime NULL , 19> ShipVia int NULL , 20> Freight money NULL DEFAULT (0), 21> ShipName nvarchar (40) NULL , 22> ShipAddress nvarchar (60) NULL , 23> ShipCity nvarchar (15) NULL , 24> ShipRegion nvarchar (15) NULL , 25> ShipPostalCode nvarchar (10) NULL , 26> ShipCountry nvarchar (15) NULL 27> ) 28> GO 1> 2> 3> 4> DECLARE @i AS INT; 5> SET @i = 10248; 6> 7> DECLARE @sql AS NVARCHAR(46); 8> SET @sql = "SELECT * FROM dbo.Orders WHERE OrderID = @oid;"; 9> 10> EXEC sp_executesql 11> @stmt = @sql, 12> @params = N"@oid AS INT", 13> @oid = @i; 14> GO OrderID CustomerID EmployeeID OrderDate RequiredDate ShippedDate ShipVia Freight ShipName ShipAddress

                                          ShipCity        ShipRegion      ShipPostalCode ShipCountry

---------- ----------- ----------------------- ----------------------- ----------------------- ----------- --------------------- ---------------------------------------- ------------------
--------------- --------------- -------------- ---------------

1> 2> drop table orders; 3> GO 1></source>


SELECT query with a variable for the table name so that condition determines which table the result set is based on.

   <source lang="sql">

4> 5> CREATE TABLE sales( 6> stor_id char(4) NOT NULL, 7> ord_num varchar(20) NOT NULL, 8> ord_date datetime NOT NULL, 9> qty smallint NOT NULL, 10> payterms varchar(12) NOT NULL, 11> title_id varchar(80) 12> ) 13> GO 1> insert sales values("1", "QA7442.3", "09/13/94", 75, "ON Billing","1") 2> insert sales values("2", "D4482", "09/14/94", 10, "Net 60", "1") 3> insert sales values("3", "N914008", "09/14/94", 20, "Net 30", "2") 4> insert sales values("4", "N914014", "09/14/94", 25, "Net 30", "3") 5> insert sales values("5", "423LL922", "09/14/94", 15, "ON Billing","3") 6> insert sales values("6", "423LL930", "09/14/94", 10, "ON Billing","2") 7> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> DECLARE @chvTable VARCHAR(30), 3> @chvSQL VARCHAR(255) 4> IF getdate() > "7/1/1997" 5> SELECT @chvTable = "sales" 6> ELSE 7> SELECT @chvTable = "authors" 8> SELECT @chvSQL = "SELECT * FROM " + @chvTable 9> EXEC (@chvSQL) 10> GO stor_id ord_num ord_date qty payterms title_id


-------------------- ----------------------- ------ ------------ --------------------------------------------------------------------------------

1 QA7442.3 1994-09-13 00:00:00.000 75 ON Billing 1 2 D4482 1994-09-14 00:00:00.000 10 Net 60 1 3 N914008 1994-09-14 00:00:00.000 20 Net 30 2 4 N914014 1994-09-14 00:00:00.000 25 Net 30 3 5 423LL922 1994-09-14 00:00:00.000 15 ON Billing 3 6 423LL930 1994-09-14 00:00:00.000 10 ON Billing 2 (6 rows affected) 1> 2> drop table sales; 3> GO</source>


Simple SELECT query that uses a variable for the field to sequence the result set.

   <source lang="sql">

4> 5> 6> CREATE TABLE authors( 7> au_id varchar(11), 8> au_lname varchar(40) NOT NULL, 9> au_fname varchar(20) NOT NULL, 10> phone char(12) NOT NULL DEFAULT ("UNKNOWN"), 11> address varchar(40) NULL, 12> city varchar(20) NULL, 13> state char(2) NULL, 14> zip char(5) NULL, 15> contract bit NOT NULL 16> ) 17> GO 1> insert authors values("1", "Joe", "Abra", "111 111-1111", "6 St.", "Berkeley", "CA", "11111", 1) 2> insert authors values("2", "Jack", "Majo", "222 222-2222", "3 St.", "Oakland" , "CA", "22222", 1) 3> insert authors values("3", "Pink", "Cherry", "333 333-3333", "5 Ln.", "Vancouver", "BC", "33333", 1) 4> insert authors values("4", "Blue", "Albert", "444 444-4444", "7 Av.", "Vancouver", "BC", "44444", 1) 5> insert authors values("5", "Red", "Anne", "555 555-5555", "6 Av.", "Regina", "SK", "55555", 1) 6> insert authors values("6", "Black", "Michel", "666 666-6666", "3 Pl.", "Regina", "SK", "66666", 1) 7> insert authors values("7", "White", "Sylvia", "777 777-7777", "1 Pl.", "Rockville", "MD", "77777", 1) 8> insert authors values("8", "Yellow","Heather","888 888-8888", "3 Pu", "Vacaville", "CA", "88888", 0) 9> insert authors values("9", "Gold", "Dep", "999 999-9999", "5 Av.", "Oakland", "CA", "99999", 0) 10> insert authors values("10", "Siler", "Dean", "000 000-0000", "4 Av.", "Oakland", "CA", "00000", 1) 11> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> DECLARE @chvOrderBy VARCHAR(30), 4> 5> @chvSQL VARCHAR(255) 6> 7> SELECT @chvOrderBy = "au_lname" 8> 9> SELECT @chvSQL = "SELECT * FROM authors ORDER BY " + @chvOrderBy 10> 11> EXEC (@chvSQL) 12> 13> GO au_id au_lname au_fname phone address city state zip contract


---------------------------------------- -------------------- ------------ ---------------------------------------- -------------------- ----- ----- --------

6 Black Michel 666 666-6666 3 Pl. Regina SK 66666 1 4 Blue Albert 444 444-4444 7 Av. Vancouver BC 44444 1 9 Gold Dep 999 999-9999 5 Av. Oakland CA 99999 0 2 Jack Majo 222 222-2222 3 St. Oakland CA 22222 1 1 Joe Abra 111 111-1111 6 St. Berkeley CA 11111 1 3 Pink Cherry 333 333-3333 5 Ln. Vancouver BC 33333 1 5 Red Anne 555 555-5555 6 Av. Regina SK 55555 1 10 Siler Dean 000 000-0000 4 Av. Oakland CA 00000 1 7 White Sylvia 777 777-7777 1 Pl. Rockville MD 77777 1 8 Yellow Heather 888 888-8888 3 Pu Vacaville CA 88888 0 (10 rows affected) 1> 2> 3> drop table authors; 4> GO</source>


Using Stored Procedure to create dynamic sql statement

   <source lang="sql">

4> 5> 6> CREATE TABLE sales( 7> stor_id char(4) NOT NULL, 8> ord_num varchar(20) NOT NULL, 9> ord_date datetime NOT NULL, 10> qty smallint NOT NULL, 11> payterms varchar(12) NOT NULL, 12> title_id varchar(80) 13> ) 14> GO 1> insert sales values("1", "QA7442.3", "09/13/94", 75, "ON Billing","1") 2> insert sales values("2", "D4482", "09/14/94", 10, "Net 60", "1") 3> insert sales values("3", "N914008", "09/14/94", 20, "Net 30", "2") 4> insert sales values("4", "N914014", "09/14/94", 25, "Net 30", "3") 5> insert sales values("5", "423LL922", "09/14/94", 15, "ON Billing","3") 6> insert sales values("6", "423LL930", "09/14/94", 10, "ON Billing","2") 7> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> CREATE PROCEDURE SalesQtys 3> @chvOrderBy VARCHAR(30) = "qty", 4> @chvSortType VARCHAR(4) = "ASC" 5> AS 6> DECLARE @chvSQL VARCHAR(255) 7> SELECT @chvSQL = "SELECT stor_id, ord_num, title_id, qty" 8> SELECT @chvSQL = @chvSQL + " FROM Sales" 9> SELECT @chvSQL = @chvSQL + " ORDER BY " + @chvOrderBy + " " + @chvSortType 10> EXEC (@chvSQL) 11> GO 1> 2> SalesQtys @chvOrderBy = "stor_id", @chvSortType = "DESC" 3> GO stor_id ord_num title_id qty


-------------------- -------------------------------------------------------------------------------- ------

6 423LL930 2 10 5 423LL922 3 15 4 N914014 3 25 3 N914008 2 20 2 D4482 1 10 1 QA7442.3 1 75 (6 rows affected) 1> SalesQtys 2> GO stor_id ord_num title_id qty


-------------------- -------------------------------------------------------------------------------- ------

2 D4482 1 10 6 423LL930 2 10 5 423LL922 3 15 3 N914008 2 20 4 N914014 3 25 1 QA7442.3 1 75 (6 rows affected) 1> SalesQtys @chvOrderBy = "title_id" 2> GO stor_id ord_num title_id qty


-------------------- -------------------------------------------------------------------------------- ------

1 QA7442.3 1 75 2 D4482 1 10 3 N914008 2 20 6 423LL930 2 10 4 N914014 3 25 5 423LL922 3 15 (6 rows affected) 1> 2> 3> drop table sales; 4> GO</source>