IF EXISTS
  (SELECT *
     FROM SYS.[objects] SO
    WHERE SO.[name] = 'KAAS_FN_CheckCodeExists'
      AND SO.[type] = 'FN')
BEGIN
  DROP FUNCTION [dbo].[KAAS_FN_CheckCodeExists]  
END
GO


CREATE FUNCTION
       [dbo].[KAAS_FN_CheckCodeExists]
      (
	  @Mode varchar(10),
	  @TableName varchar(50),
	  @CodeVal varchar(10),
	  @NewCodeVal varchar(20)
	
	  )
RETURNS VARCHAR(MAX)
AS
/********************************************************************************
 *																				*
 * Returns selected line number from multi-line Address							*
 * [dbo].[KAAS_FN_CheckCodeExists] 'INSERT','UndertakingStatus', '','PENDING'	*
 * Stored Procedure : KAAS_FN_CheckCodeExists									*
 * Maintenance History															*
 * 2021-06-15  Natarajan S             Created									*
 *																				*
 *******************************************************************************/

BEGIN

DECLARE   @Result varchar(10)

DECLARE @Qry Nvarchar(max)

DECLARE @ColCode varchar(20)
IF(@TableName='UndertakingStatus')
BEGIN
 SET @ColCode = 'StatusDesc'
 IF(@Mode = 'INSERT')
	SET @Qry = 'select '+@ColCode+' FROM ' +@TableName +' WHERE ' +@ColCode+'=''' +@NewCodeVal +''''
 ELSE
	SET @Qry = 'select '+@ColCode+' FROM ' +@TableName +' WHERE ' +@ColCode+'=''' +@NewCodeVal +''''+ ' AND RecordId <>''' +@CodeVal +''''
END
ELSE
BEGIN 
IF(@TableName='CategoryType')
	BEGIN
		SET @ColCode = 'CategoryType'
	END 
ELSE
	BEGIN
		SET @ColCode = (	SELECT 
							C.COLUMN_NAME 
					FROM  
						INFORMATION_SCHEMA.TABLE_CONSTRAINTS T  
					JOIN 
						INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE C  
					  ON 
						C.CONSTRAINT_NAME=T.CONSTRAINT_NAME  
					WHERE  
						C.TABLE_NAME=@TableName  
					  AND
						T.CONSTRAINT_TYPE='PRIMARY KEY'
				)
	END


				IF(@Mode = 'INSERT')
					BEGIN
						SET @Qry = 'select '+@ColCode+' FROM ' +@TableName +' WHERE ' +@ColCode+'=''' +@CodeVal +''''
					END
				ELSE
					BEGIN
						SET @Qry = 'select '+@ColCode+' FROM ' +@TableName +' WHERE ' +@ColCode+'=''' +@NewCodeVal +''''+ 'AND '+ @ColCode +'<>''' +@CodeVal +''''
					END
END








  exec sp_executesql  @Qry

  return @result 

 end

GO