SQL Cursor Examples

DECLARE @server as nvarchar(50), @sql as nvarchar(4000)
DECLARE SharedServers CURSOR FORWARD_ONLY FOR
SELECT server FROM [dyna-dell].ebis_prod.dbo.SharedServerList
OPEN SharedServers FETCH NEXT FROM SharedServers INTO @server BEGIN TRANSACTION SET @sql = ''
WHILE @@FETCH_STATUS = 0 BEGIN IF (@server) <> '' BEGIN
--One itteration of SQL goes HERE
SET @sql = @sql + '

INSERT INTO [' + @server + '].dyna.dbo.TestTable (val1, val2) VALUES (''mReschke'', ''hi'') 

'
END FETCH NEXT FROM SharedServers INTO @server
END exec sp_executesql @sql COMMIT
CLOSE SharedServers DEALLOCATE SharedServers

CREATE PROCEDURE SetProductflag

AS

Declare @EbisCancel datetime, 
    @CarMailCancel datetime, 
    @DealerMenusCancel datetime, 
    @DealerClubsCancel datetime, 
    @LiveCallCancel datetime, 
    @DynaSurveysCancel datetime, 
    @EbisPlusCancel datetime, 
    @DealerID int,
    @EbisSaleDate datetime, 
    @CarMailSaleDate datetime,
    @DealerMenusSaleDate datetime, 
    @DealerClubsSaleDate datetime, 
    @LiveCallSaleDate datetime, 
    @DynaSurveysSaleDate datetime, 
    @EbisPlusSaleDate datetime,
    @CarMailUserName nvarchar(255),
    @Distributor nvarchar(200),
    @DistributorID int
    
Declare ProductFlag CURSOR FORWARD_ONLY FOR SELECT DealerID, DistributorID, EbisCancel, CarMailCancel, DealerMenusCancel, DealerClubsCancel, LiveCallCancel, DynaSurveysCancel, EbisPlusCancel,
EbisSaleDate, CarMailSaleDate, DealerMenusSaleDate, DealerClubsSaleDate, LiveCallSaleDate, DynaSurveysSaleDate, EbisPlusSaleDate, CarMailUserName
 FROM [dyna-sql2].DealerProfile.dbo.tblDPSummary

Open ProductFlag

FETCH NEXT FROM ProductFlag
INTO @DealerID, @DistributorID, @EbisCancel, @CarMailCancel, @DealerMenusCancel, @DealerClubsCancel, @LiveCallCancel, @DynaSurveysCancel, @EbisPlusCancel, 
@EbisSaleDate, @CarMailSaleDate, @DealerMenusSaleDate, @DealerClubsSaleDate, @LiveCallSaleDate, @DynaSurveysSaleDate, @EbisPlusSaleDate, @CarMailUserName

WHILE @@FETCH_STATUS = 0
BEGIN

    Set @Distributor = (Select distributor from [dyna-sql2].DealerProfile.dbo.tblDistributors where id = @DistributorID) 
    Update [dyna-sql2].DealerProfile.dbo.tblDPSummary Set Distributor=@Distributor Where DealerID = @DealerID

if @EbisSaleDate is not null and @EbisCancel is null
    Begin
        Update [dyna-sql2].DealerProfile.dbo.tblDPSummary
        Set EbisProductActive= 1
        Where DealerID = @DealerID
    End

if @CarMailSaleDate is not null and @CarMailCancel is null
    Begin
        Update [dyna-sql2].DealerProfile.dbo.tblDPSummary
        Set CarMailProductActive = 1
        Where DealerID = @DealerID
    End

if @DealerMenusSaleDate is not null and @DealerMenusCancel is null
    Begin
        Update [dyna-sql2].DealerProfile.dbo.tblDPSummary
        Set DealerMenusProductActive = 1
        Where DealerID = @DealerID
    End

if @DealerClubsSaleDate is not null and @DealerClubsCancel is null
    Begin
        Update [dyna-sql2].DealerProfile.dbo.tblDPSummary
        Set DealerClubProductActive = 1
        Where DealerID = @DealerID
    End

if @LiveCallSaleDate is not null and @LiveCallCancel is null
    Begin
        Update [dyna-sql2].DealerProfile.dbo.tblDPSummary
        Set LiveCallProductActive = 1
        Where DealerID = @DealerID
    End

if @DynaSurveysSaleDate is not null and @DynaSurveysCancel is null
    Begin
        Update [dyna-sql2].DealerProfile.dbo.tblDPSummary
        Set DynaSurveysProductActive = 1
        Where DealerID = @DealerID
    End

if @EbisPlusSaleDate is not null and @EbisPlusCancel is null
    Begin
        Update [dyna-sql2].DealerProfile.dbo.tblDPSummary
        Set EbisPlusProductActive = 1
        Where DealerID = @DealerID
    End

FETCH NEXT FROM ProductFlag
INTO @DealerID, @DistributorID, @EbisCancel, @CarMailCancel, @DealerMenusCancel, @DealerClubsCancel, @LiveCallCancel, @DynaSurveysCancel, @EbisPlusCancel, 
@EbisSaleDate, @CarMailSaleDate, @DealerMenusSaleDate, @DealerClubsSaleDate, @LiveCallSaleDate, @DynaSurveysSaleDate, @EbisPlusSaleDate, @CarMailUserName

END

CLOSE ProductFlag

DEALLOCATE ProductFlag

exec RecapCarMailCalculation
GO