原存储过程ALTER PROCEDURE [dbo].[GetProductList]
@GroupID int
as
if not exists (SELECT PKID FROM TPRODUCTGROUP WHERE PARENTID =@GROUPID)
begin
SELECT PKID,ProductCode,BarCode,'upload/'+left(BarCode,7)+'_s_1.jpg' as ListImg,FullName,SalePrice,MarketPrice,substring(ProductCode,1,charindex('-',ProductCode)-1) as Number FROM TProduct Where GroupID = @GroupID And Valid ='T'
end
else
begin
SELECT PKID,ProductCode,BarCode,'upload/'+left(BarCode,7)+'_s_1.jpg' as ListImg,FullName,SalePrice,MarketPrice,substring(ProductCode,1,charindex('-',ProductCode)-1) as Number FROM TProduct Where GroupID IN (SELECT PKID FROM TPRODUCTGROUP WHERE PARENTID =@GROUPID) And Valid ='T'
END
我现在要把它改成这样的
ALTER PROCEDURE [dbo].[GetProductList]
@GroupID int
as
declare @sql nvarchar(4000)
set @sql =''
if not exists (SELECT PKID FROM TPRODUCTGROUP WHERE PARENTID =@GROUPID)
begin
set @sql='SELECT PKID,ProductCode,BarCode,''upload/''+left(BarCode,7)+''_s_1.jpg'' as ListImg,FullName,SalePrice,MarketPrice,substring(ProductCode,1,charindex(''-'',ProductCode)-1) as Number FROM TProduct Where GroupID = ' +@GroupID + ' And Valid =''T'''
end
else
begin
set @sql='SELECT PKID,ProductCode,BarCode,''upload/''+left(BarCode,7)+''_s_1.jpg'' as ListImg,FullName,SalePrice,MarketPrice,substring(ProductCode,1,charindex(''-'',ProductCode)-1) as Number FROM TProduct Where GroupID IN (SELECT PKID FROM TPRODUCTGROUP WHERE PARENTID = ' + @GROUPID + ') And Valid =''T'''
END
execute(@sql)
运行有错误啊!
在将 varchar 值 'SELECT PKID,ProductCode,BarCode,'upload/'+left(BarCode,7)+'_s_1.jpg' as ListImg,FullName,SalePrice,MarketPrice,substring(ProductCode,1,charindex('-',ProductCode)-1) as Number FROM TProduct Where GroupID = ' 转换成数据类型 int 时失败。
大家给点帮助哈!
@GroupID int
as
if not exists (SELECT PKID FROM TPRODUCTGROUP WHERE PARENTID =@GROUPID)
begin
SELECT PKID,ProductCode,BarCode,'upload/'+left(BarCode,7)+'_s_1.jpg' as ListImg,FullName,SalePrice,MarketPrice,substring(ProductCode,1,charindex('-',ProductCode)-1) as Number FROM TProduct Where GroupID = @GroupID And Valid ='T'
end
else
begin
SELECT PKID,ProductCode,BarCode,'upload/'+left(BarCode,7)+'_s_1.jpg' as ListImg,FullName,SalePrice,MarketPrice,substring(ProductCode,1,charindex('-',ProductCode)-1) as Number FROM TProduct Where GroupID IN (SELECT PKID FROM TPRODUCTGROUP WHERE PARENTID =@GROUPID) And Valid ='T'
END
我现在要把它改成这样的
ALTER PROCEDURE [dbo].[GetProductList]
@GroupID int
as
declare @sql nvarchar(4000)
set @sql =''
if not exists (SELECT PKID FROM TPRODUCTGROUP WHERE PARENTID =@GROUPID)
begin
set @sql='SELECT PKID,ProductCode,BarCode,''upload/''+left(BarCode,7)+''_s_1.jpg'' as ListImg,FullName,SalePrice,MarketPrice,substring(ProductCode,1,charindex(''-'',ProductCode)-1) as Number FROM TProduct Where GroupID = ' +@GroupID + ' And Valid =''T'''
end
else
begin
set @sql='SELECT PKID,ProductCode,BarCode,''upload/''+left(BarCode,7)+''_s_1.jpg'' as ListImg,FullName,SalePrice,MarketPrice,substring(ProductCode,1,charindex(''-'',ProductCode)-1) as Number FROM TProduct Where GroupID IN (SELECT PKID FROM TPRODUCTGROUP WHERE PARENTID = ' + @GROUPID + ') And Valid =''T'''
END
execute(@sql)
运行有错误啊!
在将 varchar 值 'SELECT PKID,ProductCode,BarCode,'upload/'+left(BarCode,7)+'_s_1.jpg' as ListImg,FullName,SalePrice,MarketPrice,substring(ProductCode,1,charindex('-',ProductCode)-1) as Number FROM TProduct Where GroupID = ' 转换成数据类型 int 时失败。
大家给点帮助哈!
--检查
print @sql
cast(@GroupID as varchar(20))
@GroupID int
as
declare @sql nvarchar(4000)
set @sql =''
if not exists (SELECT PKID FROM TPRODUCTGROUP WHERE PARENTID =@GROUPID)
begin
set @sql='SELECT PKID,ProductCode,BarCode,''upload/''+left(BarCode,7)+''_s_1.jpg'' as ListImg,FullName,SalePrice,MarketPrice,substring(ProductCode,1,charindex(''-'',ProductCode)-1) as Number FROM TProduct Where GroupID = @GroupID And Valid =''T'''
end
else
begin
set @sql='SELECT PKID,ProductCode,BarCode,''upload/''+left(BarCode,7)+''_s_1.jpg'' as ListImg,FullName,SalePrice,MarketPrice,substring(ProductCode,1,charindex(''-'',ProductCode)-1) as Number FROM TProduct Where GroupID IN (SELECT PKID FROM TPRODUCTGROUP WHERE PARENTID = @groupid) And Valid =''T'''
END
execute(@sql)@groupid 直接写在字符串里即可。
ltrim(@GroupID)
其他的是字符型的
動態中不匹配