SQL Server/T-SQL Tutorial/Transact SQL/Dynamic SQL
Содержание
- 1 A stored procedure with dynamic execution.
- 2 Build a dynamic sql statement from schema name, table name and column name
- 3 Change database dynamically
- 4 create a dynamic sql statement and save it in a varchar type variable
- 5 Pass parameters into dynamic sql statement
- 6 SELECT query with a variable for the table name so that condition determines which table the result set is based on.
- 7 Simple SELECT query that uses a variable for the field to sequence the result set.
- 8 Using Stored Procedure to create dynamic sql statement
A stored procedure with dynamic execution.
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
Build a dynamic sql statement from schema name, table name and column name
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
Change database dynamically
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
create a dynamic sql statement and save it in a varchar type variable
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
Pass parameters into dynamic sql statement
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>
SELECT query with a variable for the table name so that condition determines which table the result set is based on.
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
Simple SELECT query that uses a variable for the field to sequence the result set.
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
Using Stored Procedure to create dynamic sql statement
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