CODEWITHSUNDEEP
sql-server-2008stored-procedurescursor
Abdur Rahim
Abdur Rahim

Reputation: 4021

Using cursors in a stored procedure

Here is my stored procedure : 

CREATE PROCEDURE uspUpdateDataInBapMidTable (
   @companyName varchar(50)
)
AS
BEGIN
    SET @companyName = 'nürnberger'

    DECLARE @tableNameMid varchar(50) , @tableNameIn varchar(50) , @queryToCreateTable nvarchar (500) , @queryToAddColumn nvarchar(500)
    DECLARE @queryToUpdateBapMid nvarchar(500)
    SET @tableNameMid = 'BAP_'+@companyName+'_MID'
    SET @tableNameIn = 'BAP_'+@companyName+'_IN'
    DECLARE @COGI varchar (50)
    SET @COGI = ''

SET NOCOUNT ON
    DECLARE @collectionOfGesellschaft_id nvarchar(100)
    DECLARE myCursor cursor FOR
    SELECT distinct gesellschaft_id from CRM_Wifo_GmbH.dbo.vertrag_168 where gesellschaft like '%nürnberger%'

    open myCursor
    DECLARE @collectionOfGesellschaft_id1 nvarchar(100)

    set @collectionOfGesellschaft_id  = '('
    fetch next from myCursor into @collectionOfGesellschaft_id1
    while @@fetch_status = 0
    begin
    set @collectionOfGesellschaft_id = @collectionOfGesellschaft_id + @collectionOfGesellschaft_id1 + ' ,'

    fetch next from myCursor into @collectionOfGesellschaft_id1
    END
    set @collectionOfGesellschaft_id = SUBSTRING(@collectionOfGesellschaft_id,1,LEN(@collectionOfGesellschaft_id)-1) + ')'
    SET @COGI = @collectionOfGesellschaft_id
    CLOSE myCursor
    DEALLOCATE myCursor                         
    go

SET @queryToUpdateBapMid = N'select distinct b.*,v.vertrag_id,v.VersicherungsscheinNummer 
from CRM_Wifo_GmbH.dbo.vertrag_168 v,temp_universa b where v.VersicherungsscheinNummer like ''%'' + b.VSNR + ''%''  and v.gesellschaft_id in' + @COGI
EXEC(@queryToUpdateBapMid)

END

@companyName is set for my testing purpose. when I execute the cursor only, it runs fine. but in the stored procedure it shows followingerror :

Msg 102, Level 15, State 1, Procedure uspUpdateDataInBapMidTable, Line 33
Incorrect syntax near 'myCursor'.

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

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

I have declare these two variable in the starting of the SP, but they are showing this error. and what is the problem with the line 

 DEALLCOATE myCursor

Upvotes: 1

Views: 3348

Answers (1)

Andriy M
Andriy M

Reputation: 77677

The issue has to do with the word go just after the line DEALLOCATE myCursor.

GO isn't part of either SQL in general or specifically Transact-SQL. It is a keyword recognised by tools like SQL Server Management Studio or sqlcmd as a batch delimiter. When GO is encountered, your script is split at that point and the part preceding GO gets executed separately from the part following it.

The go in your particular script splits it prematurely. It essentially leaves the initial BEGIN unclosed, and that actually is what the error is about, although, granted, the message may seem somewhat confusing.

Anyway, that was your issue, so, just remove the go.

Upvotes: 1

Related Questions