CODEWITHSUNDEEP
sql-server
Rodney Wormsbecher
Rodney Wormsbecher

Reputation: 927

SQL, cursor for one time initialization, Must declare scalar variable

I am using a cursor to one time populate a table with all tenants that are in the tenants table. I want to make sure that the tenantId will be set with a name of General in my navigations table. But for some reason it thinks that my variable isn't set.

I tried the following code 

**** Script for SelectTopNRows command from SSMS  ******/

declare @tenantId int
declare @tenantName nvarchar(100)


DECLARE tenantCursor CURSOR FOR  
SELECT Id, [Name]   
FROM [dbo].[Tenant]


OPEN tenantCursor;  
FETCH NEXT FROM tenantCursor  INTO @tenantId, @tenantName;  
WHILE @@FETCH_STATUS = 0  
   BEGIN  

        print @tenantId
        print @tenantName
        SET @tenantId = @tenantId

        Insert INTO [dbo].Navigations ([Name, TenantId]) 
            VALUES ('Algemeen', @tenantId);

        GO

        FETCH NEXT FROM tenantCursor INTO @tenantId, @tenantName; 
   END;  
CLOSE tenantCursor;  
DEALLOCATE tenantCursor;  
GO

I got the follwoing error:

Msg 102, Level 15, State 1, Line 22 Incorrect syntax near ';'.

Msg 137, Level 15, State 2, Line 26 Must declare the scalar variable "@tenantId".

The structure of my tables enter image description here

Any help will be much appreciated. Rodney

Upvotes: 0

Views: 242

Answers (1)

iamdave
iamdave

Reputation: 12243

The simple solution is to just use SQL in the set based manner with which it is designed and run the following instead of your cursor:

insert into dbo.Navigations ([Name]
                            ,TenantId
                            ) 
select 'Algemeen'
      ,Id
from dbo.Tenant;

Outside of the above however, you shouldn't have a go in the middle of your cursor and you have missed some square brackets on your insert:

Insert INTO [dbo].Navigations ([Name, TenantId])

should be

Insert INTO [dbo].Navigations ([Name], [TenantId])

Upvotes: 3

Related Questions