Using sys.sp_executesql procedure dynamiclly
Using the procedure to call the same dml with different arguments.
Last updated:
Warning: Review and test in a non-production environment before running.
USE AdventureWorks
GO
DECLARE @TSQLDefinition NVARCHAR(100)
SET @TSQLDefinition = 'SELECT TOP 10 * FROM Sales.SalesPerson';
EXEC sys.sp_executesql @TSQLDefinition;
DECLARE @TSQLDefinition NVARCHAR(100)
DECLARE @SalesQuotaVar DECIMAL(6, 2)
DECLARE @TSQLVariableDefinitions NVARCHAR(500)
SET @ TSQLVariableDefinitions = N'@SalesQuotaVal decimal(6,2)';
SET @SalesQuotaVar = 200.00;
SET @TSQLDefinition = 'SELECT TOP 10 * FROM Sales.SalesPerson WHERE SalesQuota > @SalesQuotaVal';
EXEC sys.sp_executesql @TSQLDefinition, @ TSQLVariableDefinitions,
@SalesQuotaVal = @SalesQuotaVar;
GO
DECLARE @TSQLDefinition NVARCHAR(500)
DECLARE @TerritoryVar INT
DECLARE @SalesQuotaVar DECIMAL(6, 2)
DECLARE @TSQLParameterDefinitions NVARCHAR(500)
SET @TSQLParameterDefinitions = N'@SalesQuotaVal decimal(6,2), @TerritoryID INT';
SET @SalesQuotaVar = 200.00;
SET @TerritoryVar = 969;
SET @TSQLDefinition = 'SELECT TOP 10 * FROM Sales.SalesPerson WHERE SalesQuota > @SalesQuotaVal and TerritoryID = @TerritoryID';
EXEC sys.sp_executesql
@TSQLDefinition,
@TSQLParameterDefinitions,
@SalesQuotaVal = @SalesQuotaVar,
@TerritoryID = @TerritoryVar;
DECLARE @TSQLDefinition NVARCHAR(500)
DECLARE @TableNameVar NVARCHAR(100)
DECLARE @TableList TABLE
(
TableName NVARCHAR(100)
)
/* collect table names into a table variable */
INSERT @TableList
( TableName)
( SELECT Name
FROM sys.tables
WHERE type_desc = 'USER_TABLE')
WHILE EXISTS ( SELECT *
FROM @TableList )
BEGIN
/* build the TSQL statement we want executed for one table */
SELECT TOP 1
@TableNameVar = tl.TableName
FROM @TableList AS tl
SET @TSQLDefinition = 'SELECT COUNT(*) AS ' + @TableNameVar + ' FROM ' + @TableNameVar;
/* execute the TSQL */
EXEC sys.sp_executesql @TSQLDefinition;
/* remove the tablename from the list of tables*/
DELETE FROM @TableList
WHERE TableName = @TableNameVar
END
GO