SA0172 : The dynamic SQL is constructed using external parameters, which is not ensured to be safe

 1CREATE PROCEDURE search_orders 
 2@custid   nchar(5)     = NULL,
 3@shipname nvarchar(40) = NULL ,
 4@TableName varchar(100)
 5AS
 6declare @v_custid   nchar(5)     = NULL,
 7        @v_shipname nvarchar(40) = NULL,
 8        @v2_custid  nchar(7)     = 'aa' + @v_custid,
 9        @v_tableName nvarchar(100)
10
11DECLARE @sql nvarchar(4000)
12DECLARE @sql1 nvarchar(4000)
13DECLARE @sql2 nvarchar(4000)
14DECLARE @sql3 nvarchar(4000)
15SELECT @sql1 =  @tableName+ '1'
16SELECT @sql3 =  @sql1
17exec ( @sql3 + @sql1)
18SELECT @sql2 = '' + @sql1
19
20SELECT @sql = ' SELECT OrderID, OrderDate, CustomerID, ShipName ' +
21              ' FROM dbo.Orders WHERE 1 = 1 '  
22
23exec ( @sql + @sql2 + @sql1 + @shipname)
24DECLARE @sqlSafe nvarchar(4000)
25set @sql1 = quotename(@sql1) + quotename(@sql2);
26set @sqlSafe  = ' SELECT OrderID, OrderDate, CustomerID,  ShipName ' +
27              ' FROM dbo.' +  @sql1  +' WHERE 1 = 1 '
28
29exec (@sqlSafe)
30
31
32DECLARE @IntVariable int;
33DECLARE @SQLString nvarchar(500);
34DECLARE @ParmDefinition nvarchar(500);
35DECLARE @max_title varchar(30);
36
37SET @IntVariable = 197;
38SET @SQLString = N'SELECT @max_titleOUT = max(JobTitle) 
39   FROM AdventureWorks2008R2.HumanResources.Employee
40   WHERE BusinessEntityID = @level'  + @sql1;
41SET @ParmDefinition = N'@level tinyint, @max_titleOUT varchar(30) OUTPUT';
42
43EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;
44
45EXECUTE sp_executesql @statement = @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;
46
47declare @result int
48
49EXECUTE @result = sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;
50
51EXECUTE @result = sp_executesql @statement = @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;