Use aggregate function in dynamic sql statement : Dynamic SQL « Transact SQL « SQL Server / T-SQL

1. SQL Server / T-SQL
2. Transact SQL
3. Dynamic SQL

Use aggregate function in dynamic sql statement

 


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

-----------
          0
1>
2> drop table orders;
3> GO

 

Related examples in the same category