今天在写拼写SQL脚本时候遇到这个问题,一开始还是没有注意,什么也没有改就好了。
但是好景不长,当我这个脚本都做好了,很画面联调的时候有出现这个问题了。这没办法了,
就的好好调查一下了!
拼写的脚本摘录一部分在这里:
IF ISNULL(@RQStart,'') <> '' AND @RQStart <> '' BEGIN SET @sqlTextTwo = ' And RQ >= ''' + CONVERT(VARCHAR(22), @RQStart, 120) + ''''; END; IF ISNULL(@RQEnd,'') <> '' AND @RQEnd <> '' BEGIN SET @sqlTextTwo = @sqlTextTwo + ' And RQ <= ''' + CONVERT(VARCHAR(22), @RQEnd, 120) + ''''; END; IF ISNULL(@HYDM,'') <> '' AND @HYDM <> '' BEGIN SET @sqlTextTwo = @sqlTextTwo + ' And HYDM like ''%' + @HYDM +'%'''; END; IF ISNULL(@HYMC,'') <> '' AND @HYMC <> '' BEGIN SET @sqlTextTwo = @sqlTextTwo + ' And HYMC like ''%' + @HYMC +'%'''; END;
后来对这个存储过程进行了调试,发现,如果我把所有的参数都传上,不传空值,就没有这
个问题。原因是在拼接的时候都是SET @sqlTextTwo = @sqlTextTwo + 一个段条件。
当第一次拼接时@sqlTextTwo是为空值,空值不能直接拼接一点字符串,需要先做转换成
字符串才可以进行拼接处理。
修改如下就可以:
IF ISNULL(@RQStart,'') <> '' AND @RQStart <> '' BEGIN SET @sqlTextTwo = ' And RQ >= ''' + CONVERT(VARCHAR(22), @RQStart, 120) + ''''; END; IF ISNULL(@RQEnd,'') <> '' AND @RQEnd <> '' BEGIN SET @sqlTextTwo = ISNULL(@sqlTextTwo, '') + ' And RQ <= ''' + CONVERT(VARCHAR(22), @RQEnd, 120) + ''''; END; IF ISNULL(@HYDM,'') <> '' AND @HYDM <> '' BEGIN SET @sqlTextTwo = ISNULL(@sqlTextTwo, '') + ' And HYDM like ''%' + @HYDM +'%'''; END; IF ISNULL(@HYMC,'') <> '' AND @HYMC <> '' BEGIN SET @sqlTextTwo = ISNULL(@sqlTextTwo, '') + ' And HYMC like ''%' + @HYMC +'%'''; END;