I'm building a fun stored procedure that will use dynamic SQL, sp_executesql with parameters, to allow some alter statements for a column in all database tables if the column name exists ( As you can see I used a cursor for loop all the tables on DB)
I built a test but the parameter doesn't work, I get the next error on each alter table statement that runs
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '@parTablename'.
The next is the code
SET NOCOUNT ON;
GO
DECLARE @tablename varchar(100);
DECLARE @alteredColumn varchar(100)='[mycolumn] [datetimeoffset](0) NOT NULL;';
DECLARE @column varchar(100)='mycolumn';
DECLARE @parDefinition nvarchar(500) = N'@parTablename nvarchar(100)';
DECLARE @sqlCommand nvarchar(1000)= N'ALTER TABLE @parTablename ALTER COLUMN '+@alteredColumn;
DECLARE ALTERCURSOR CURSOR LOCAL FAST_FORWARD FOR
SELECT name AS tablename
FROM sys.Tables
OPEN ALTERCURSOR;
FETCH NEXT FROM ALTERCURSOR INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
--print @tablename
IF EXISTS(SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tablename AND COLUMN_NAME = @column)
BEGIN
EXECUTE sp_executesql @sqlCommand, @parDefinition,@parTablename = @tablename
END
FETCH NEXT FROM ALTERCURSOR INTO @tablename
END
CLOSE ALTERCURSOR;
DEALLOCATE ALTERCURSOR;
SET NOCOUNT OFF;
GO
SOLUTION
Apparently is not possible to send a table name as a parameter, instead of that I used the @SeanLange option for degub with a little modification
SET @sqlCommand =Replace(@sqlCommand, '@parTablename',QUOTENAME(@tablename))
EXECUTE sp_executesql @sqlCommand
You can't stick a parameter in the middle of your dynamic sql like this. You need to use PRINT instead of EXECUTE to debug this. I wouldn't use a cursor for this myself but if you go that path you will have to do something like this before the EXECUTE statement.
Set @sqlCommand = Replace(sqlCommand, '@parTablename', @parTablename)