USE [AIADatabaseV5] GO /****** Object: StoredProcedure [dbo].[GetLicenseByIPAndAccount] Script Date: 2/1/2018 12:15:55 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: -- Description: -- ============================================= if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetLicenseByIPAndAccount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[GetLicenseByIPAndAccount] GO CREATE PROCEDURE [dbo].[GetLicenseByIPAndAccount] -- Add the parameters for the stored procedure here @sSiteIP VARCHAR(100), @sAccountNumber VARCHAR(16), @iEditionId TINYINT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SELECT License.AccountNumber, License.AccountTypeId, License.Address1, License.Address2, License.City, License.CountryId, License.CreationDate, License.EmailId, License.Id, License.InstitutionName, License.IsActive, License.IsDistrictSiteLicense, License.IsTermsAccepted, License.LicenseTypeId, License.LicenseeFirstName, License.LicenseeLastName, License.ModifiedDate, License.NoOfRenewals, License.Phone, License.StateId, License.TotalLogins, License.Zip, SiteToLicenseEdition.IsModesty FROM License INNER JOIN LicenseToEdition ON License.Id = LicenseToEdition.LicenseId INNER JOIN SiteToLicenseEdition ON LicenseToEdition.Id = SiteToLicenseEdition.LicenseEditionId INNER JOIN Site ON SiteToLicenseEdition.SiteId = Site.Id WHERE (SUBSTRING(REPLACE(REPLACE(REPLACE(Site.SiteIP+'/','www.',''),'http://',''),'https://',''),0, CHARINDEX('/',REPLACE(REPLACE(REPLACE(Site.SiteIP+'/','www.',''),'http://',''),'https://',''))) = @sSiteIP) AND (License.AccountNumber = @sAccountNumber) AND (LicenseToEdition.EditionId = @iEditionId) END GO