WHILE SQL Server error

Question

I have a SQL query like this :

ALTER PROCEDURE [dbo].[sp_dynamic_column_list](
    @tahun varchar(4),
    @bulan varchar(2),
    @pks varchar(3))
AS  
BEGIN
    SET NOCOUNT ON;

    DECLARE @totalrow int
    DECLARE @inc int = 1
    DECLARE @dynamictable NVARCHAR(MAX)

    CREATE TABLE #temp
    (
        tanggal datetime, 
    )

    -- query cari column dulu baru alter table temp diatas
    SET @totalrow = dbo.fn_count_row_penerimaan(2014,11,40)

    WHILE (@inc <= @totalrow)
    BEGIN
        ALTER TABLE #temp ADD @inc FLOAT
        SET @inc = @inc + 1
    END

    INSERT INTO #temp 
        EXEC sp_get_list_penerimaan_pks2 @tahun, @bulan, @pks

    SELECT * FROM #temp 

    DROP TABLE #temp
END

I got error like this:

[Err] 42000 - [SQL Server]Incorrect syntax near '@inc'.

I'm new to SQL Server and like to know the solution for this problem

Thanks in advance

Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
– marc_s, Commented Dec 4, 2016 at 12:35

Community · Accepted Answer · 2017-05-23 12:24:16Z

3

ALTER TABLE my_table ADD @column INT

You need to use Execute statement as mentioned in link.

WHILE @inc <= @totalrow
BEGIN
  exec ('ALTER table #temp add '+@inc+' FLOAT   set '+@inc+' = '+ @inc+'+1')
END

edited May 23, 2017 at 12:24

CommunityBot

11 silver badge

answered Dec 4, 2016 at 11:37

Lemjur

3383 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hadi Over a year ago

@Jsnow You have to accept this answer if it works. Just check the mark below of voting arrows

Hadi · Accepted Answer · 2016-12-04 13:40:48Z

0

In a SQL statment variable value cannot be provided as a column name, to achieve this you have to use a dynamic SQL query like the Following:

WHILE (@inc <= @totalrow)
BEGIN
    Declare @strquery as varchar(4000)

    SET @strquery  = 'ALTER TABLE #temp ADD [' + @inc + '] FLOAT'

    EXECUTE sp_executesql @strquery  -- if @inc =1 then @strQuery : ALTER TABLE #temp ADD [1] FLOAT

    SET @inc = @inc + 1

END

You can read more about Dynamic SQL queries in this Article

edited Dec 4, 2016 at 13:40

answered Dec 4, 2016 at 13:34

Hadi

37.5k13 gold badges73 silver badges128 bronze badges

Collectives™ on Stack Overflow

WHILE SQL Server error

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related