1

I want to insert into Tables but error:

Msg 207, Level 16, State 1, Line 1 Invalid column name 'A'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'B'.

My table names is : TBL_PriceKeyBoard Keyboard is variable and maybe hard or ram but TBL_Price is static.

Declare @sql as nvarchar(max)
Declare  @TBL as nvarchar(max)
Declare @Name_Edit  nvarchar(max) 
Declare @Name1 as nvarchar(max)= N'B'
Declare @Price as nvarchar(max) = N'12'
Declare @NameTBL  as nvarchar(max)  = N'KeyBoard'



set @Name_Edit = N'A'
 set @TBL = N'TBL_Price'+ @NameTBL  
Set @sql = N'INSERT INTO [DB1].[dbo].'+quotename(@TBL) +'
           (
            [Name_Edit]
           ,[Name1]
           ,[Price]
           )
       VALUES
            (
             '+@Name_Edit+'
            ,'+@Name1+'
            ,'+@Price+'
            )'
            EXECUTE sp_executesql @sql

1 Answer 1

1

You're not quoting your strings properly.

When you insert a string, it has to be surrounded by single quotes, which in Dynamic SQL-ese, is three ticks. This should work.

DECLARE @sql AS NVARCHAR(MAX);
DECLARE @TBL AS NVARCHAR(MAX);
DECLARE @Name_Edit NVARCHAR(MAX); 
DECLARE @Name1 AS NVARCHAR(MAX)= N'B';
DECLARE @Price AS NVARCHAR(MAX) = N'12';
DECLARE @NameTBL AS NVARCHAR(MAX)  = N'KeyBoard';

SET @Name_Edit = N'A';
SET @TBL = N'TBL_Price' + @NameTBL;  
SET @sql = N'INSERT INTO [DB1].[dbo].' + QUOTENAME(@TBL) + '
           (
            [Name_Edit]
           ,[Name1]
           ,[Price]
           )
       VALUES
            (
             ''' + @Name_Edit + '''
            ,''' + @Name1 + '''
            ,''' + @Price + '''
            )';
              PRINT ( @sql );
            --EXECUTE sp_executesql @sql

When I print the code, I get this.

INSERT INTO [DB1].[dbo].[TBL_PriceKeyBoard]
           (
            [Name_Edit]
           ,[Name1]
           ,[Price]
           )
       VALUES
            (
             'A'
            ,'B'
            ,'12'
            )
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.