SQL 存储过程参数被损坏? [英] SQL Stored Procedure Parameters being Corrupted?

问题描述

我有一个存储过程，现在只是假设将您的字符串输入验证为有效日期.我试过在程序之外运行它并且它完美无缺，但是一旦我将它放入程序中，参数就会更改为零.

I have a stored procedure that for now is just suppose to validate your string input as a valid date. I've tried running this outside of a procedure and it works flawlessly, but as soon as I stick it in a procedure, the parameters get changed to zero.

我的程序如下:

CREATE PROCEDURE spDateRange
@DateMin varchar = NULL,
@DateMax varchar = NULL
AS
PRINT @DateMin;
PRINT @DateMax;
IF @DateMin IS NOT NULL AND @DateMax IS NOT NULL
    BEGIN
    PRINT @DateMin;
    IF(ISDATE(@DateMin)=1 AND ISDATE(@DateMax)=1)
        PRINT 'Valid Date';
    ELSE
        RAISERROR('Parameters not date format', 10, 1);
    END
ELSE
    RAISERROR ('Invalid parameters', 10, 1);

我的执行语句是:

EXECUTE spDateRange @DateMin='01/11/2011', @DateMax='01/12/2011';

我的输出是:

0
0
0
Parameters not date format

推荐答案

您需要为 varchar 参数指定一个明确的长度.默认为 1 个字符.

You need to give an explicit length for the varchar parameters. It defaults to 1 character.

char(10) 对于 dd/mm/yyyy 格式的有效日期应该足够了，但我想你可能想要更长的时间，因为这正在验证可能无效的日期！

char(10) should be enough for valid dates in dd/mm/yyyy format but I guess you might want longer as this is validating potentially invalid dates!

CREATE PROCEDURE spDateRange
@DateMin varchar(30) = NULL,
@DateMax varchar(30) = NULL
AS

这篇关于SQL 存储过程参数被损坏?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！