Multi tool use

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.

varchar

CHAR

INT

DECIMAL

3 Answers
3

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.

char()

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

between

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

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'.

periodid

fiscalyr

char

char

periodid

fiscalyr

periodid = '1'

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

WHERE LTRIM( RTRIM( periodid ) ) = LTRIM( RTRIM( @P3 ) )

Trim()

WHERE

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.