locked
SQL incorrect syntax near 'id' and 'go' RRS feed

  • Question

  • User1452403003 posted

    Hello everyone, 

    I get a Incorrect sytax error near 'id' and 'go' when I try to execute my classified SQL scripts with godaddy. Here are a few posts that resemble the same problem as mine. I've tried all the tricks in these posts, but haven't had any luck.

    http://forums.asp.net/p/1320780/2624445.aspx#2624445<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>

    http://forums.asp.net/p/932670/1097213.aspx#1097213

     

    <o:p> Here is my SQL classified-add file. Sorry it's pretty long. Does anybody see anything wrong? </o:p><o:p></o:p> <o:p>CREATE TABLE [classifieds_Ads] (
     [Id] [int] IDENTITY (1, 1) NOT NULL ,
     [MemberId] [int] NOT NULL ,
     [CategoryId] [int] NOT NULL ,
     [Title] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
     [Description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
     [URL] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
     [Price] [money] NOT NULL ,
     [Location] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
     [ExpirationDate] [smalldatetime] NULL ,
     [DateCreated] [smalldatetime] NOT NULL ,
     [DateApproved] [smalldatetime] NULL ,
     [NumViews] [int] NOT NULL ,
     [NumResponses] [int] NOT NULL ,
     [AdLevel] [int] NOT NULL ,
     [AdStatus] [int] NOT NULL ,
     [AdType] [int] NOT NULL ,
     [PreviewImageId] [int] NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO</o:p>
    <o:p>CREATE TABLE [classifieds_Categories] (
     [Id] [int] IDENTITY (1, 1) NOT NULL ,
     [ParentCategoryId] [int] NULL ,
     [Path] [varchar] (800) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
     [Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
     [NumActiveAds] [int] NOT NULL
    ) ON [PRIMARY]
    GO</o:p>
    <o:p>CREATE TABLE [classifieds_Locations] (
     [Id] [int] IDENTITY (1, 1) NOT NULL ,
     [Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
    ) ON [PRIMARY]
    GO</o:p>
    <o:p>CREATE TABLE [classifieds_Members] (
     [Id] [int] IDENTITY (1, 1) NOT NULL ,
     [AspNetUsername] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
     [AspNetApplicationName] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
     [DateCreated] [smalldatetime] NOT NULL
    ) ON [PRIMARY]
    GO</o:p>
    <o:p>CREATE TABLE [classifieds_Photos] (
     [Id] [int] IDENTITY (1, 1) NOT NULL ,
     [AdId] [int] NOT NULL ,
     [BytesFull] [image] NULL ,
     [BytesSmall] [image] NULL ,
     [BytesMedium] [image] NULL ,
     [IsMainPreview] [bit] NOT NULL ,
     [DateCreated] [smalldatetime] NOT NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO</o:p>
    <o:p>CREATE TABLE [classifieds_SavedAds] (
     [Id] [int] IDENTITY (1, 1) NOT NULL ,
     [MemberId] [int] NOT NULL ,
     [AdId] [int] NOT NULL ,
     [DateCreated] [smalldatetime] NOT NULL
    ) ON [PRIMARY]
    GO</o:p>
    <o:p>ALTER TABLE [classifieds_Ads] WITH NOCHECK ADD
     CONSTRAINT [PK_Ads] PRIMARY KEY  CLUSTERED
     (
      [Id]
     )  ON [PRIMARY]
    GO</o:p>
    <o:p>ALTER TABLE [classifieds_Categories] WITH NOCHECK ADD
     CONSTRAINT [PK_Categories] PRIMARY KEY  CLUSTERED
     (
      [Id]
     )  ON [PRIMARY]
    GO</o:p>
    <o:p>ALTER TABLE [classifieds_Locations] WITH NOCHECK ADD
     CONSTRAINT [PK_Locations] PRIMARY KEY  CLUSTERED
     (
      [Id]
     )  ON [PRIMARY]
    GO</o:p>
    <o:p>ALTER TABLE [classifieds_Members] WITH NOCHECK ADD
     CONSTRAINT [PK_Members] PRIMARY KEY  CLUSTERED
     (
      [Id]
     )  ON [PRIMARY]
    GO</o:p>
    <o:p>ALTER TABLE [classifieds_Photos] WITH NOCHECK ADD
     CONSTRAINT [PK_Photos] PRIMARY KEY  CLUSTERED
     (
      [Id]
     )  ON [PRIMARY]
    GO</o:p>
    <o:p>ALTER TABLE [classifieds_SavedAds] WITH NOCHECK ADD
     CONSTRAINT [PK_SavedAds] PRIMARY KEY  CLUSTERED
     (
      [Id]
     )  ON [PRIMARY]
    GO</o:p>
    <o:p>ALTER TABLE [classifieds_Ads] WITH NOCHECK ADD
     CONSTRAINT [DF_Ads_Price] DEFAULT (0) FOR [Price],
     CONSTRAINT [DF_Ads_DateAdded] DEFAULT (getdate()) FOR [DateCreated],
     CONSTRAINT [DF_Ads_NumViews] DEFAULT (0) FOR [NumViews],
     CONSTRAINT [DF_Ads_NumResponses] DEFAULT (0) FOR [NumResponses],
     CONSTRAINT [DF_Ads_AdLevel] DEFAULT (0) FOR [AdLevel],
     CONSTRAINT [DF_Ads_AdStatus] DEFAULT (0) FOR [AdStatus],
     CONSTRAINT [DF_Ads_IsForSale] DEFAULT (1) FOR [AdType]
    GO</o:p>
    <o:p>ALTER TABLE [classifieds_Categories] WITH NOCHECK ADD
     CONSTRAINT [DF_Categories_NumActiveAds] DEFAULT (0) FOR [NumActiveAds]
    GO</o:p>
    <o:p>ALTER TABLE [classifieds_Members] WITH NOCHECK ADD
     CONSTRAINT [DF_Members_DateAdded] DEFAULT (getdate()) FOR [DateCreated]
    GO</o:p>
    <o:p>ALTER TABLE [classifieds_Photos] WITH NOCHECK ADD
     CONSTRAINT [DF_Photos_IsMainPreview] DEFAULT (0) FOR [IsMainPreview],
     CONSTRAINT [DF_Photos_DateCreated] DEFAULT (getdate()) FOR [DateCreated]
    GO</o:p>
    <o:p>ALTER TABLE [classifieds_SavedAds] WITH NOCHECK ADD
     CONSTRAINT [DF_SavedAds_DateCreated] DEFAULT (getdate()) FOR [DateCreated]
    GO</o:p>
    <o:p>ALTER TABLE [classifieds_Ads] ADD
     CONSTRAINT [FK_Ads_Categories] FOREIGN KEY
     (
      [CategoryId]
     ) REFERENCES [classifieds_Categories] (
      [Id]
     ) ON DELETE CASCADE  ON UPDATE CASCADE ,
     CONSTRAINT [FK_Ads_Members] FOREIGN KEY
     (
      [MemberId]
     ) REFERENCES [classifieds_Members] (
      [Id]
     ) ON DELETE CASCADE  ON UPDATE CASCADE
    GO</o:p>
    <o:p>ALTER TABLE [classifieds_Categories] ADD
     CONSTRAINT [FK_Categories_Categories] FOREIGN KEY
     (
      [ParentCategoryId]
     ) REFERENCES [classifieds_Categories] (
      [Id]
     )
    GO</o:p>
    <o:p>ALTER TABLE [classifieds_Photos] ADD
     CONSTRAINT [FK_Photos_Ads] FOREIGN KEY
     (
      [AdId]
     ) REFERENCES [classifieds_Ads] (
      [Id]
     ) ON DELETE CASCADE  ON UPDATE CASCADE
    GO</o:p>
    <o:p>ALTER TABLE [classifieds_SavedAds] ADD
     CONSTRAINT [FK_SavedAds_Ads] FOREIGN KEY
     (
      [AdId]
     ) REFERENCES [classifieds_Ads] (
      [Id]
     ) ON DELETE CASCADE  ON UPDATE CASCADE ,
     CONSTRAINT [FK_SavedAds_Members] FOREIGN KEY
     (
      [MemberId]
     ) REFERENCES [classifieds_Members] (
      [Id]
     )
    GO</o:p>
    <o:p>SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>CREATE VIEW ClassifiedsView_Ads
    AS
    SELECT     classifieds_Ads.Id, classifieds_Ads.MemberId, classifieds_Ads.CategoryId, classifieds_Ads.Title, classifieds_Ads.Description, classifieds_Ads.URL, classifieds_Ads.Price, classifieds_Ads.Location,
                          classifieds_Ads.ExpirationDate, classifieds_Ads.DateCreated, classifieds_Ads.DateApproved, classifieds_Ads.NumViews, classifieds_Ads.NumResponses, classifieds_Ads.AdLevel,
                          classifieds_Ads.AdStatus, classifieds_Ads.AdType, classifieds_Ads.PreviewImageId, classifieds_Members.AspNetUsername AS MemberName,
                          classifieds_Categories.Name AS CategoryName
    FROM         classifieds_Ads INNER JOIN
                          classifieds_Members ON classifieds_Ads.MemberId = classifieds_Members.Id INNER JOIN
                          classifieds_Categories ON classifieds_Ads.CategoryId = classifieds_Categories.Id</o:p>
    <o:p>GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>CREATE VIEW PhotosView
    AS
    SELECT     Id, AdId, IsMainPreview, DateCreated
    FROM         classifieds_Photos</o:p>
    <o:p>GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>CREATE PROCEDURE CountAdResponsesByStatus
    (
     @AdStatus int = NULL
    )
    AS
    SET NOCOUNT ON;</o:p>
    <o:p> SELECT      ISNULL(SUM(NumResponses), 0) AS Count
     FROM         classifieds_Ads
     WHERE (@AdStatus IS NULL OR AdStatus = @AdStatus)
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>CREATE PROCEDURE CountAdViewsByStatus
    (
     @AdStatus int
    )
    AS
    SET NOCOUNT ON;</o:p>
    <o:p> SELECT     SUM(NumViews) AS Count
     FROM         classifieds_Ads
     WHERE     (@AdStatus IS NULL OR AdStatus = @AdStatus)
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS OFF
    GO</o:p>
    <o:p>CREATE PROCEDURE CountAdsByCategory
    @CategoryId int,
    @AdStatus int = 0
    AS
    SELECT Count(Id) AS AdCount FROM classifieds_Ads
    WHERE
     (@AdStatus = 0 OR AdStatus = @AdStatus) AND
     (@CategoryId = 0
      OR
      CategoryId IN (
      SELECT Id FROM classifieds_Categories
      WHERE Path LIKE
         (SELECT Path
          FROM classifieds_Categories
          WHERE Id = @CategoryId ) + '%'))
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>CREATE PROCEDURE CountAdsByStatus
    (
     @AdStatus int = NULL,
     @MinDateCreated datetime = NULL
    )
    AS
    SET NOCOUNT ON;</o:p>
    <o:p> SELECT     COUNT(*) AS Count
     FROM classifieds_Ads
     WHERE (@AdStatus IS NULL OR AdStatus = @AdStatus) AND
     (@MinDateCreated IS NULL OR DateCreated > @MinDateCreated)
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>CREATE PROCEDURE CountLocations
    AS
     SET NOCOUNT ON;
    SELECT     COUNT(*) AS Count
    FROM         classifieds_Locations</o:p>
    <o:p>GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>CREATE PROCEDURE CountMembersByDateRange
    (
     @StartDate smalldatetime = NULL,
     @EndDate smalldatetime = NULL
    )
    AS
    SET NOCOUNT ON;</o:p>
    <o:p> SELECT     COUNT(*) AS Count
     FROM         classifieds_Members
     WHERE  
     (@StartDate IS NULL OR DateCreated > @StartDate) AND
     (@EndDate IS NULL OR DateCreated < @EndDate)
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>CREATE PROCEDURE CountTopCategories
    AS
     SET NOCOUNT ON;
    SELECT     COUNT(*) AS Count
    FROM         classifieds_Categories
    WHERE     (ParentCategoryId IS NULL)</o:p>
    <o:p>GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>CREATE PROCEDURE CreateCategory
    (
     @ParentCategoryId int,
     @Name nvarchar(50)
    )
    AS
     SET NOCOUNT OFF;
    INSERT INTO [classifieds_Categories] ([ParentCategoryId], [Name] ) VALUES (@ParentCategoryId, @Name)</o:p>
    <o:p>GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS OFF
    GO</o:p>
    <o:p>CREATE PROCEDURE ExpireAd
    @AdId int,
    @MemberId int,
    @AdStatus int
    AS
    UPDATE classifieds_Ads
    SET
     AdStatus = @AdStatus,
     ExpirationDate = getdate()
    WHERE
     Id = @AdId AND MemberId = @MemberId
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>CREATE PROCEDURE GetAdById
    (
     @Id int
    )
    AS
     SET NOCOUNT ON;
    SELECT     classifieds_Ads.Id, classifieds_Ads.MemberId, classifieds_Ads.CategoryId, classifieds_Ads.Title, classifieds_Ads.Description, classifieds_Ads.URL, classifieds_Ads.Price, classifieds_Ads.Location,
                          classifieds_Ads.ExpirationDate, classifieds_Ads.DateCreated, classifieds_Ads.DateApproved, classifieds_Ads.NumViews, classifieds_Ads.NumResponses, classifieds_Ads.AdLevel,
                          classifieds_Ads.AdStatus, classifieds_Ads.AdType, classifieds_Ads.PreviewImageId, classifieds_Members.AspNetUsername AS MemberName,
                          classifieds_Categories.Name AS CategoryName
    FROM         classifieds_Ads INNER JOIN
                          classifieds_Members ON classifieds_Ads.MemberId = classifieds_Members.Id INNER JOIN
                          classifieds_Categories ON classifieds_Ads.CategoryId = classifieds_Categories.Id
    WHERE classifieds_Ads.Id = @Id</o:p>
    <o:p>GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS OFF
    GO</o:p>
    <o:p>CREATE PROCEDURE GetAdsByRandomOrder
    @NumRecords int,
    @AdStatus int,
    @AdLevel int
    AS
    SET ROWCOUNT @NumRecords
    SELECT * FROM ClassifiedsView_Ads
    WHERE AdStatus = @AdStatus AND AdLevel = @AdLevel And PreviewImageId IS NOT NULL
    ORDER BY newid()
    SET ROWCOUNT 0
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS OFF
    GO</o:p>
    <o:p>CREATE PROCEDURE GetAdsByStatus
    (
     @AdStatus int,
     @MemberId int = 0
    )
    AS
    SET NOCOUNT ON;</o:p>
    <o:p>SELECT * FROM ClassifiedsView_Ads
    WHERE
     AdStatus = @AdStatus AND
     (@MemberId = 0 OR MemberId = @MemberId)
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS OFF
    GO</o:p>
    <o:p>CREATE PROCEDURE GetAllAdsByQuery
    @LimitResultCount int = 50,
    @CategoryId int = 0,
    @MemberId int = 0,
    @MaxPrice money = -1,
    @SearchTerm nvarchar(50) = N'',
    @Location nvarchar(50) = N'',
    @AdType int = 0,
    @AdStatus int = 0,
    @AdLevel int = 0,
    @MinDateCreated smalldatetime = NULL,
    @MustHaveImage bit = false
    AS
     SET ROWCOUNT @LimitResultCount</o:p>
    <o:p> SELECT *
     FROM ClassifiedsView_Ads
     WHERE
     (@CategoryId = 0 OR
     CategoryId IN (
     SELECT Id FROM classifieds_Categories
     WHERE Path LIKE
       (SELECT Path
        FROM classifieds_Categories
        WHERE Id = @CategoryId ) + '%'
     )) AND
     (@MaxPrice = -1 OR Price <= @MaxPrice) AND
     (@MustHaveImage = 0 OR PreviewImageId IS NOT NULL) AND
     (@AdStatus = 0 OR AdStatus = @AdStatus) AND
     (@AdLevel = 0 OR AdLevel = @AdLevel) AND
     (@AdType = 0 OR AdType = @AdType) AND
     (@MemberId = 0 OR MemberId = @MemberId) AND
     (@MinDateCreated IS NULL OR DateCreated > @MinDateCreated) AND
     Title LIKE '%' + @SearchTerm + '%' AND
     Location LIKE '%' + @Location + '%'
        ORDER BY DateCreated DESC
       
     SET ROWCOUNT 0
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS OFF
    GO</o:p>
    <o:p>CREATE PROCEDURE GetAllLocations
    AS
    SELECT     Id, Name
    FROM         classifieds_Locations
    ORDER BY Name</o:p>
    <o:p>GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>CREATE PROCEDURE GetCategoriesByParentId
    (
     @ParentCategoryId int = 0
    )
    AS
    SET NOCOUNT ON;
    SELECT     [Id], [ParentCategoryId], [Name], NumActiveAds
    FROM         classifieds_Categories
    WHERE    (ParentCategoryId = @ParentCategoryId)
     OR (@ParentCategoryId = 0 AND ParentCategoryId IS NULL)
    ORDER BY Name
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>CREATE PROCEDURE GetCategoryById
    (
     @Id int
    )
    AS
     SET NOCOUNT ON;
    SELECT Id, ParentCategoryId, Name, NumActiveAds FROM classifieds_Categories WHERE Id = @Id</o:p>
    <o:p>GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS OFF
    GO</o:p>
    <o:p>CREATE PROCEDURE GetExpiredAds
    @ExpirationDate smalldatetime = getdate,
    @AdStatus int = 0
    AS
    SELECT * FROM ClassifiedsView_Ads
    WHERE
    ExpirationDate < @ExpirationDate AND
    (@AdStatus = 0 OR AdStatus = @AdStatus)
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>CREATE PROCEDURE GetMemberByUsername
    (
     @AspNetUsername nvarchar(256),
     @AspNetApplicationName nvarchar(256)
    )
    AS
     SET NOCOUNT ON;
    SELECT     Id, AspNetUsername, AspNetApplicationName, DateCreated
    FROM         classifieds_Members
    WHERE     (AspNetUsername = @AspNetUsername) AND (AspNetApplicationName = @AspNetApplicationName)</o:p>
    <o:p>GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS OFF
    GO</o:p>
    <o:p>CREATE PROCEDURE GetParentCategoriesById
    @Id int
    AS
    SELECT Id, ParentCategoryId, Name, NumActiveAds
    FROM classifieds_Categories
    WHERE (SELECT Path
           FROM classifieds_Categories
           WHERE Id = @Id) LIKE Path + '%'
    ORDER BY Path
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS OFF
    GO</o:p>
    <o:p>CREATE PROCEDURE GetPhotoById
    @Id int,
    @Size int
    AS
    IF @Size = 1
     SELECT      *
     FROM         PhotosView
     WHERE Id = @Id
    ELSE
     SELECT      *
     FROM         PhotosView
     WHERE Id = @Id
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS OFF
    GO</o:p>
    <o:p>CREATE PROCEDURE GetPhotoBytesById
    @Id int,
    @Size int
    AS
    IF @Size = 3
     SELECT      BytesFull AS Bytes
     FROM         classifieds_Photos
     WHERE Id = @Id
    ELSE IF @Size = 2
     SELECT      BytesMedium AS Bytes
     FROM         classifieds_Photos
     WHERE Id = @Id
    ELSE
     SELECT      BytesSmall AS Bytes
     FROM         classifieds_Photos
     WHERE Id = @Id
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>CREATE PROCEDURE GetPhotosByAdId
    (
     @AdId int
    )
    AS
     SET NOCOUNT ON;
    SELECT     classifieds_Photos.Id, classifieds_Photos.AdId, IsMainPreview, DateCreated
    FROM         classifieds_Photos
    WHERE     (AdId = @AdId)
    ORDER BY IsMainPreview DESC, DateCreated
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS OFF
    GO</o:p>
    <o:p>CREATE PROCEDURE GetPhotosByAdStatus
    @AdStatus int
    AS
    DECLARE @NullBytes binary
    SET @NullBytes = NULL</o:p>
    <o:p>SELECT     classifieds_Photos.Id, classifieds_Photos.AdId, classifieds_Photos.IsMainPreview,
                          classifieds_Photos.DateCreated
    FROM         classifieds_Photos INNER JOIN
                          classifieds_Ads ON classifieds_Photos.Id = classifieds_Ads.Id
    WHERE     (classifieds_Ads.AdStatus = @AdStatus)
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS OFF
    GO</o:p>
    <o:p>CREATE PROCEDURE GetSavedAds
    @MemberId int
    AS
    SELECT     classifieds_Ads.Id, classifieds_Ads.MemberId, classifieds_Ads.CategoryId, classifieds_Ads.Title, classifieds_Ads.Description, classifieds_Ads.URL, classifieds_Ads.Price, classifieds_Ads.Location,
                          classifieds_Ads.ExpirationDate, classifieds_Ads.DateCreated, classifieds_Ads.DateApproved, classifieds_Ads.NumViews, classifieds_Ads.NumResponses, classifieds_Ads.AdLevel,
                          classifieds_Ads.AdStatus, classifieds_Ads.AdType, classifieds_Ads.PreviewImageId, classifieds_Members.AspNetUsername AS MemberName,
                          classifieds_Categories.Name AS CategoryName
    FROM         classifieds_Ads INNER JOIN
                          classifieds_SavedAds ON classifieds_Ads.Id = classifieds_SavedAds.AdId INNER JOIN
                          classifieds_Members ON classifieds_Ads.MemberId = classifieds_Members.Id INNER JOIN
                          classifieds_Categories ON classifieds_Ads.CategoryId = classifieds_Categories.Id
    WHERE classifieds_SavedAds.MemberId = @MemberId
    ORDER BY classifieds_SavedAds.DateCreated DESC
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>CREATE PROCEDURE InsertAd
    (
     @MemberId int,
     @CategoryId int,
     @Title nvarchar(100),
     @Description ntext,
     @URL nvarchar(500),
     @Price money,
     @Location nvarchar(50),
     @ExpirationDate smalldatetime = NULL,
     @DateCreated smalldatetime = NULL,
     @DateApproved smalldatetime = NULL,
     @NumViews int,
     @NumResponses int,
     @AdLevel int,
     @AdStatus int,
     @AdType int
    )
    AS
    DECLARE @Id int;
    SET NOCOUNT OFF;
    INSERT INTO classifieds_Ads
                          (MemberId, CategoryId, Title, Description, URL, Price, Location, ExpirationDate, DateCreated, DateApproved, NumViews, NumResponses, AdLevel,
                          AdStatus, AdType)
    VALUES     (@MemberId,@CategoryId,@Title,@Description,@URL,@Price,@Location,@ExpirationDate,@DateCreated,@DateApproved,@NumViews,@NumResponses,@AdLevel,@AdStatus,@AdType)
    SET @Id = @@IDENTITY
    SELECT  @Id  AS [Id]
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>CREATE PROCEDURE InsertCategory
    (
     @ParentCategoryId int,
     @Name nvarchar(50)
    )
    AS
    SET NOCOUNT OFF;
    DECLARE @Id int;
    INSERT INTO classifieds_Categories
                          (ParentCategoryId, Name)
    VALUES     (@ParentCategoryId,@Name)
    SET @Id = @@IDENTITY;
    SELECT @Id AS [Id]</o:p>
    <o:p>GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS OFF
    GO</o:p>
    <o:p>CREATE PROCEDURE InsertLocation
    @Name nvarchar(50)
    AS
    INSERT INTO classifieds_Locations (Name)
    VALUES (@Name)</o:p>
    <o:p>GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>CREATE PROCEDURE InsertMember
     @AspNetUsername nvarchar(256),
     @AspNetApplicationName nvarchar(256),
     @DateCreated smalldatetime = getdate
    AS
    DECLARE @Id int;
    SET NOCOUNT ON;
    INSERT INTO [classifieds_Members] ([AspNetUsername], [AspNetApplicationName], [DateCreated]) VALUES (@AspNetUsername, @AspNetApplicationName, @DateCreated);
    SET @Id = @@IDENTITY
    SELECT  @Id  AS [Id]</o:p>
    <o:p>GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS OFF
    GO</o:p>
    <o:p>CREATE PROCEDURE InsertPhoto
    @AdId int,
    @BytesFull image = NULL,
    @BytesMedium image = NULL,
    @BytesSmall image = NULL,
    @IsMainPreview bit = 0,
    @DateCreated smalldatetime = getdate
    AS
    DECLARE @Id int;
    INSERT classifieds_Photos
    (AdId, BytesFull, BytesMedium, BytesSmall, IsMainPreview, DateCreated)
    VALUES
    (@AdId, @BytesFull, @BytesMedium, @BytesSmall, @IsMainPreview, @DateCreated)
    SET @Id = @@IDENTITY;</o:p>
    <o:p>IF @IsMainPreview = 1
    UPDATE classifieds_Ads SET PreviewImageId = @Id WHERE Id = @AdId </o:p>
    <o:p>SELECT @Id AS [Id]
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS OFF
    GO</o:p>
    <o:p>CREATE PROCEDURE InsertSavedAd
    @AdId int,
    @MemberId int
    AS
    SET NOCOUNT ON;
    SELECT AdId FROM classifieds_SavedAds WHERE AdId = @AdId AND MemberId = @MemberId
    IF @@ROWCOUNT = 0
    INSERT INTO classifieds_SavedAds
     (AdId, MemberId, DateCreated)
    VALUES
     (@AdId, @MemberId, getdate())</o:p>
    <o:p>GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS OFF
    GO</o:p>
    <o:p>CREATE PROCEDURE MoveAds
    @CurrentCategoryId int,
    @NewCategoryId int
    AS
    UPDATE classifieds_Ads
    SET
     CategoryId = @NewCategoryId
    WHERE
     CategoryId = @CurrentCategoryId
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO</o:p>
    <o:p>SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS OFF
    GO</o:p>
    <o:p>CREATE PROCEDURE MoveCategory
    @CategoryId int,
    @NewParentCategoryId int
    AS
    declare @NumActiveAdsInCategory int;
    declare @OldPath varchar(800);
    declare @NewPath varchar(800);</o:p>
    <o:p>-- Stored Procedure will not execute, if:</o:p><o:p>-- 1st case: Current Category = New Parent Category (it cannot become a parent of itself)
    IF @CategoryId = @NewParentCategoryId
     RETURN -1</o:p>
    <o:p>-- 2nd case: the input category is a parent of the new category
    -- (a parent category cannot become a child category under one of its own children)
    IF EXISTS  (SELECT Id FROM classifieds_Categories
      WHERE
       (SELECT Path
       FROM classifieds_Categories
       WHERE
       Id = @CategoryId) LIKE Path + '%'
      AND
       Id = @NewParentCategoryId)
     RETURN -1 -- exits</o:p>
    <o:p>SELECT @OldPath = ParentCategories.Path</o:p><o:p>FROM         classifieds_Categories INNER JOIN
                          classifieds_Categories ParentCategories ON classifieds_Categories.ParentCategoryId = ParentCategories.Id
    WHERE     (classifieds_Categories.Id = @CategoryId)</o:p>
    <o:p>SELECT @NewPath =
     CASE WHEN
      @NewParentCategoryId IS NULL THEN '.'
     ELSE
      Path
     END
     FROM classifieds_Categories
     WHERE @NewParentCategoryId is NULL OR Id = @NewParentCategoryId</o:p>
    <o:p>IF @OldPath IS NULL
    BEGIN
     SELECT @OldPath = Path FROM classifieds_Categories WHERE Id = @CategoryId
     SET @NewPath = @NewPath + CAST(@CategoryId AS VARCHAR(10)) + '.'
    END</o:p>
    <o:p>


    SELECT @NumActiveAdsInCategory  = Count(Id) FROM ClassifiedsView_Ads WHERE
    AdStatus >= 100 AND
    ExpirationDate > getdate() AND
    ClassifiedsView_Ads.CategoryId IN (SELECT Id FROM classifieds_Categories
    WHERE Path LIKE
      (SELECT Path
       FROM classifieds_Categories
       WHERE Id = @CategoryId ) + '%' )

    DECLARE @NegativeCount int;
    SET @NegativeCount = 0 - @NumActiveAdsInCategory
    EXEC UpdateCategoryAdCounts @CategoryId, @NegativeCount

    UPDATE classifieds_Categories
    SET Path =
     REPLACE(Path, @OldPath, @NewPath)

    WHERE Path LIKE

      (SELECT Path
       FROM classifieds_Categories
       WHERE Id = @CategoryId ) + '%'

    EXEC UpdateCategoryAdCounts @CategoryId, @NumActiveAdsInCategory

    UPDATE classifieds_Categories SET ParentCategoryId = @NewParentCategoryId WHERE Id = @CategoryId

    RETURN 1
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS OFF
    GO

    CREATE PROCEDURE RelistAd
     @AdId int,
     @CategoryId int,
     @Title nvarchar(100),
     @Description ntext,
     @URL nvarchar(500),
     @Price money,
     @Location nvarchar(50),
     @ExpirationDate smalldatetime = NULL,
     @DateCreated smalldatetime = NULL,
     @DateApproved smalldatetime = NULL,
     @AdLevel int,
     @AdStatus int,
     @AdType int
    AS
    UPDATE classifieds_Ads
    SET

     CategoryId = @CategoryId,
     Title = @Title ,
     Description = @Description ,
     URL = @URL,
     Price = @Price,
     Location = @Location,
     ExpirationDate = @ExpirationDate ,
     -- DateCreated = GetDate(),    
     DateApproved = @DateApproved,
     AdLevel = @AdLevel,
     AdStatus = @AdStatus,
     AdType = @AdType,
     NumResponses = 0,
     NumViews = 0 

    WHERE Id = @AdId

    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS OFF
    GO

    CREATE PROCEDURE RemoveAd
    @AdId int
    AS
    DELETE FROM classifieds_Photos WHERE AdId = @AdId
    DELETE FROM classifieds_SavedAds WHERE AdId = @AdId
    DELETE FROM classifieds_Ads WHERE Id = @AdId
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS OFF
    GO

    CREATE PROCEDURE RemoveAdsByStatus
    @AdStatus int
    AS
    SET XACT_ABORT ON
    SET NOCOUNT ON
    BEGIN TRANSACTION
    DELETE FROM classifieds_Photos WHERE AdId IN (SELECT Id FROM classifieds_Ads WHERE AdStatus = @AdStatus)
    DELETE FROM classifieds_SavedAds WHERE AdId IN (SELECT Id FROM classifieds_Ads WHERE AdStatus = @AdStatus)
    DELETE FROM classifieds_Ads WHERE AdStatus = @AdStatus
    COMMIT
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS OFF
    GO

    CREATE PROCEDURE RemoveCategory
    (
     @Id int
    )
    AS
    SET NOCOUNT ON;

    DECLARE @returnval int
    SELECT @returnval = 1

    -- cannot remove a category with ads in it (they must be moved first)
    ---- return -1
    IF EXISTS(SELECT Id FROM classifieds_Ads WHERE
       (classifieds_Ads.CategoryId IN
        (SELECT Id FROM classifieds_Categories
        WHERE Path LIKE
         (SELECT Path
         FROM classifieds_Categories
         WHERE Id = @Id ) + '%')))
     SELECT @returnval = -1

    -- cannot remove category if it is a parent of other Categories
    ---- return -2
    IF EXISTS(SELECT Id FROM classifieds_Categories WHERE ParentCategoryId = @Id)
        SELECT @returnval = -2

    -- can remove category
    IF (@returnval = 1)
      DELETE FROM [classifieds_Categories] WHERE [Id] = @Id

    SELECT @returnval

    RETURN 0

    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS OFF
    GO

    CREATE PROCEDURE RemoveLocation
    @Id int
    AS
    DELETE FROM classifieds_Locations
    WHERE [Id] = @Id

    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS OFF
    GO

    CREATE PROCEDURE RemovePhoto
    @Id int
    AS
    DECLARE @AdId int

    SELECT @AdId = AdId
    FROM classifieds_Photos
    WHERE Id = @Id

    DELETE FROM classifieds_Photos WHERE Id = @Id

    IF NOT EXISTS(SELECT Id FROM classifieds_Photos WHERE AdId = @AdId AND IsMainPreview = 1)
    BEGIN
     UPDATE
      classifieds_Photos SET IsMainPreview = 1
      WHERE Id =
      (SELECT TOP 1 Id FROM classifieds_Photos WHERE AdId = @AdId)
     UPDATE   
      classifieds_Ads SET PreviewImageId =
            (SELECT Id
             FROM classifieds_Photos
             WHERE (AdId = @AdId) AND (IsMainPreview = 1)
             )
      WHERE (Id = @AdId)
    END
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS OFF
    GO

    CREATE PROCEDURE RemovePhotosByAdStatus
    @AdStatus int
    AS
    UPDATE classifieds_Ads SET PreviewImageId = NULL WHERE AdStatus = @AdStatus
    DELETE FROM classifieds_Photos
    WHERE AdId IN
     (SELECT Id FROM classifieds_Ads WHERE AdStatus = @AdStatus)
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS OFF
    GO

    CREATE PROCEDURE RemoveSavedAd
    @AdId int,
    @MemberId int
    AS
    DELETE FROM classifieds_SavedAds
    WHERE AdId = @AdId AND MemberId = @MemberId
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS OFF
    GO

    CREATE PROCEDURE SetAdPreviewPhoto
    @PhotoId int,
    @AdId int
    AS
    UPDATE classifieds_Photos SET
     IsMainPreview = 1 - ABS(SIGN(@PhotoId - Id))
    WHERE AdId = @AdId

    UPDATE classifieds_Ads
     SET PreviewImageId = @PhotoId
    WHERE
     Id = @AdId
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS OFF
    GO

    CREATE PROCEDURE UpdateAd
     @Id int,
     @MemberId int,
     @Title nvarchar(100),
     @Description ntext,
     @URL nvarchar(500),
     @Price money,
     @Location nvarchar(50)
    AS
    UPDATE classifieds_Ads
    SET
     Title = @Title,
     [Description] = @Description,
     Url = @URL,
     Price = @Price,
     Location = @Location
    WHERE
     Id = @Id
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS OFF
    GO

    CREATE PROCEDURE UpdateAdCategory
    @Id int,
    @CategoryId int
    AS
    UPDATE classifieds_Ads
    SET
     CategoryId = @CategoryId
    WHERE
     Id = @Id
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS OFF
    GO

    CREATE PROCEDURE UpdateAdLevel
    @Id int,
    @AdLevel int
    AS
    UPDATE classifieds_Ads
    SET
     AdLevel = @AdLevel
    WHERE
     Id = @Id
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS OFF
    GO

    CREATE PROCEDURE UpdateAdStats
    @AdId int,
    @NumberToAddToViews int = 0,
    @NumberToAddToResponses int = 0
    AS
    UPDATE classifieds_Ads SET
     NumViews = NumViews + @NumberToAddToViews,
     NumResponses = NumResponses + @NumberToAddToResponses
    WHERE
     Id = @AdId

    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS OFF
    GO

    CREATE PROCEDURE UpdateAdStatus
    @Id int,
    @AdStatus int
    AS
    UPDATE classifieds_Ads
    SET
     AdStatus = @AdStatus
    WHERE
     Id = @Id
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO

    CREATE PROCEDURE UpdateCategory
    (
     @Id int,
     @ParentCategoryId int = NULL,
     @Name nvarchar(50),
     @NumActiveAds int
    )
    AS
     SET NOCOUNT OFF;
    UPDATE [classifieds_Categories] SET [ParentCategoryId] = @ParentCategoryId,  [Name] = @Name, [NumActiveAds] = @NumActiveAds WHERE (([Id] = @Id))

    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS OFF
    GO

    CREATE PROCEDURE UpdateCategoryAdCounts
    @LeafCategoryId int,
    @NumberToAdd int
    AS

    SET NOCOUNT ON

    UPDATE classifieds_Categories SET NumActiveAds = NumActiveAds + @NumberToAdd
    WHERE Id IN
     (SELECT Id
     FROM classifieds_Categories
     WHERE (SELECT Path
            FROM classifieds_Categories
            WHERE Id = @LeafCategoryId) LIKE Path + '%')
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS OFF
    GO

    CREATE PROCEDURE UpdateCategoryName
    @Id int,
    @Name nvarchar(50)
    AS
    UPDATE classifieds_Categories
    SET Name = @Name
    WHERE Id = @Id
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS OFF
    GO

    CREATE PROCEDURE UpdateLocation
    @Id int,
    @Name nvarchar(50)
    AS
    UPDATE classifieds_Locations
    SET [Name] = @Name
    WHERE [Id] = @Id

    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    CREATE TRIGGER AdDeleted
    ON classifieds_Ads
    FOR DELETE
    AS

    DECLARE @CurrentAdId AS int
    SET @CurrentAdId =  (SELECT TOP 1 [Id]
       FROM Deleted
       ORDER BY [Id])


     -- loop through each row 
     WHILE @CurrentAdId IS NOT NULL
     BEGIN
     
      DECLARE @AdStatus     int;
      DECLARE @CategoryId int;
      
      SELECT @AdStatus = AdStatus, @CategoryId = CategoryId
      FROM Deleted
      WHERE Id = @CurrentAdId
      
      IF @AdStatus >= 100
      BEGIN
          -- decrement the affected category counts by adding a -1
          EXEC UpdateCategoryAdCounts @CategoryId, -1
      END

      SET @CurrentAdId =  (SELECT TOP 1 [Id]
         FROM Deleted
         WHERE [Id] > @CurrentAdId
         ORDER BY [Id])

    END

     


    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    CREATE TRIGGER AdInserted
    ON classifieds_Ads
    FOR INSERT
    AS

    DECLARE @CurrentAdId AS int

    SET @CurrentAdId =  (SELECT TOP 1 [Id]
       FROM Inserted
       ORDER BY [Id])


     -- loop through each row 
     WHILE @CurrentAdId IS NOT NULL
     BEGIN
      
      
      DECLARE @AdStatus   int;
      DECLARE @CategoryId int;

      SELECT @AdStatus = AdStatus, @CategoryId = CategoryId
      FROM Inserted
      WHERE Id = @CurrentAdId;

      IF @AdStatus >= 100
      BEGIN
          -- increment the affected category counts by adding +1
          EXEC UpdateCategoryAdCounts @CategoryId, 1
      END

      SET @CurrentAdId =  (SELECT TOP 1 [Id]
          FROM Inserted
          WHERE [Id] > @CurrentAdId
          ORDER BY [Id])

     END

     

    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    CREATE TRIGGER AdUpdated
    ON classifieds_Ads
    FOR UPDATE
    AS

    DECLARE @CurrentAdId AS int

    SET @CurrentAdId =  (SELECT TOP 1 [Id]
       FROM Inserted
       ORDER BY [Id])


     -- loop through each row 
     WHILE @CurrentAdId IS NOT NULL
     BEGIN

      DECLARE @AdStatus         int;
      DECLARE @CategoryId         int;
      DECLARE @OldAdStatus     int;
      DECLARE @OldCategoryId     int;
     
      SELECT      @AdStatus = Inserted.AdStatus,
             @CategoryId = Inserted.CategoryId
      FROM        Inserted
      WHERE Id = @CurrentAdId
                         
      SELECT      @OldAdStatus = Deleted.AdStatus,
                  @OldCategoryId = Deleted.CategoryId
      FROM        Deleted
      WHERE Id = @CurrentAdId

      IF UPDATE(AdStatus)
      BEGIN
          IF (@AdStatus < 100 AND @OldAdStatus >= 100)
              EXEC UpdateCategoryAdCounts @OldCategoryId, -1
          ELSE IF (@AdStatus >= 100 AND @OldAdStatus < 100)
              EXEC UpdateCategoryAdCounts @OldCategoryId, 1
      END
      
      IF UPDATE(CategoryId)
      BEGIN
        IF (@OldCategoryId <> @CategoryId)
           BEGIN
               EXEC UpdateCategoryAdCounts @CategoryId, 1
               EXEC UpdateCategoryAdCounts @OldCategoryId, -1
           END
      END
       
      SET @CurrentAdId =  (SELECT TOP 1 [Id]
         FROM Inserted
         WHERE [Id] > @CurrentAdId
         ORDER BY [Id])

     END

     


    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO

    CREATE TRIGGER OnCategoryInsert
    ON classifieds_Categories
    FOR INSERT
    AS
    DECLARE @numrows AS int
    SET @numrows = @@rowcount
    IF @numrows > 1
    BEGIN
      RAISERROR('Only single row inserts are supported', 16, 1)
      ROLLBACK TRAN
    END
    ELSE
    IF @numrows = 1
    BEGIN
    UPDATE classifieds_Categories
    SET   
        Path =
          CASE
              WHEN Inserted.ParentCategoryId IS NULL THEN '.'
              ELSE ParentCategory.Path
              END + CAST(Inserted.Id AS varchar(10)) + '.'
    FROM        Inserted INNER JOIN
                          classifieds_Categories ON Inserted.Id = classifieds_Categories.Id
                          LEFT OUTER JOIN
                          classifieds_Categories AS ParentCategory ON classifieds_Categories.ParentCategoryId = ParentCategory.Id
                       
    END

    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO
     </o:p>

     

    Wednesday, January 14, 2009 7:46 PM

All replies

  • User1452403003 posted

    All,

    I found the fix shortly after I posting. The file format needs to be in UTF-8. I changed my files to this structure and tried to executed the scripts. I received the "incorrect syntax near" error (you must click the "show execution plan" check box to see this error if hosting with godaddy) so i never bothered to check to see if the tables and procedures etc..were created. I just happen to look at the tables after numerous attempts in loading my database and they were there. Make sure you check you tables and stored procedures after each time you run your scripts.

     Dan

     

    Wednesday, January 14, 2009 11:10 PM