UC知道SQL存储过程问题,请高手帮忙看一下
来源：百度知道 编辑：UC知道 时间：2024/06/10 07:14:41
ALTER PROCEDURE [dbo].[MainSearch]
@prodbrandid int,
@prodcategoryid int
as
declare @sql nvarchar(100)
if @prodbrandid is not null
begin
set @sql = 'where prodBrandId = ' + convert(int,@prodbrandid)
end
if @prodcategoryid is not null
begin
if @sql is not null
set @sql = @sql + ' and where prodCategoryID = ' + convert(int,@prodcategoryid)
else set @sql = ' where prodCategoryID = ' + convert(int,@prodcategoryid)
end
execute('select * from dbo.product' + @sql)
return
大家帮看看问题出在哪了?
换成convert(varchar)后调试提示我'='附近有语法错误是为什么?
@prodbrandid int,
@prodcategoryid int
as
declare @sql nvarchar(100)
if @prodbrandid is not null
begin
set @sql = 'where prodBrandId = ' + convert(int,@prodbrandid)
end
if @prodcategoryid is not null
begin
if @sql is not null
set @sql = @sql + ' and where prodCategoryID = ' + convert(int,@prodcategoryid)
else set @sql = ' where prodCategoryID = ' + convert(int,@prodcategoryid)
end
execute('select * from dbo.product' + @sql)
return
大家帮看看问题出在哪了?
换成convert(varchar)后调试提示我'='附近有语法错误是为什么?
请参见我在这里的回答，谢谢！
http://zhidao.baidu.com/question/65813463.html
另，这里所用的“declare @sql nvarchar(100)”定义方式是OK的，不过长度 100 可能少了，建议改为500或1000.
~chenjin99
convert(int,@prodbrandid)
convert(int,@prodcategoryid)
都错了
你是拼接的sql，sql是字符串，你convert成int是不行的，应改成：
convert(varchar(8),@prodbrandid)
convert(varchar(8),@prodcategoryid)
set @sql = 'where prodBrandId = '+ @prodbrandid
不需要加Convert的
会自动进行隐式数据转换的
ALTER PROCEDURE [dbo].[MainSearch]
@prodbrandid int,
@prodcategoryid int
as
declare @sql nvarchar(100)
if @prodbrandid is not null
begin
set @sql = 'where prodBrandId = ' + @prodbrandid
end
if @prodcategoryid is not null
begin
if @sql is not null
set @sql = @sql + ' and where prodCategoryID = ' + @prodcategoryid
else set @sql