USE [AIADatabaseV5] GO /****** Object: StoredProcedure [dbo].[GetUserInGroup] Script Date: 2/1/2018 12:15:55 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Magic Software -- Create date: 18-May-2009 -- Description: To get the list of all user of a account with the associated group -- ============================================= if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetUserInGroup]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[GetUserInGroup] GO CREATE PROCEDURE [dbo].[GetUserInGroup] -- Add the parameters for the stored procedure here @iLicenseId int, @iGroupId int AS BEGIN -- returns the metadata IF 1=0 BEGIN SET FMTONLY OFF END SELECT AIAUser.Id, AIAUser.FirstName, AIAUser.LastName, AIAUser.LoginId, AIAUser.EmailId, Edition.Title, (CASE WHEN UserGroupToAIAUser.UserId IS NULL THEN 0 ELSE 1 END) AS InGroup FROM AIAUser INNER JOIN AIAUserToLicenseEdition ON AIAUser.Id = AIAUserToLicenseEdition.UserId INNER JOIN LicenseToEdition ON AIAUserToLicenseEdition.LicenseEditionId = LicenseToEdition.Id INNER JOIN Edition ON Edition.Id = LicenseToEdition.EditionId INNER JOIN License ON LicenseToEdition.LicenseId = License.Id AND License.IsActive = 1 INNER JOIN UserGroupToAIAUser ON UserGroupToAIAUser.UserId = AIAUser.Id AND UserGroupToAIAUser.UserGroupId = @iGroupId WHERE License.Id = @iLicenseId AND AIAUser.UserTypeId = 6 END GO