The stored procedure accepts a bunch of parameters, but for clarity, I will simplify it to include the ones relevant to the problem: There is a single entry for Branch,department, and subaccount. However, there is a startaccount parameter, and a Endaccount parameter, where I will have to check for permissions for branch , department and subaccounts and also every account in the range of accounts supplied, in order to determine if the user has access to all of the accounts in the range. If yes, then run the query, if not, then return an empty set. There is a list of accounts with other data in an Accounts table.
UserPermissions Table UserName Branch Department Account SubAccount John 1 A ALL ALL John 2 ALL 12345 ALL John 2 ALL 36524 25 Alex ALL ALL ALL ALL
My approach was: trying to check if the user has the permission to run the query as below. And then if count(*) is greater than zero then run the query. But I am having a problem comparing the range of accounts….New to Sql,and STUCK here…and don’t know if what I am doing is right!!!
ALTER PROCEDURE [dbo].[MyProc] ( @Branch varchar(4) = null, @Department varchar(4) = null, @SubAccount varchar(8) = null, @Start_Account varchar(6) = Null, @End_Account varchar(6) = Null, @UserName ) As BEGIN
SELECT COUNT(*) From UserPermissions WHERE (Branch = 'All' or Branch = @Branch) AND (Department = 'All' or Department = @Department) AND (SubAccount = 'All' or SubAccount = @SubAccount) AND (U.Account = 'All') And username = @username --This is the final query SELECT Account, SubAccount, Branch, Department, Amount, FROM Table1 Where (Branch = ISNULL(@Branch,Branch)) AND (Department = ISNULL(@Department,Department)) AND (SubAccount = ISNULL(@SubAccount, SubAccount)) AND (Account BETWEEN ISNULL(@Start_Account,Account) AND ISNULL(@End_Account,Account)) END GO