SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_Role_Get]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_Role_Get]
GO


--We will need to filter all by application name

create procedure [dbo].cs_Role_Get
(
	@RoleID uniqueidentifier = null,
	@RoleName nvarchar(256) = null,
	@SettingsID int
	
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN


	Declare @ApplicationName nvarchar(256)
	Select @ApplicationName = ApplicationName from cs_SiteSettings where SettingsID = @SettingsID
	
	
	select RoleId, RoleName as [Name], r.Description
	from aspnet_Roles r
		inner join aspnet_Applications a on 
			r.ApplicationId = a.ApplicationId
	where
		((@RoleID is not null and r.RoleId = @RoleID ) or @RoleID is null ) and
		((@RoleName is not null and r.RoleName = @RoleName ) or @RoleName is null ) and
		a.LoweredApplicationName = lower(@ApplicationName);


END







GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO









GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_Role_Get] to public
go