CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
sirius_pain
sirius_pain

Reputation: 34

Why is my T-SQL stored procedure not executing?

My table structure is as follows:

GLPERIOD:

 periodid     char
 strtdate     datetime
 closdate     datetime
 fiscalyr     char

GLPOST:

 postdate     datetime
 account      char
 amount       money

With this in mind, the following stored procedure, which has parameters defined to be of identical type to what the table columns require, returns nothing when called.

PROCEDURE MonthlyActual 
    @P2 CHAR, @P3 CHAR, @P4 CHAR
    SELECT SUM(amount) 
    FROM GLPOST 
    WHERE account = @P2
      AND postdate BETWEEN (SELECT strtdate 
                            FROM GLPERIOD 
                            WHERE periodid = @P3 AND fiscalyr = @P4) AND 
                           (SELECT closedate 
                            FROM GLPERIOD 
                            WHERE periodid = @P3 AND fiscalyr = @P4)
    GROUP BY   
        account

I try calling it like this: 

MonthlyActual @P2 = '51080-000', @P3 = 1, @P4 = 2018

MonthlyActual @P2 = '51080-000', @P3 = '1', @P4 = '2018'

...and get nothing. Yet if I hard-code these values, the code runs perfectly. What am I doing wrong?

I am using SQL Server Management Studio on Windows Server.

Upvotes: 0

Views: 1101

Answers (3)

sirius_pain
sirius_pain

Reputation: 34

The problem was that though periodid and fiscalyr are of char data type, passing them char values gave no results - because they were not trimmed. Though periodid and fiscalyr would only ever hold integers, the fact that the database was designed some 20 years ago also meant mediocre habits were in play. Specifically, the equality of periodid = '1' never went through because it was stored as ' 1'.

Setting the checks to WHERE LTRIM( RTRIM( periodid ) ) = LTRIM( RTRIM( @P3 ) ) resolved the issue.

Upvotes: 1

Dyna Dave
Dyna Dave

Reputation: 149

First move your two dates for the between into separate statements:

Declare @StartDate as datetime
Declare @EndDate as datetime

Set @StartDate = SELECT strtdate FROM GLPERIOD WHERE periodid = @P3 AND fiscalyr = @P4
Set @EndDate = SELECT closdate FROM GLPERIOD WHERE periodid = @P3 AND fiscalyr = @P4

SELECT 
     SUM(amount) 
FROM 
     GLPOST 
WHERE 
     account = @P2 
     AND postdate BETWEEN @StartDate AND @EndDate 
GROUP BY 
     account

Next comment out the between statement and see if it returns data. If so then you know the problem is with the date data

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1271003

I am going to guess that your stored procedure looks like this:

create stored procedure MonthlyActual (
    @p2 char,
    @p3 char,
    @p4 char
)

If so, the problem is simple. The declarations are for a single character. Change them to more reasonable types:

create stored procedure MonthlyActual (
    @account varchar(255),
    @periodid varchar(255),
    @year varchar(255)
)

I also recommend renaming the parameters so the code makes more sense.

Moral? In SQL Server always use lengths with char() and related types. The default length varies by context and often introduces hard-to-debug errors.

Upvotes: 4

Related Questions