October 12, 2015 at 9:10 am
Greetings,
I've been working on a project to clean up a former co-workers stored procedures that are a part of our daily ETL process.
Part of our process dynamically creates indexes based on a control table - we store the index name, table name, column name(s) and uniqueness in a control table, and the stored procedure loops through and creates the index(s) once the base table has been loaded.
Currently I'm trying to convert the old dynamic SQL to a statement that uses sp_executesql and a parameter list - but I'm encountering an error message.
First, the old SQL:
SET @sql = 'CREATE '+@UNIQUENESS+' INDEX '+@INDEX_NAME+' ON etl.'+@REFERENCE_NAME+' ('+@COLUMN_NAME+') ON [INDEX]'+ ' '+
'UPDATE [AdminDB].[etl].[INDEX_TABLE]'+' '+
'SET CURRENT_STATE = '+CHAR(39)+'BUILT'+CHAR(39)+' '+
',LAST_BUILD_DTE = GETDATE()'+' '+
'WHERE INDEX_NAME = '+CHAR(39)+@INDEX_NAME+CHAR(39)
This does work, but it doesn't fit with our design pattern, and I've been asked to re-write it.
This is the new SQL that I've come up with, but as stated above, I keep getting an error message:
DECLARE @INDX_SQL NVARCHAR(MAX), @PRM_LIST NVARCHAR(MAX), @REFERENCE_NAME NVARCHAR(50), @INDEX_NAME NVARCHAR(50), @COLUMN_NAME NVARCHAR(MAX)
SET @REFERENCE_NAME = 'MY_TEST_TABLE'
SET @INDEX_NAME = 'ETL_MY_TEST_TABLE_COL01_INDEX'
SET @COLUMN_NAME = 'COL01_TEST'
SET @INDX_SQL = (
'CREATE INDEX @IDX_NAME ON [ETL].@REF_NAME (@COL_NAME) ON [ETL_INDX] '
);
SET @PRM_LIST = '@IDX_NAME NVARCHAR(50), @REF_NAME NVARCHAR(50), @COL_NAME NVARCHAR(MAX)';
EXEC sp_executesql @INDX_SQL, @PRM_LIST, @INDEX_NAME, @REFERENCE_NAME, @COLUMN_NAME;
The DECLARE and hard setting the parameters are just for troubleshooting/development.
This is the error I get when I try to execute the statement:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '@IDX_NAME'.
It seems as though the value for @IDX_NAME isn't being passed, but I'm not sure why. As I test, I tried setting the relationships specifically by changing the sp_executesql to:
EXEC sp_executesql @INDX_SQL, @PRM_LIST, @IDX_NAME = @INDEX_NAME, @REF_NAME = @REFERENCE_NAME, @COL_NAME = @COLUMN_NAME;
But I still get the same error message:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '@IDX_NAME'.
Is it just not possible to build a dynamic CREATE INDEX statement this way? If it is, what am I doing wrong?
My Google Fu seems to be failing me on this, as everything I find leads to the first 'old' SQL statement, and nothing that passes the parameters the way I'm trying to do.
October 12, 2015 at 9:16 am
Most DDL can't be parameterised. The way to do this is your original statement.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 12, 2015 at 9:23 am
I assume that you were asked to change the old sql to prevent sql injection.
You could validate your variables to prevent it. For @UNIQUENESS you can do it manually. For @REFERENCE_NAME and @COLUMN_NAME you can validate the values against sys.tables and sys.columns or INFORMATION_SCHEMA.COLUMNS. For the names of objects or columns, you can also use QUOTENAME function.
October 12, 2015 at 9:46 am
GilaMonster (10/12/2015)
Most DDL can't be parameterised. The way to do this is your original statement.
That figures. And if I had thought to Google for "DDL" and "parameterised" I probably wouldn't have had to ask. Thank you.
October 12, 2015 at 9:50 am
Luis Cazares (10/12/2015)
I assume that you were asked to change the old sql to prevent sql injection.
Yes and no - the stored procedure gets the values from all variables from a table that only the DBAs have access to. In theory, there shouldn't be a threat from SQL injection, yet if a row in the table were changed then it could create the same situation.
I'm trying to get our code base to a common standard and squash some bugs while I'm at it - this one has had me stuck since last week.
October 12, 2015 at 3:17 pm
So is the clustered index on the table permanent, and you just add nonclustered indexes? The clustered index is the most critical index. The best clustered index keys should be chosen very carefully. And that index should always be created first, since all nonclustered indexes will use the key(s) from the clustered index.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply