CODEWITHSUNDEEP
sqlsql-server
Stojdza
Stojdza

Reputation: 445

INSERT INTO command doesn't work

I have one outer cursor and one inner cursor also have two tables to work with. Now with the outer cursor i'm making new columns in the table 1 and naming them by the values from the table two, and that works just fine. Problem is with the inner cursor witch i used to insert the values into those new columns from one specific column from another table. This seams not to work, but what confusing me is that i do not get any error messages. Now i hope you understand what i'm trying to do, here is the code so comment for more description about the problem :

DECLARE @rbr_param  nvarchar(255)
DECLARE @vrednost nvarchar(255)
DECLARE @cName nvarchar(255)
DECLARE @sql nvarchar (255)



DECLARE curs CURSOR FOR SELECT DISTINCT rbr_param FROM dbo.parametri_pomocna ORDER BY   rbr_param
OPEN curs
FETCH NEXT FROM curs
INTO @rbr_param
WHILE @@FETCH_STATUS = 0
BEGIN

SET @cName = 'P_'+@rbr_param+'_P'
EXEC('ALTER TABLE dbo.Parametri ADD ' + @cName + ' nvarchar(255)')

    DECLARE vrd CURSOR FOR SELECT DISTINCT vrednost FROM dbo.parametri_pomocna
    OPEN vrd
        FETCH NEXT FROM vrd
        INTO @vrednost
        WHILE @@FETCH_STATUS = 0
        BEGIN
        SET @sql = 'INSERT INTO dbo.Parametri'+(@cName)+ ' SELECT vrednost    FROM dbo.parametri_pomocna WHERE vrednost = '+@vrednost+ ' AND rbr_param = '+@rbr_param

        if exists (select * from INFORMATION_SCHEMA.COLUMNS where   table_name = 'dbo.Parametri' and column_name = '@cName')
        begin
        exec(@sql)
        end
        FETCH NEXT FROM vrd
        INTO @vrednost
        END --end vrd
        CLOSE vrd
        DEALLOCATE vrd

FETCH NEXT FROM curs
INTO @rbr_param

END
CLOSE curs
DEALLOCATE curs

Upvotes: 1

Views: 645

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

You have two problems here:

if exists ( select * from INFORMATION_SCHEMA.COLUMNS 
     where  table_name = 'dbo.Parametri'
      and column_name = '@cName'
)

(1) This view will never have table_name = schema name and table name.

(2) You have enclosed your variable name in single quotes for some reason.

For both of these reasons, your IF condition will never return true.

Try:

IF EXISTS 
(
    SELECT 1 FROM sys.columns 
      WHERE [object_id] = OBJECT_ID('dbo.Parametri')
      AND name = @cName
)

(And here is why I prefer catalog views over INFORMATION_SCHEMA.)

Also this double-nested cursor thing seems quite inefficient and a lot more code than necessary to achieve what I think you're trying to do. How about something like this instead:

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

SELECT @sql = @sql + N'ALTER TABLE dbo.Parametri ADD ' 
   + QUOTENAME('P_' + rbr_param + '_P') + ' NVARCHAR(255);'
  FROM dbo.parametri_pomocna GROUP BY rbr_param;

EXEC sp_executesql @sql;

SET @sql = N'';

SELECT @sql = @sql + N'INSERT dbo.Parametri('+QUOTENAME('P_' + rbr_param + '_P')+ ') 
   SELECT vrednost 
   FROM dbo.parametri_pomocna WHERE rbr_param = ''' + rbr_param + '''
     GROUP BY vrednost;'
   FROM dbo.parametri_pomocna
    GROUP BY rbr_param;

EXEC sp_executesql @sql;

Upvotes: 2

Related Questions