Database

ALTER PROCEDURE [dbo].[spVendorUpdateVendor] 
-- Add the parameters for the stored procedure here
                                @VENDORID          INT,
                                @ACTIVE            BIT,
                                @COUNTRYID         INT,
                                @VENDORCODE        NVARCHAR(10),
                                @NAME              NVARCHAR(50),
                                @WEBSITE1          NVARCHAR(50),
                                @WEBSITE2          NVARCHAR(50),
                                @VIACODE           NVARCHAR(10),
                                @TERMS             SMALLINT,
                                @USECOMPCODE       BIT,
                                @DISCOUNTPERCENT   SMALLINT,
                                @DELETEDBYID       INT,     -- Pass 0 if not deleting the record
                                @DELETEDON         DATETIME,
                                @NONUS             BIT,
                                @NOTES             NVARCHAR(MAX)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
	BEGIN TRY
                                
        	IF EXISTS(SELECT ID FROM VENDOR_MASTER WHERE ID = @VENDORID)
                	BEGIN                                                  --<< SQL SERVER SAYS THE ERROR IS ON THIS LINE

			        IF (@DELETEDBYID > 0) AND (NOT EXISTS(SELECT USERID FROM USERS WHERE USERID = @DELETEDBYID))
                                	BEGIN
                                        	RETURN 2; -- DeletedById does not exist in USERS table
                                        END

                                IF (NOT EXISTS(SELECT * FROM COUNTRY_CODES CC WHERE CC.COUNTRYCODE = @COUNTRYID))
                                	BEGIN
                                        	RETURN 3; -- Country Code does not exist in COUNTRY_CODES table
                                        END

                                IF (NOT EXISTS(SELECT SHIPVIACODE FROM ORDER_HANDLE_MASTER_SHIPVIA_CODES WHERE SHIPVIACODE = @VIACODE))
                                        BEGIN
                                                RETURN 4; -- VIA CODE does not exist.
                                        END

                                UPDATE VENDOR_MASTER SET	ACTIVE          = @ACTIVE,
								COUNTRYID       = @COUNTRYID,
								VENDORCODE      = @VENDORCODE,
   								NAME            = @NAME,
								WEBSITE1        = @WEBSITE1,
								WEBSITE2        = @WEBSITE2,
								VIACODE         = @VIACODE,
								TERMS           = @TERMS,
								USECOMPCODE     = @USECOMPCODE,
								DISCOUNTPERCENT = @DISCOUNTPERCENT,
								DELETEDBYID     = @DELETEDBYID,
								DELETEDON       = @DELETEDON,
								NONUS           = @NONUS,
								NOTES           = @NOTES

                                WHERE ID = @VENDORID;
			END
		ELSE -- IF EXISTS VENDORID
                	BEGIN
                        	RETURN 1; -- VendorId does not exist in Vendor Master table
                        END
	END TRY

        BEGIN CATCH
        	SELECT  ERROR_NUMBER()		[ErrorNumber],
                        ERROR_PROCEDURE()   	[ErrorProcedure],
                        ERROR_LINE()            [ErrorLine],
                        ERROR_MESSAGE()         [ErrorMessage]

                RETURN ERROR_NUMBER();
 	END CATCH

	RETURN 0;
END

pResults =
(
    from d in context.PRODUCT_DEPARTMENT
    join da1 in context.PRODUCT_DEPARTMENT_AVATARS on d.Avatar_Primary equals da1.AvatarID into avatars
    from da1 in avatars.DefaultIfEmpty()
    join pc in context.PRODUCT_CATEGORY on d.DepartmentID equals pc.DepartmentID into categories
    from pc in categories.DefaultIfEmpty()
    where d.Deleted == false
    orderby d.Name
    select new model_departments_index
    {
        DepartmentID = d.DepartmentID,
        Enabled = d.Enabled,
        Deleted = d.Deleted,
        Name = d.Name,
        Description = d.Description,
        Featured = d.Featured,
        Rollback = d.Rollback,
        Avatar_Primary_ID = d.Avatar_Primary,
        Avatar_Primary_Image = new model_type_avatar
        {
            Name = da1.Name,
            Alt = da1.Alt,
            Data = da1.Data,
            Type = da1.Type,
            Url = da1.Url
        },
        Categories =
        (
            from c in categories
            orderby c.Name
            select new json_product_categories
            {
                text = c.Name,
                value = c.CategoryID
            }
        ).ToList<json_product_categories>()
    }
).ToList();
pValue = pResults.Count();