CODEWITHSUNDEEP
sql-server-2008t-sqlparameters
jdids
jdids

Reputation: 571

Variable not passing correctly in TSQL Query

I'm stumped on this one. Probably an easy problem, but I can't wrap my head around this.

I have a simple query where I union the result set together so I can manipulate data based on different criteria.

I have a parameter in place so this can eventually become a SSRS report.

Here's my condensed query...

There are 3 params: StartDate, EndDate, and Supplier.

StartDate and EndDate are data type date, and Supplier is char(7)

When I run the query with the param set as '1040', I get no results

DECLARE @STARTDATE AS DATE
DECLARE @ENDDATE AS DATE
DECLARE @SUPPLIER AS CHAR

SET @STARTDATE = '2013-12-01'
SET @ENDDATE = '2013-12-31'
SET @SUPPLIER = '1040'

SELECT *
  FROM InvMovements AS AA
  JOIN InvMaster AS BB
    ON AA.StockCode = BB.StockCode
  JOIN ApSupplier AS CC
    ON CC.Supplier = BB.Supplier
 WHERE MovementType = 'I'
   AND TrnType = 'T'
   AND TrnQty < 0
   AND Warehouse = 'ZC'
   AND CC.Supplier = @SUPPLIER 
   AND EntryDate BETWEEN @STARTDATE AND @ENDDATE

Now when I change the WHERE clause to show any suppliers like the parameter (CC.Supplier LIKE '%' + @SUPPLIER + '%') , I get these results

Supplier    StockCode   Warehouse   EntryDate   TrnType    TrnQty
1000        317012      ZC          2013-12-06  T          18.000
1040        320062      ZC          2013-12-06  T           8.000

We only have 4 character suppliers.

If I manually set the supplier as CC.Supplier = '1040' I get the exact result set that I'm looking for. I've tried it in SSRS and I'm getting the same results.

Any ideas? This seems like such a dumb problem, but I can't seem to figure out why this is happening.

Upvotes: 1

Views: 59

Answers (1)

marc_s
marc_s

Reputation: 754278

If you do this:

DECLARE @SUPPLIER AS CHAR

then your variable is exactly one character long.

So when you do an assigment

SET @SUPPLIER = '1040'

you really just stored 1 in that variable.

You need to give your CHAR and VARCHAR an explicit length when you define them! You should get in the habit of doing this always - when you declare a variable, when you declare a parameter to a stored procedure or function, or when you use CHAR or VARCHAR in a CAST or CONVERT situation - define the length!

DECLARE @SUPPLIER AS CHAR(4)
SET @SUPPLIER = '1040'

then it works as expected.

Upvotes: 3

Related Questions