Input Parameter in SQL string where clause 1

[bustercoder]

Hello, This is an excerpt from my stored proc, which I use to build a SQL string with and execute. I have an input parameter, @checkNumber varchar(21) = NULL, and I'm trying to get it into this SQL statement like I have with others, but because I'm building a SQL string it doesn't like the syntax and so doesn't work. Had trouble finding info on this, so if someone could please show me the correct way to do this I'd sure appreciate it:

SET @SQL = '
(SELECT * FROM
(SELECT CUSTNMBR, DOCNUMBR, CHEKNMBR, ORTRXAMT, CURTRXAM, RMDTYPAL, DOCDATE
FROM RM20101
UNION
SELECT CUSTNMBR, DOCNUMBR, CHEKNMBR, ORTRXAMT, CURTRXAM, RMDTYPAL, DOCDATE
FROM RM30101
) SubQ1
WHERE CUSTNMBR = (''' + @customerNumber + ''') AND ''@checkNumber'' IS NULL OR CHEKNMBR = (''' + @checkNumber + ''')
) RMInvoices
ON RMInvoices.CUSTNMBR = RM00101.CUSTNMBR
PRINT(@SQL)
Exec(@SQL)

Here is the output from PRINT(@SQL):
(SELECT * FROM
(SELECT CUSTNMBR, DOCNUMBR, CHEKNMBR, ORTRXAMT, CURTRXAM, RMDTYPAL, DOCDATE
FROM RM20101
UNION
SELECT CUSTNMBR, DOCNUMBR, CHEKNMBR, ORTRXAMT, CURTRXAM, RMDTYPAL, DOCDATE
FROM RM30101
) SubQ1
WHERE CUSTNMBR = ('COUR004') AND '@checkNumber' IS NULL OR CHEKNMBR = ('4814')
) RMInvoices
ON RMInvoices.CUSTNMBR = RM00101.CUSTNMBR

The problem is this line:
WHERE CUSTNMBR = ('COUR004') AND '@checkNumber' IS NULL OR CHEKNMBR = ('4814')

But without the single quotes around @checkNumber I get:
"Must declare the variable '@checkNumber'."

Is there any way around this? I need to be able to return ALL records with and without (null) check numbers. Hope this makes sense.

Thanks,
Buster

 

[gmmastros]

try this...

SET @SQL = '
(SELECT * FROM
     (SELECT CUSTNMBR, DOCNUMBR, CHEKNMBR, ORTRXAMT, CURTRXAM, RMDTYPAL, DOCDATE
      FROM RM20101
     UNION
      SELECT CUSTNMBR, DOCNUMBR, CHEKNMBR, ORTRXAMT, CURTRXAM, RMDTYPAL, DOCDATE
      FROM RM30101 
     ) SubQ1
  WHERE CUSTNMBR = (''' + @customerNumber + ''') ' + Case when @checkNumber IS Not NULL 
          Then 'CHEKNMBR = (''' + @checkNumber + ''') '
          Else ''
          End + '
    ) RMInvoices
    ON RMInvoices.CUSTNMBR = RM00101.CUSTNMBR '
PRINT(@SQL)
Exec(@SQL)

Notice that if @checkNumber is NULL, then nothing is added to the where clause, but if @checkNumber is not null, then it is added to the where clause. I suspect this is what you are actually trying to do, but can't be sure.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bustercoder]

THANK YOU GEORGE!!