CODEWITHSUNDEEP
sql-serverwhile-loop
Jsnow
Jsnow

Reputation: 375

WHILE SQL Server error

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

Upvotes: 1

Views: 79

Answers (2)

Hadi
Hadi

Reputation: 37368

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

Upvotes: 0

Lemjur
Lemjur

Reputation: 338

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

Upvotes: 3

Related Questions