Dynamic SQL for Schema Modifications
During run time Table Schema changes can be done using dynamic SQL based on user inputs or application logic. A typical Schema modification includes adding a new table or adding a new column to an existing table using dynamic SQL.
Create Procedure AddNewTableDynamicSQL
(
@TableName varchar(30),
@ColumnName1 varchar(30),
@ColumnName2 varchar(30),
@ColumnName3 varchar(30)
)
As
Begin
Declare @SQLString nvarchar(1000)
Set @SQLString='Create Table ' + QUOTENAME(@TableName) + '(' + QUOTENAME(@ColumnName1) +
'varchar(50),' + QUOTENAME(@ColumnName2) + 'varchar(50),' + QUOTENAME(@ColumnName3) + 'varchar(50))'
EXEC sp_executesql @SQLString
End
User can execute the stored procedure as below to create a new table:
EXEC AddNewTableDynamicSQL 'Customers', 'CustID', 'CustomerName', 'CustomerLocation'
Another example of Adding the column to an existing table:
CREATE Procedure AddNewColumnDynamicSQL
(
@TableName varchar(30),
@ColumnName1 varchar(30),
@DataType varchar(30)
)
As
Begin
Declare @SQLString nvarchar(1000)
Set @SQLString='ALTER TABLE ' + QUOTENAME(@TableName) + ' ADD ' + QUOTENAME(@ColumnName1) + ' ' + @DataType
EXEC sp_executesql @SQLString
End
User can execute the stored procedure as below to create a add a new column to existing table:
EXEC AddNewColumnDynamicSQL 'Customers','CustomerPhone','varchar(50)'
Dynamic SQL in SQL Server
In SQL Server, at times the SQL Queries need to be dynamic and not static, meaning the complete SQL query may be built dynamically at run time as a string using the user inputs and any specific application logic. This can be done in queries run from back-end applications or inside stored procedures. In this article let us look into the details about how to create a dynamic SQL and its uses and also what are the security issues that may arise and how to handle those security issues.
Contact Us