Dynamic SQL parameter error, Incorrect syntax near '@myparametername'

Question

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

I replaced the sql command variable to 'PRINT @parTablename ' and i can see all the table names, i don't get why isn't work with the alter statements — Eduardo Chávez, Jun 22 '16 at 16:41
what's the `@partablename = @tablename` for? and where's @partablename defined? you have a string that contains a definition for it, but that string's never executed. — Marc B, Jun 22 '16 at 16:42
Is used `@partablename = @tablename ` for set the value of `@partablename` That is defined here `DECLARE @parDefinition nvarchar(500) = N'@parTablename nvarchar(100)';` And used here `EXECUTE sp_executesql @sqlCommand, @parDefinition,@parTablename = @tablename` @Marc Check the documentation ;) [link](https://msdn.microsoft.com/en-us/library/ms188001.aspx) **Note that `@tablename` is set on the cursor** — Eduardo Chávez, Jun 22 '16 at 16:52
You can't pass the table name as a parameter, you need to build the string manually. — Aaron Bertrand, Jun 22 '16 at 16:55
Why did you use concatenation for the "alteredColumn" variable, but parameterization for the "tablename" variable? — Tab Alleman, Jun 22 '16 at 17:19
@AaronBertrand thanks, that's the problem I will choose the @SeanLange option but just with a little addition `SET @sqlCommand =Replace(@sqlCommand, '@parTablename',QUOTENAME(@tablename))` before execute statement. — Eduardo Chávez, Jun 22 '16 at 17:45

score 0 · Accepted Answer · answered Jun 22 '16 at 16:46

0

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)

answered Jun 22 '16 at 16:46

Sean Lange

33,028
3
25
40

Maybe you mean `SET @sqlCommand =Replace(@sqlCommand, '@parTablename', @tablename)` yes, I did that and the ALTER statements are fine. So Why I can't use parameters like this? the documentation says that is possible – Eduardo Chávez Jun 22 '16 at 17:13
Well finally I got it, I can't parameterize tables is better to build the string before I will choose the @SeanLange option :) thanks man – Eduardo Chávez Jun 22 '16 at 17:42

Dynamic SQL parameter error, Incorrect syntax near '@myparametername'

1 Answers1