Question

SqlServer 中存储过程：money类型与char类型的转换问题

ALTER PROCEDURE [dbo].[SearchMerchandise] @Category varchar(50), @Brand varchar(50), @Type varchar(50), @FormerPrice money, @LatterPrice money AS declare @QueryString varchar(100) set @QueryString='select * from Merchandise where Category=@Category and Brand=@Brand ' BEGIN SET NOCOUNT ON; if @Type<>'' set @QueryString=@QueryString+' and Type='+''''+@Type+''''+' and ' if @FormerPrice<>'' and @LatterPrice<>'' set @QueryString=@QueryString+'PriceNow bettwwen'+''''+@FormerPrice+''''+' and '+''''+@LatterPrice+''''+' and ' if @FormerPrice<>'' and @LatterPrice='' set @QueryString=@QueryString+'PriceNow >='+''''+@FormerPrice+''''+' and ' if @FormerPrice='' and @LatterPrice<>'' set @QueryString=@QueryString+'PriceNow <='+''''+@LatterPrice+''''+' and ' set @QueryString=@QueryString+'1=1' exec(@QueryString) END 其中有一句是if @Type<>'' set @QueryString=@QueryString+' and Type='+''''+@Type+''''+' and ' 我不明白为什么+@Type+外面要四个单引号。。。 第二个问题就是if @FormerPrice<>'' and @LatterPrice<>'' set @QueryString=@QueryString+'PriceNow bettwwen'+''''+@FormerPrice+''''+' and '+''''+@LatterPrice+''''+' and ' 这句话中还是那几个单引号的问题，貌似这几个单引号把里面的内容转换成了char类型了，但是@FormerPrice和@LatterPrice是money类型，所有执行该存储过程中出错。错误信息为：消息 235，级别 16，状态 0，过程 SearchMerchandise，第 18 行 无法将 char 值转换为 money。该 char 值的语法有误。 执行的代码为： USE [OfficialBusiness] GO DECLARE @return_value int EXEC @return_value = [dbo].[SearchMerchandise] @Category = N'笔记本类', @Brand = N'神州', @Type = NULL, @FormerPrice = 2000, @LatterPrice = 3000 SELECT 'Return Value' = @return_value GO 希望高手尽快给予答复，答案满意者定追加分。。。谢谢啦

Answer 1

自己写的么？
1有些地方没必要4个单引号，3个就行，在sql中字符串的拼接语句中要表示1个单引号就得用两个单引号，前一个表示转义的意思，编程语言都有转义一说
2因为是字符串拼接的，而@FormerPrice这些是money类型的，如果不转换，就默认要把字符串转换成money类型了，就会出错。所以得把@FormerPrice之类的转换成字符类型的，存储过程修改如下
ALTER
PROCEDURE
[dbo].[SearchMerchandise]
@Category
varchar(50),
@Brand
varchar(50),
@Type
varchar(50),
@FormerPrice
money,
@LatterPrice
money
AS
BEGIN
declare
@QueryString
varchar(100)
set
@QueryString
=
'select
*
from
Merchandise
where
Category
=
'''
+
@Category
+
'''
and
Brand
=
'''
+
@Brand
+
'''
'
SET
NOCOUNT
ON;
if
@Type<>''
set
@QueryString
=
@QueryString+'
and
Type='''
+@Type+
''''
if
@FormerPrice<>''
and
@LatterPrice<>''
set
@QueryString
=
@QueryString+'
and
PriceNow
between
'''+
convert(varchar(20),@FormerPrice)
+
'''
and
'''
+
convert(varchar(20),@LatterPrice)
+
''''
if
@FormerPrice<>''
and
@LatterPrice=''
set
@QueryString
=
@QueryString+'
and
PriceNow
>='''
+
convert(varchar(20),@FormerPrice)
+
''''
if
@FormerPrice=''
and
@LatterPrice<>''
set
@QueryString
=
@QueryString+'
and
PriceNow
<='''
+
convert(varchar(20),@LatterPrice)
+
''''
exec(@QueryString)
END
因为没有表结果不能进行测试，所以可能会出错，如果出错就把表结果发一下，进行测试