Build a dynamic sql statement from schema name, table name and column name : Dynamic SQL « Transact SQL « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. Transact SQL
3. Dynamic 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