ADAM / AIAHTML5

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

CREATE PROCEDURE [dbo].[usp_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