October 4, 2013 at 3:30 am
Hi,
I'm developing a stored procedure (the proc below).
When I try to print the @str2 it always returns '' but I pass a value to the @ano variable.
Can someone help?
Thanks
ALTER PROCEDURE sp_RetornaCTBDesc (@ANO VARCHAR(5) = NULL,
@MES VARCHAR (2) = NULL,
@DIA VARCHAR (10) = NULL,
@status VARCHAR(2) = NULL,
@PROVINCIA VARCHAR(3) = NULL,
@SERVICOFISCAL VARCHAR(5) = NULL,
@METODOT VARCHAR(6) = NULL,
@OPERACAO VARCHAR (5) = NULL)
AS
BEGIN
DECLARE @DB AS VARCHAR(50)
DECLARE @STR1 AS VARCHAR(1000)
DECLARE @STR2 AS VARCHAR(1000)
DECLARE @STR3 AS VARCHAR(1000)
DECLARE @STR4 AS VARCHAR(1000)
DECLARE @STR5 AS VARCHAR(1000)
DECLARE @OPT AS VARCHAR (20)
SET @DB = (SELECT CONFIG_VALUE from TBL_BASE_CONFIG WHERE CONFIG_PARAM ='SGCT_BD')
IF @OPERACAO = 'INSERIDOS'
SET @OPT ='DT_INICIO'
ELSE
BEGIN
IF @OPERACAO = 'INSERIDOS'
SET @OPT ='DT_ALTERACAO'
ELSE
SET @OPT ='CESSADOS'
END
SET @STR1 = ('SELECT
COD_PROVINCIA,
COD_MET_TRIBUTARIO,
NIF+ISNULL(FILIAL_NUMBER,'''') NIF,
NOME,
STATUS
FROM
'+@DB+'.DBO.CONTRIBUINTES ')
--PRINT @STR1
SET @STR2 =('SELECT
CASE
WHEN ('+@ANO+'=NULL AND '+@MES+'=NULL AND '+@DIA+'=NULL)
THEN ''UI''
WHEN '+@DIA+' <> NULL
THEN ''WHERE '''+@OPERACAO+ '''= ''' + @DIA +'''''
WHEN '+@MES+' <> NULL
THEN ''WHERE MONTH('+@OPERACAO+ ')' + '=' + @MES + ' AND '+' YEAR('+@OPERACAO+')'+' = ' +@ANO+'''
ELSE ''WHERE YEAR('+@OPERACAO+ ')'+' = ' + @ANO +'''
END')
PRINT @STR2
October 4, 2013 at 4:01 am
i ALSO TRIED TO CHANGE BY DOING THIS:
IF (@ANO IS NULL AND @MES IS NULL AND @DIA IS NULL)
BEGIN
SET @STR2 = 'boi'
END
IF (@DIA IS NOT NULL)
BEGIN
SET @STR2 = 'WHERE '+@OPERACAO+ '= ' + @DIA +''
END
IF (@ANO <> IS NULL AND @MES IS NULL)
BEGIN
SET @STR2 = 'WHERE YEAR('+@OPERACAO+ ')'+' = ' + @ANO +''
END
IF (@ANO IS NOT NULL AND @MES IS NOT NULL)
BEGIN
SET @STR2 = 'WHERE MONTH('+@OPERACAO+ ')' + '=' + @MES + ' AND '+' YEAR('+@OPERACAO+')'+' = ' +@ANO+''
END
SELECT @STR2
BUT I HAVE THE SAME PROBLEM. THE SELECT ALWAYS RETURNS ''
October 4, 2013 at 4:09 am
Is it always showing a blank result or NULL?
This is important, because if you concatenate strings together and one of them contains NULL then the result will always be NULL (unless you've played with the default CONCAT_NULL_YIELDS_NULL setting):
DECLARE @String1 VARCHAR(100);
DECLARE @String2 VARCHAR(100);
SET @string1 = 'Show this ';
SET @string2 = 'string';
SELECT @String1 + @String2;
SET @String2 = NULL;
SELECT @String1 + @String2;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply