text: ﻿
  /*******************************************************************
  DECLARE 
       @output INT = 0
  	,@fieldName				nvarchar(255)
  EXEC [dbo].[Entity_Lookup]		
  	@companyGuid		= '12A5CD86-F6C6-455F-B27A-EFE587ED410D'	
  	,@invokingUser  	= '7D31E738-5E24-4EA2-AAEF-47BB0F3CCD41'
  	,@version			= 'v1'
  	,@output			= @output		OUTPUT
  	,@fieldName			= @fieldName	OUTPUT	
                 
   SELECT @output status,  @fieldName AS fieldName    
   
   001	sgh-1 28-11-2019 [Nishit Khakhi]	Added Initial Version to Lookup Entity
  *******************************************************************/
  
  CREATE PROCEDURE [dbo].[Entity_Lookup]
  (	 
  	 @companyGuid		UNIQUEIDENTIFIER
  	,@invokingUser		UNIQUEIDENTIFIER
  	,@version			NVARCHAR(10)
  	,@output			SMALLINT		  OUTPUT
  	,@fieldName			NVARCHAR(255)	  OUTPUT	
  	,@culture			NVARCHAR(10)	  = 'en-Us'
  	,@enableDebugInfo	CHAR(1)			  = '0'
  )
  AS
  BEGIN
      SET NOCOUNT ON
  	
  	IF (@enableDebugInfo = 1)
  	BEGIN
          DECLARE @Param XML
          SELECT @Param =
          (
              SELECT 'Entity_Lookup' AS '@procName'			
  			, CONVERT(nvarchar(MAX),@companyGuid) AS '@companyGuid'
              , CONVERT(nvarchar(MAX),@invokingUser) AS '@invokingUser'
  			, CONVERT(nvarchar(MAX),@version) AS '@version'
  			, CONVERT(nvarchar(MAX),@output) AS '@output'
              , CONVERT(nvarchar(MAX),@fieldName) AS '@fieldName'
              FOR XML PATH('Params')
  	    )
  	    INSERT INTO DebugInfo(data, dt) VALUES(Convert(nvarchar(MAX), @Param), GETUTCDATE())
      END
      Set @output = 1
      SET @fieldName = 'Success'
  
      IF NOT EXISTS (SELECT TOP 1 1 FROM [Entity] (NOLOCK) WHERE companyGuid=@companyGuid AND [isDeleted]=0)
  	BEGIN
  		Set @output = -3
  		SET @fieldName = 'EntityNotFound'
  		RETURN;
  	END
    
      BEGIN TRY
  		SELECT [guid]
  			  ,[name]
  		FROM [Entity] (NOLOCK) WHERE [companyguid]=@companyGuid AND [isDeleted] = 0 
  		and [Guid] not in (select entityGuid from [dbo].[Company] where [Guid]=@companyGuid)
  		ORDER BY [name]
  
  	END TRY
  	BEGIN CATCH
  		DECLARE @errorReturnMessage nvarchar(MAX)
  
  		SET @output = 0
  
  		SELECT @errorReturnMessage =
  			ISNULL(@errorReturnMessage, '') +  SPACE(1)   +
  			'ErrorNumber:'  + ISNULL(CAST(ERROR_NUMBER() as nvarchar), '')  +
  			'ErrorSeverity:'  + ISNULL(CAST(ERROR_SEVERITY() as nvarchar), '') +
  			'ErrorState:'  + ISNULL(CAST(ERROR_STATE() as nvarchar), '') +
  			'ErrorLine:'  + ISNULL(CAST(ERROR_LINE () as nvarchar), '') +
  			'ErrorProcedure:'  + ISNULL(CAST(ERROR_PROCEDURE() as nvarchar), '') +
  			'ErrorMessage:'  + ISNULL(CAST(ERROR_MESSAGE() as nvarchar(max)), '')
  		RAISERROR (@errorReturnMessage, 11, 1)
  
  		IF (XACT_STATE()) = -1
  		BEGIN
  			ROLLBACK TRANSACTION
  		END
  		IF (XACT_STATE()) = 1
  		BEGIN
  			ROLLBACK TRANSACTION
  		END
  		RAISERROR (@errorReturnMessage, 11, 1)
  	END CATCH
  END