This is the 26th day of my participation in the August More Text Challenge

The following describes how to execute dynamic SQL. If necessary, view the sp_executesQL incoming and outgoing parameters to obtain the execution results of dynamic SQL

Dynamic SQL is usually executed by concatenating strings that conform to THE SQL syntax, so that the SQL statement can be executed dynamically according to the conditions in the execution process.

Executing dynamic SQL is simple, and it is important to get the results of executing dynamic SQL, which can be useful in some cases.

In the actual development, we often use dynamic SQL, through different parameters or variables, and other SQL command splicing into a complete SQL statement, and then dynamic execution of the SQL string, to achieve the desired function or obtain data.

How do I get the results of dynamic SQL execution into a variable? For further processing, you can do this by executing the SP_executesQL stored procedure.

Execute Executes dynamic SQL

The EXEC or EXECUTE command is dynamically executed by passing in a SQL string as a function call.

A dynamic SQL example

Here is a simple dynamic SQL that executes a SQL string:

execute ('select * from Person.Address where city like ''a%''; ');
Copy the code

The results are as follows:

Gets all tables in the database that contain a column name

As follows, get all tables in the database with a column name, or a column name containing a string.

Get all tables in the database that contain a column name
declare @colName varchar(30)='person';
declare @allContainer bit=0;	-- Contains all column names

declare @sqlStr varchar(500)='SELECT s.name as schema_names, t.name as table_names, col.name as col_names FROM sys.objects AS t JOIN sys.schemas AS s ON s.schema_id = t.schema_id JOIN sys.columns AS col ON col.object_id=t.object_id WHERE t.type = ''U'' AND col.name';

if @allContainer=1
begin
	select @sqlStr=@sqlStr+'=' ' '+@colName+"'; ';
end
else
begin
	select @sqlStr=@sqlStr+' LIKE ''%'+@colName+The '%' '; ';
end
exec (@sqlStr);
Copy the code

When exec or execute executes a dynamic SQL string, it must be placed in parentheses (). Otherwise, an error is reported as a stored procedure or function.

is not a valid identifier.

Perform the following operations.

You can further extend this to get records that meet a condition for that column in the corresponding table.

Get all tables containing a column, as shown in this example from Microsoft official documentation: Get all user-defined tables with cursors and rebuild all indexes of the table:

DECLARE tables_cursor CURSOR  
   FOR  
   SELECT s.name, t.name   
   FROM sys.objects AS t  
   JOIN sys.schemas AS s ON s.schema_id = t.schema_id  
   WHERE t.type = 'U';  
OPEN tables_cursor;  
DECLARE @schemaname sysname;  
DECLARE @tablename sysname;  
FETCH NEXT FROM tables_cursor INTO @schemaname.@tablename;  
WHILE (@@FETCH_STATUS <> - 1)  
BEGIN;  
   EXECUTE ('ALTER INDEX ALL ON ' + @schemaname + '. ' + @tablename + ' REBUILD; ');  
   FETCH NEXT FROM tables_cursor INTO @schemaname.@tablename;  
END;  
PRINT 'The indexes on all tables have been rebuilt.';  
CLOSE tables_cursor;  
DEALLOCATE tables_cursor;  
Copy the code

Use SP_executesQL to pass in and out variables to dynamic SQL

Sp_executesql stored procedures are used to execute reusable or dynamically built T-SQL statements or batch processing. T-sql statements or batch processes can contain embedded parameters or input or output data.

The syntax is as follows:

sp_executesql [ @stmt = ] statement  
[   
  { , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]'} {[@param1 = ] 'value1' [ ,...n ] }  
]  
Copy the code

Sp_executesql Use of embedded parameters

Sp_executesql passes parameters to SQL statements to get all tables in the database that contain a column name

declare @colName varchar(30)='person';
declare @allContainer bit=0;	-- Contains all column names
DECLARE @ParmDefinition NVARCHAR(500);  

SELECT @ParmDefinition='@colName varchar(30)';

declare @sqlStr nvarchar(500)='SELECT s.name as schema_names, t.name as table_names, col.name as col_names FROM sys.objects AS t JOIN sys.schemas AS s ON s.schema_id = t.schema_id JOIN sys.columns AS col ON col.object_id=t.object_id WHERE t.type = ''U'' AND col.name';

if @allContainer=1
begin
	select @sqlStr=@sqlStr+'=@colName';
end
else
begin
	select @sqlStr=@sqlStr+' LIKE ''%''+@colName+''%''; ';
end
exec sp_executesql @sqlStr.@ParmDefinition.@colName=@colName;
Copy the code

Dynamic SQL statements pass in and out parameters

Sp_executesql can also use the output parameter to export data from dynamic SQL to a variable:

declare @colName varchar(30)='person';
declare @count int;
declare @allContainer bit=0;	-- Contains all column names
DECLARE @ParmDefinition NVARCHAR(500);  

SELECT @ParmDefinition='@colName varchar(30),@cnt int output';

declare @sqlStr nvarchar(500)='SELECT @cnt=count(*) FROM sys.objects AS t JOIN sys.schemas AS s ON s.schema_id = t.schema_id JOIN sys.columns AS col ON col.object_id=t.object_id WHERE t.type = ''U'' AND col.name';

if @allContainer=1
begin
	select @sqlStr=@sqlStr+'=@colName';
end
else
begin
	select @sqlStr=@sqlStr+' LIKE ''%''+@colName+''%''; ';
end
exec sp_executesql @sqlStr.@ParmDefinition.@colName=@colName.@cnt= @count output;
select @count;
Copy the code