IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'dbo.CF_Json_ParsearVariable') 
                   and type = 'FN')
BEGIN
    DROP FUNCTION dbo.CF_Json_ParsearVariable
END
GO


CREATE  FUNCTION dbo.CF_Json_ParsearVariable(@pVariable VARCHAR(4000), @pValor VARCHAR(4000), @pTipoDato VARCHAR(20))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Json		VARCHAR(8000)
DECLARE @Fecha		DATETIME
DECLARE @FechaVC	VARCHAR(30)

/*--------------------------------------------22/9/2021       si le pasamos un nulo devuele el valor que corresponde ------------------------------------------------*/
IF (ISNULL(@pValor,'0') = '0' OR LTRIM(RTRIM(@pValor)) = '') AND RTRIM(LTRIM(UPPER(@pTipoDato))) IN ('BIGINT','NUMERIC','BIT','SMALLINT','DECIMAL','SMALLMONEY','INT','TINYINT','MONEY','FLOAT','REAL')
BEGIN
	SET @Json = '"' + LTRIM(RTRIM(@pVariable)) + '"' + ':' + '"0"'
	RETURN @Json
END


IF ISNULL(@pValor,'19000101') = '19000101' AND RTRIM(LTRIM(UPPER(@pTipoDato))) IN ('DATE','DATETIMEOFFSET','DATETIME2','SMALLDATETIME','DATETIME','TIME')
BEGIN
	SET @Json = '"' + LTRIM(RTRIM(@pVariable)) + '"' + ':' + '"1900-01-01T00:00:00"'
	RETURN @Json
END


IF ISNULL(@pValor,'') = ''
BEGIN
	SET @Json = '"' + LTRIM(RTRIM(@pVariable)) + '"' + ':' + '""'
	RETURN @Json
END
/*--------------------------------------------------fin de validacion de nulos -------------------------------------------------*/



IF(@pTipoDato = 'DATETIME')
BEGIN
	SET @Fecha = CONVERT(DATETIME, @pValor)
	SET @FechaVC = REPLACE(CONVERT(VARCHAR, @Fecha, 111), '/', '-') + 'T' + CONVERT(VARCHAR, @Fecha, 8)
	
	SET @Json = '"' + LTRIM(RTRIM(@pVariable)) + '"' + ':' + '"' + @FechaVC + '"'
	
	RETURN @Json
END


IF(@pTipoDato = 'DATE')
BEGIN
	SET @Fecha = CONVERT(DATETIME, @pValor)
	SET @FechaVC = REPLACE(CONVERT(VARCHAR, @Fecha, 111), '/', '-') + 'T00:00:00'
	
	SET @Json = '"' + LTRIM(RTRIM(@pVariable)) + '"' + ':' + '"' + @FechaVC + '"'
	
	RETURN @Json
END

ELSE
BEGIN
	SET @Json = '"' + LTRIM(RTRIM(@pVariable)) + '"' + ':' + '"' + @pValor + '"'
	
	RETURN @Json
END

RETURN ISNULL(@Json, '"":""')
END

/*

SELECT dbo.CF_Json_ParsearVariable('Saludo', 'Hola', 'VARCHAR')
SELECT dbo.CF_Json_ParsearVariable('Numero', '0', 'INT')
SELECT dbo.CF_Json_ParsearVariable('Fecha', '20210701', 'DATETIME')
SELECT dbo.CF_Json_ParsearVariable('Saludo', null, 'VARCHAR')
SELECT dbo.CF_Json_ParsearVariable('Numero', null, 'INT')
SELECT dbo.CF_Json_ParsearVariable('Fecha', null, 'DATETIME')


*/