dbo.GetUserTyeByAccountNumber.StoredProcedure.sql
4.31 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
USE [AIADatabaseV5]
GO
/****** Object: StoredProcedure [dbo].[GetUserTyeByAccountNumber] Script Date: 2/1/2018 12:15:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Magic Software
-- Create date: 29-Apr-2009
-- Description: To get the list of user type on the basis of account number and type of logged in user
-- =============================================
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetUserTyeByAccountNumber]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetUserTyeByAccountNumber]
GO
CREATE PROCEDURE [dbo].[GetUserTyeByAccountNumber]
-- Add the parameters for the stored procedure here
@iUserTypeId tinyint, @iLicenseId int
AS
BEGIN
-- returns the metadata
IF 1=0 BEGIN
SET FMTONLY OFF
END
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @sUserType varchar(50)
DECLARE @sLicenseType varchar(50)
-- create a temporary table to store the usertype according to the role and accountnumber
CREATE TABLE #UserTypeToAccountNumber
(
Id tinyint,
Title varchar(50)
)
--SELECT @sUserType = Title FROM UserType WHERE Id = @iUserTypeId
IF @iLicenseId = 0
BEGIN
IF @iUserTypeId = 1
BEGIN
INSERT INTO #UserTypeToAccountNumber SELECT Id, Title FROM UserType WHERE Title = 'General Admin' AND IsActive = 1
END
END
ELSE
BEGIN
SELECT @sLicenseType = LicenseType.Title FROM License INNER JOIN LicenseType ON LicenseType.Id = License.LicenseTypeId
WHERE License.Id = @iLicenseId
IF @sLicenseType = 'Site License'
BEGIN
INSERT INTO #UserTypeToAccountNumber SELECT Id, Title FROM UserType WHERE Title IN ('Client Admin', 'District Admin')
END
ELSE IF @sLicenseType = 'Concurrent License'
BEGIN
INSERT INTO #UserTypeToAccountNumber SELECT Id, Title FROM UserType WHERE Title IN ('Client Admin', 'Concurrent User') ORDER BY Priority ASC
END
END
SELECT Id,Title FROM #UserTypeToAccountNumber
-- Dropping the temporary table
DROP TABLE #UserTypeToAccountNumber
END
GO