在存储过程中遇到一下的雷同语句,想写成函数调用
其中 @BasicUnit_1 为字段的值,BasicUnit_1为对应的字段名称
也就是说函数一个参数是字段名,一个参数是该字段的值if (@BasicUnit_1 != 0) and (@BasicUnit_1 != null)
begin
update ProductPrice set BasicUnit_1 = cast(FactoryPrice * @BasicUnit_1 as decimal(18,2)) where ProductID in
(select ProductID from V_Products where @strWhere)
end if (@BasicUnit_2 != 0) and (@BasicUnit_2 != null)
begin
update ProductPrice set BasicUnit_2 = cast(FactoryPrice * @BasicUnit_2 as decimal(18,2)) where ProductID in
(select ProductID from V_Products where @strWhere)
end
其中 @BasicUnit_1 为字段的值,BasicUnit_1为对应的字段名称
也就是说函数一个参数是字段名,一个参数是该字段的值if (@BasicUnit_1 != 0) and (@BasicUnit_1 != null)
begin
update ProductPrice set BasicUnit_1 = cast(FactoryPrice * @BasicUnit_1 as decimal(18,2)) where ProductID in
(select ProductID from V_Products where @strWhere)
end if (@BasicUnit_2 != 0) and (@BasicUnit_2 != null)
begin
update ProductPrice set BasicUnit_2 = cast(FactoryPrice * @BasicUnit_2 as decimal(18,2)) where ProductID in
(select ProductID from V_Products where @strWhere)
end
解决方案 »
- 未能加载文件或程序集"Microsoft.SqlServer.Management.Sdk.Sfc, Version=10.0.0.0, Culture=nat
- 一个简单的存储过程:如何输出多个查询得到的值
- 急急急!!高分求数据库实体关系图,特别是表间关系,马上给分!
- 请问:如何对Group by 出来的数据进行 Inner Join
- 请问like语句这样写行不行啊!!!!!帮忙
- 这种SQL语句这么写?要执行高效!
- sql中全文索引contains(A,B)函数A是索引字段,B必须是条件字符么,B可以是另一字段么?
- SQL Server 2000行列问题
- SQL交叉报表难题,高手来,在线等,请回复!!
- 急问:这个查询条件怎么写
- 求1个COPY数据的最快方法.
- 关于存储过程的一个难点
百度exec executesql
CREATE PROC Test
@ParaName VARCHAR(20),
@ParaValue VARCHAR(20),
@strWhere VARCHAR(100)
AS
BEGIN
DECLARE @Sql VARCHAR(1000)
IF ISNULL(@ParaValue,'0') !='0'
BEGIN
SET @Sql='UPDATE ProductPrice SET '+@ParaName+' = CAST(FactoryPrice * '+@ParaValue+' AS DECIMAL(18,2)) '+
'WHERE ProductID IN(SELECT ProductID FROM V_Products WHERE '+@strWhere+')'
EXEC(@Sql)
END
END
begin
exec('update ProductPrice set BasicUnit_1 = cast(FactoryPrice *'+ ltrim(@BasicUnit_1)+' as decimal(18,2)) where ProductID in
(select ProductID from V_Products where '+@strWhere+'='+LTRIM(@BasicUnit_1)+')'
end if (@BasicUnit_2 != 0) and (@BasicUnit_2 != null)
begin
exec('update ProductPrice set BasicUnit_1 = cast(FactoryPrice *'+ ltrim(@BasicUnit_2)+' as decimal(18,2)) where ProductID in
(select ProductID from V_Products where '+@strWhere+'='+LTRIM(@BasicUnit_2)+')'
end这种情况要动态的哦