why is script not working after setting and declaring parameters?

Question

When I run the following code without setting any parameters I get the desired output

    SELECT * FROM (
    SELECT [CaseNumber]
          ,[EventCode]
          ,[EventDate]
          ,[Assigned]
          ,ROW_NUMBER() OVER (
                                    PARTITION BY CaseNumber
                                    ORDER BY CaseNumber,EventDate DESC,EventCode  
                            ) AS [ROW NUMBER] 
      FROM  [databasename].[dbo].[tblCsCaseEvents] 
      ) groups

    WHERE 1=1 
    AND (groups.[ROW NUMBER]=1) AND (Assigned IN ('PARKERE')) 
    AND  EventCode LIKE 'ba%'

However, when I declare and set variables as shown below and run it. The script doesn't seem to work. Why is that the case?

Declare @attorney CHAR (10)
Declare @event CHAR (10)

set @attorney='PARKERE'
set @event='ba%'

SELECT * FROM (
SELECT [CaseNumber]
      ,[EventCode]
      ,[EventDate]
      ,[Assigned]
      ,ROW_NUMBER() OVER (
                                PARTITION BY CaseNumber
                                ORDER BY CaseNumber,EventDate DESC,EventCode  
                        ) AS [ROW NUMBER] 
  FROM  [databasename].[dbo].[tblCsCaseEvents] 
  ) groups

WHERE 1=1 
AND (groups.[ROW NUMBER]=1) AND (Assigned IN (@attorney)) 
AND  EventCode LIKE @event

What error are you getting?

alexherm
– alexherm

2019-08-22 15:09:19 +00:00
Commented Aug 22, 2019 at 15:09 — alexherm
– alexherm, Commented Aug 22, 2019 at 15:09

Gordon Linoff · Accepted Answer · 2019-08-22 15:08:02Z

3

char(<n>) defines a string of exactly a length <n>. If the string is too short, it is padded with spaces.

This affects some comparisons, such as like. Instead, use varchar().

Here is an explanation from Microsoft.

answered Aug 22, 2019 at 15:08

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

why is script not working after setting and declaring parameters?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related