动态SQL中使用变量时,可使用存储过程sp_executesql
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
--region Drop Existing Procedures
IF OBJECT_ID(N'[dbo].[usp_SelectSingleValue]') IS NOT NULL
DROP PROCEDURE [dbo].[usp_SelectSingleValue]
GO
--region [dbo].[usp_SelectSingleValue]
------------------------------------------------------------------------------------------------------------------------
-- COPYRIGHT (C) 2008, SG. ALL RIGHTS RESERVED.
-- PROCEDURE NAME: [dbo].[usp_SelectSingleValue]
-- TEMPLATE :
-- AUTHOR : Theo
-- VERSION : 0.1
-- DATED CREATED : Mar 25,2008
--
-- * MODIFIED HISTORY *
--
-- 1.Mar 25,2008 by Author, Version - 0.1
-- *Created.
------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[usp_SelectSingleValue]
@OP varchar(100), --操作:比如MAX,MIN,SUM
@Field varchar(400), --字段名
@From varchar(400) = '*', -- 表名
@Where varchar(4000)='', -- 查询条件 (注意: 不要加 where)
@value int output -- 对于输出值只使用INT型,求取MAX或MIN的时候,也可能出现VARCHAR等类型,但仍然是INT型最常用
AS
declare @strSQL nvarchar(4000) -- 主语句
if @Where <>''
set @Where = ' WHERE ' + @Where
if @Field<>'*'
SET @Field = '[' + @Field + ']'
set @strSQL = 'select @value=' + @OP + '(' + @Field + ') from [' + @From + '] '+@Where
print @strSQL
exec sp_executesql @strSQL,N'@value int output',@value output --动态SQL中使用变量时,可使用存储过程sp_executesql
--select @value
--endregion