title: SQL dynamic query check for null, or empty string parameter
body: <p>I am writing a stored procedure that will be executed from an SSRS report. It receives 2 parameters: SourceID, and ConfirmationNumber but it is not working for all the tests that I am running</p>
  
  <pre><code>this returns recs:[dbo].[GetParkingPaymentInformation] 'PARC', ''
  this does not return recs:[dbo].[GetParkingtPaymentInformation] 'PARC', NULL
  this does not return recs:[dbo].[GetParkingPaymentInformation] '', '002077770'
  this does not return recs:[dbo].[GetParkingPaymentInformation] NULL, '002077770'
  this does return recs:[dbo].[GetParkingPaymentInformation] 'PARC', '002077770'
  </code></pre>
  
  <p>I want the sp to work when one or the other parameter are either null, or blank.</p>
  
  <p>This is what I have so far for code:</p>
  
  <pre><code>SET @s_SQL = 'SELECT d.ID, d.TransactionNumber, h.SourceType, ' + @s_ColumnName + ', d.FirstName, d.LastName,' +    
                   'LTRIM(RTRIM(d.FirstName)) + '' '' + LTRIM(RTRIM(d.LastName)) [Name], '+ 
                   'd.PaymentAmount,  CONVERT(VARCHAR(10), CAST(d.InitiationDate AS DATE), 101) [InitiationDate]' +
                   ', d.Fee, d.TotalAmount,   d.PaymentStatus, d.PaymentType, d.CreditCardType, ' +
                   'CONVERT(VARCHAR(10), CAST(d.PaymentEffectiveDate AS DATE), 101) [PaymentEffectiveDate]' + 
                   ', CONVERT(VARCHAR(10), CAST(d.ProcessDate AS DATE), 101) [ProcessDate], CONVERT(VARCHAR(10), CAST(d.CreatedDate AS DATE), 101) [CreatedDate],' +
                   'd.CashCode, d.TransConfirmID' +
                   ', d.Phone, d.StreetAddress1, d.StreetAddress2, ' +
                   'LTRIM(RTRIM(d.StreetAddress1)) + '' '' + CASE WHEN LEN(d.StreetAddress2) &gt; 0 THEN LTRIM(RTRIM(d.StreetAddress2)) ELSE '''' END [Address]' +
                   ', d.City, d.[State],  d.ZipFive,  d.ZipFour,  d.Email ' +
                   'FROM '+ @s_TableHeader + ' h WITH (NOLOCK) ' + 
                   'INNER JOIN ' + @s_TableDetail + ' d WITH (NOLOCK) ' +
                   'ON h.ID = d.headerID ' +
                   'WHERE' +
                   '  ((h.sourcetype = ' + '''' + @s_Source_Type + '''' + ') OR ' + '''' + @s_Source_Type + '''' + ' IS NULL OR ' + '''' + @s_Source_Type + '''' + '= '''')' +
                   '  AND ((d.transconfirmid = ' + '''' + @s_Confirmation_Number + '''' + ') OR ' + '''' + @s_Confirmation_Number + '''' + ' IS NULL OR ' + '''' + @s_Confirmation_Number + '''' + '= '''')'
  </code></pre>
  
  <p>Any help I can get to figure why my checks are not working, it would be great. </p>