执行如下SQL
declare
@t
int
exec ( 'set @t=1 ' )
print ( @t )
exec ( 'set @t=1 ' )
print ( @t )
报如下错误!
服务器: 消息 137,级别 15,状态 2,行 1
必须声明变量 '@t'。
实际就是变量与动态语句不能共享,
那么换成这样就行,
declare
@t
int
set @t = 1
print ( @t )
set @t = 1
print ( @t )
或
exec
(
'
declare @t int;
set @t=1;
print(@t) ' )
set @t=1;
print(@t) ' )
但是有时候你一定要,变量与动态SQL结合起来。
比如,你在存储过程中定义一个输出参数,◎COUNT int output
而你在获得这个值的最好的方法就是动态SQL,那么有什么好的方式吗?
那就要用到系统存储过程,sp_executesql
declare @sql2 nvarchar ( 500 )
set @sql2 = ' select @COUNT = count(distinct( ' + @groupby + ' )) from TG_ENTRY where ' + @sqlWhere
execute sp_executesql
@sql2 ,
N ' @COUNT int output ' ,
@TOTAL_COUNT output
评论:
#3楼
2005-07-08 13:30 |
edobnet [未注册用户]
系统存储过程,sp_executesql
语言在这里!
sp_executesql
执行可以多次重用或动态生成的 Transact-SQL 语句或批处理。Transact-SQL 语句或批处理可以包含嵌入参数。
语法
sp_executesql [@stmt =] stmt
[
{, [@params =] N'@parameter_name data_type [,...n]' }
{, [@param1 =] 'value1' [,...n] }
]
参数
[@stmt =] stmt
包含 Transact-SQL 语句或批处理的 Unicode 字符串,stmt 必须是可以隐式转换为 ntext 的 Unicode 常量或变量。不允许使用更复杂的 Unicode 表达式(例如使用 + 运算符串联两个字符串)。不允许使用字符常量。如果指定常量,则必须使用 N 作为前缀。例如,Unicode 常量 N'sp_who' 是有效的,但是字符常量 'sp_who' 则无效。字符串的大小仅受可用数据库服务器内存限制。
stmt 可以包含与变量名形式相同的参数,例如:
N'SELECT * FROM Employees WHERE EmployeeID = @IDParameter'
stmt 中包含的每个参数在 @params 参数定义列表和参数值列表中均必须有对应项。
[@params =] N'@parameter_name data_type [,...n]'
字符串,其中包含已嵌入到 stmt 中的所有参数的定义。该字符串必须是可以隐式转换为 ntext 的 Unicode 常量或变量。每个参数定义均由参数名和数据类型组成。n 是表明附加参数定义的占位符。stmt 中指定的每个参数都必须在 @params 中定义。如果 stmt 中的 Transact-SQL 语句或批处理不包含参数,则不需要 @params。该参数的默认值为 NULL。
[@param1 =] 'value1'
参数字符串中定义的第一个参数的值。该值可以是常量或变量。必须为 stmt 中包含的每个参数提供参数值。如果 stmt 中包含的 Transact-SQL 语句或批处理没有参数,则不需要值。
n
附加参数的值的占位符。这些值只能是常量或变量,而不能是更复杂的表达式,例如函数或使用运算符生成的表达式。
返回代码值
0(成功)或 1(失败)
测试功能与效率都比,EXEC要高!
回复 引用 查看
语言在这里!
sp_executesql
执行可以多次重用或动态生成的 Transact-SQL 语句或批处理。Transact-SQL 语句或批处理可以包含嵌入参数。
语法
sp_executesql [@stmt =] stmt
[
{, [@params =] N'@parameter_name data_type [,...n]' }
{, [@param1 =] 'value1' [,...n] }
]
参数
[@stmt =] stmt
包含 Transact-SQL 语句或批处理的 Unicode 字符串,stmt 必须是可以隐式转换为 ntext 的 Unicode 常量或变量。不允许使用更复杂的 Unicode 表达式(例如使用 + 运算符串联两个字符串)。不允许使用字符常量。如果指定常量,则必须使用 N 作为前缀。例如,Unicode 常量 N'sp_who' 是有效的,但是字符常量 'sp_who' 则无效。字符串的大小仅受可用数据库服务器内存限制。
stmt 可以包含与变量名形式相同的参数,例如:
N'SELECT * FROM Employees WHERE EmployeeID = @IDParameter'
stmt 中包含的每个参数在 @params 参数定义列表和参数值列表中均必须有对应项。
[@params =] N'@parameter_name data_type [,...n]'
字符串,其中包含已嵌入到 stmt 中的所有参数的定义。该字符串必须是可以隐式转换为 ntext 的 Unicode 常量或变量。每个参数定义均由参数名和数据类型组成。n 是表明附加参数定义的占位符。stmt 中指定的每个参数都必须在 @params 中定义。如果 stmt 中的 Transact-SQL 语句或批处理不包含参数,则不需要 @params。该参数的默认值为 NULL。
[@param1 =] 'value1'
参数字符串中定义的第一个参数的值。该值可以是常量或变量。必须为 stmt 中包含的每个参数提供参数值。如果 stmt 中包含的 Transact-SQL 语句或批处理没有参数,则不需要值。
n
附加参数的值的占位符。这些值只能是常量或变量,而不能是更复杂的表达式,例如函数或使用运算符生成的表达式。
返回代码值
0(成功)或 1(失败)
测试功能与效率都比,EXEC要高!
回复 引用 查看
#4楼
2005-07-08 14:37 |
power [未注册用户]
最近用存储过程做分页,也是遇到这个问题,按照楼主的方法可以通过。但在赋值的时候,由于我用联合查询,语句比较长,由于stmt必须为nvarchar类型,最大4000。在赋值时发现整个语句不能完全输入而产生错误。 请楼主帮忙看看。
CREATE PROCEDURE dbo.pagination
(
@pagesize int,
@pageindex int,
@docount bit,
@dataname varchar(20),
@datafield varchar(1000),
@where varchar(5000),
@order varchar(20)
)
AS
set nocount on
declare @intRootRecordCount int
declare @RecordCount int
declare @Tmp nvarchar(4000)
select @Tmp='select @SPintRootRecordCount = count(0) from '+rtrim(ltrim(@dataname))+' '+rtrim(ltrim(@where))
execute sp_executesql
@Tmp,
N'@SPintRootRecordCount int OUTPUT',
@SPintRootRecordCount=@intRootRecordCount OUTPUT
select @RecordCount = @intRootRecordCount
.............以下省略
-----------------
下面是执行存储过程及赋值情况:
exec pagination @pageindex = 1, @pagesize = 1, @docount = 1, @dataname = N'dbo.titles INNER JOIN dbo.uinfo ON dbo.titles.poster = dbo.uinfo.uid', @datafield = N'dbo.titles.aid, dbo.titles.title, dbo.titles.content, dbo.titles.subject, dbo.titles.poster,dbo.titles.repid, dbo.titles.posttime, substring(dbo.titles.ipadd,1,8)+[*.*]as ipadd,dbo.uinfo.underwrite, dbo.uinfo.uhtml,dbo.uinfo.uimgurl,dbo.uinfo.uadd, dbo.uinfo.ucountry, dbo.uinfo.logintime,(dbo.uinfo.outputno+dbo.uinfo.outputrepno) as outputno,(dbo.uinfo.outputno+dbo.uinfo.outputrepno)*10 as myjy,dbo.uinfo.isbestno', @where = N'WHERE (dbo.titles.subject = 3) AND (dbo.titles.aid = 146) OR (dbo.titles.subject = 3) AND (dbo.titles.repid = 146) AND (dbo.titles.delflag =0)', @order = N'dbo.titles.aid'
回复 引用 查看
CREATE PROCEDURE dbo.pagination
(
@pagesize int,
@pageindex int,
@docount bit,
@dataname varchar(20),
@datafield varchar(1000),
@where varchar(5000),
@order varchar(20)
)
AS
set nocount on
declare @intRootRecordCount int
declare @RecordCount int
declare @Tmp nvarchar(4000)
select @Tmp='select @SPintRootRecordCount = count(0) from '+rtrim(ltrim(@dataname))+' '+rtrim(ltrim(@where))
execute sp_executesql
@Tmp,
N'@SPintRootRecordCount int OUTPUT',
@SPintRootRecordCount=@intRootRecordCount OUTPUT
select @RecordCount = @intRootRecordCount
.............以下省略
-----------------
下面是执行存储过程及赋值情况:
exec pagination @pageindex = 1, @pagesize = 1, @docount = 1, @dataname = N'dbo.titles INNER JOIN dbo.uinfo ON dbo.titles.poster = dbo.uinfo.uid', @datafield = N'dbo.titles.aid, dbo.titles.title, dbo.titles.content, dbo.titles.subject, dbo.titles.poster,dbo.titles.repid, dbo.titles.posttime, substring(dbo.titles.ipadd,1,8)+[*.*]as ipadd,dbo.uinfo.underwrite, dbo.uinfo.uhtml,dbo.uinfo.uimgurl,dbo.uinfo.uadd, dbo.uinfo.ucountry, dbo.uinfo.logintime,(dbo.uinfo.outputno+dbo.uinfo.outputrepno) as outputno,(dbo.uinfo.outputno+dbo.uinfo.outputrepno)*10 as myjy,dbo.uinfo.isbestno', @where = N'WHERE (dbo.titles.subject = 3) AND (dbo.titles.aid = 146) OR (dbo.titles.subject = 3) AND (dbo.titles.repid = 146) AND (dbo.titles.delflag =0)', @order = N'dbo.titles.aid'
回复 引用 查看
#5楼
[楼主]
2005-07-12 18:47 |
edobnet
针对这个问题我也遇到过,可以把SQL把成两个语句,中间通过,临时表来关连,(动态SQL可以访问实际创建的临时表(不是动态创建的))
看的例子也行:
IF
EXISTS
(
SELECT
*
FROM
sysobjects
WHERE
type
=
'
P
'
AND
name
=
'
sp_tg006
'
)
BEGIN
PRINT ' Dropping Procedure sp_tg006 '
DROP Procedure sp_tg006
END
GO
PRINT ' Creating Procedure sp_tg006 '
GO
/**/ /******************************************************************************
** File:
** Name: sp_tg006
** Desc:
**
** This template can be customized:
**
** Return values:
**
** Called by:
**
** Parameters:
** Input Output
** ---------- -----------
*/
create Procedure sp_tg006
/**/ /* Param List */
@TE_I_E_FLAG varchar ( 4 ) , -- 进出口
@TE_PASS_RANGE varchar ( 4 ) , -- 关区范围
@GG_ASSURE VARCHAR ( 4 ) , -- 担保要素选择
@GG_COUNT_TIME DATETIME , -- 起止时间
@GG_COUNT_TIMEEND DATETIME , -- 起止时间
@GG_FLAG varchar ( 4 ) , -- 担保形式
@GG_RE_ACCOUNT FLOAT , -- 担保金额范围(上限)
@GG_RE_ACCOUNTEND FLOAT , -- 担保金额范围(下限)
@USER_ID varchar ( 64 ),
@CUSTOMER_CODE varchar ( 4 ),
@PAGE_NUMBER int ,
@TOTAL_COUNT int OUTPUT
AS
/**/ /******************************************************************************
** File:
** Name: sp_tg006
** Desc:
**
** This GGmplaGG can be customized:
**
** Return values:
**
** Called by:
**
** ParameGGrs:
** Input Output
** ---------- -----------
**
** Auth:chengdj
** Date: 2005-04-08
********************************************************************************/
SET NOCOUNT ON
declare @sqlWhere varchar ( 1000 )
declare @sqlWhere2 varchar ( 1000 )
declare @sqlWhere3 varchar ( 1000 )
set @sqlWhere = ''
set @GG_COUNT_TIMEEND = DateADD ( Day , 1 , @GG_COUNT_TIMEEND ) -- 结束日期加一天
if @GG_FLAG = 1
set @sqlWhere = @sqlWhere + ' AND (GG_RE_ACCOUNT between ' + CONVERT ( varchar ( 20 ), @GG_RE_ACCOUNT ) + ' and ' + CONVERT ( varchar ( 20 ), @GG_RE_ACCOUNTEND ) + ' ) '
if @TE_I_E_FLAG <> ' -2 ' -- 进出口
set @sqlWhere = @sqlWhere + ' and TE_I_E_FLAG = ''' + @TE_I_E_FLAG + ''''
if @GG_FLAG <> ' -2 ' -- 担保形式
set @sqlWhere = @sqlWhere + ' and GG_FLAG = ''' + @GG_FLAG + ''''
if @TE_PASS_RANGE <> ' -2 ' -- 关区范围
set @sqlWhere = @sqlWhere + ' and TE_PASS_RANGE in ( select TE_PASS_RANGE from #CUSTOMER ) '
-- 备案时间 GG_REGR_DATE
set @sqlWhere2 = @sqlWhere + ' AND GG_REGR_DATE between ''' + CONVERT ( varchar ( 20 ), @GG_COUNT_TIME ) + ''' and ''' + CONVERT ( varchar ( 20 ), @GG_COUNT_TIMEEND ) + ''''
-- 企业范围取并集
set @sqlWhere3 = ' ((GG_COUNT_TIME between ''' + CONVERT ( varchar ( 20 ), @GG_COUNT_TIME ) + ''' and ''' + CONVERT ( varchar ( 20 ), @GG_COUNT_TIMEEND ) + ''' ) or
(GG_REGR_DATE between ''' + CONVERT ( varchar ( 20 ), @GG_COUNT_TIME ) + ''' and ''' + CONVERT ( varchar ( 20 ), @GG_COUNT_TIMEEND ) + ''' )) ' + @sqlWhere
-- 超期统计时间 GG_COUNT_TIME
set @sqlWhere = @sqlWhere + ' AND GG_COUNT_TIME between ''' + CONVERT ( varchar ( 20 ), @GG_COUNT_TIME ) + ''' and ''' + CONVERT ( varchar ( 20 ), @GG_COUNT_TIMEEND ) + ''''
-- 参数
-- 读参数表,
-- 输出参数
/**/ /*******************************************************************/
--
--
--
--
/**/ /********************************************************************/
declare @groupBy varchar ( 255 )
if @GG_ASSURE = ' 0 ' -- 一、 1、按经营单位
set @groupBy = ' TE_TRADE_CODE '
else if @GG_ASSURE = ' 1 ' -- - 1、按申报单位
set @groupBy = ' TE_AGENT_CODE '
else -- 1、全部
set @groupBy = ' TE_PASS_RANGE '
/**/ /*declare @sql2 nvarchar(4000)
set @sql2 = risk.HgCustomList(@TE_PASS_RANGE)+'select @COUNT = count(distinct('+@groupBy+')) from TG_GUARANTEE where '+@sqlWhere3
print(@sql2)
execute sp_executesql
@sql2,
N'@COUNT int output',
@TOTAL_COUNT output*/
/**/ /*CREATE TABLE #TG_006(
'+@groupBy+' VARCHAR(10) PRIMARY KEY NOT NULL,
REGR_TOTAL int null,--备案保证金总数 --@sqlWhere2
REGR_SUM int null,--备案保证金总额 --@sqlWhere2
UNCHK_OVER_TIME int null,--超期未核票数--@sqlWhere
CHK_OVER_TIME int null,--超期核销票数--@sqlWhere
UNCHK_OVER_TIME_PCT float null,--超期未核销率--@sqlWhere
CHK_OVER_TIME_PCT float null,--超期核销率 --@sqlWhere
DEF_BAIL_COUNT int null,--发生延期的保证金票数 --@sqlWhere2
EXAM_MNG int null,--审批期限管理 --@sqlWhere2
OVER_6_BAIL_COUNT int null,--超过6个月的保证金票数 --@sqlWhere2
NOT_ENOUGH int --不足额保证金票数 --- 新增 --@sqlWhere2
)
*/
DECLARE @START_ID INT
DECLARE @END_ID INT
SET @START_ID = ( @PAGE_NUMBER - 1 ) * 15 + 1
SET @END_ID = @PAGE_NUMBER * 15
CREATE TABLE #PAGE(
TID int identity ( 1 , 1 ) primary key ,
gb VARCHAR ( 10 ) NOT NULL ,
a1 int null ,
a2 int null ,
a3 numeric( 19 , 2 ) null ,
a4 int null ,
a5 int null ,
a6 int null ,
a7 int null ,
a8 int null ,
a9 int
);
declare @sql varchar ( 8000 )
set @sql = risk.HgCustomList( @TE_PASS_RANGE ) + '
insert into #PAGE(gb,a1,a2,a3,a4,a5,a6,a7,a8,a9)
select ' + @groupBy + ' ,a1,a2,a3,a4,a5,a6,a7,a8,a9
from (SELECT a. ' + @groupBy + ' ,
(SELECT COUNT(*) FROM TG_GUARANTEE WHERE ' + @groupBy + ' = a. ' + @groupBy + @sqlWhere2 + ' ) a1,
(SELECT COUNT(*) FROM TG_GUARANTEE WHERE ' + @groupBy + ' = a. ' + @groupBy + @sqlWhere + ' ) a2,
isnull((SELECT SUM(GG_RE_ACCOUNT) FROM TG_GUARANTEE WHERE ' + @groupBy + ' = a. ' + @groupBy + @sqlWhere2 + ' ),0) a3,
(SELECT COUNT(*) FROM TG_GUARANTEE WHERE ' + @groupBy + ' = a. ' + @groupBy + @sqlWhere + ' AND GG_COUNT_TIME < ''' + convert ( varchar ( 20 ), GETDATE ()) + ''' AND GG_C_DATE IS NULL) a4,
(SELECT COUNT(*) FROM TG_GUARANTEE WHERE GG_EXCEED_T_FLAG = 1 and ' + @groupBy + ' = a. ' + @groupBy + @sqlWhere + ' ) a5,
(SELECT COUNT(*) FROM TG_GUARANTEE WHERE GG_P_FLAG = 1 and ' + @groupBy + ' = a. ' + @groupBy + @sqlWhere2 + ' ) a6,
(SELECT COUNT(*) FROM TG_GUARANTEE WHERE GG_EXCEED_FLAG = 1 and ' + @groupBy + ' = a. ' + @groupBy + @sqlWhere2 + ' )a7,
(SELECT COUNT(*) FROM TG_GUARANTEE WHERE GG_6_FLAG = 1 and ' + @groupBy + ' = a. ' + @groupBy + @sqlWhere2 + ' ) a8,
(SELECT COUNT(*) FROM TG_GUARANTEE WHERE GG_NO_FLAG = 1 and ' + @groupBy + ' = a. ' + @groupBy + @sqlWhere2 + ' ) a9
from TG_GUARANTEE a where ' + @sqlWhere3 + ' group by ' + @groupBy + ' )
n where a1 <> 0 or (a2 <> 0 AND a4 <> 0) or a3 <> 0 or (a2 <> 0 AND a5 <> 0) or a6 <> 0 or a7 <> 0 or a8 <> 0 or a9 <> 0
'
print ( @sql )
exec ( @sql )
declare @sql2 nvarchar ( 4000 )
set @sql2 = '
CREATE TABLE #TG_006(
' + @groupBy + ' VARCHAR(10) PRIMARY KEY NOT NULL,
REGR_TOTAL int null,
REGR_ALL_1 int null,
REGR_SUM numeric(19,2) null,
UNCHK_OVER_TIME int null,
CHK_OVER_TIME int null,
UNCHK_OVER_TIME_PCT numeric(10,2) null,
CHK_OVER_TIME_PCT numeric(10,2) null,
DEF_BAIL_COUNT int null,
EXAM_MNG int null,
OVER_6_BAIL_COUNT int null,
NOT_ENOUGH int
);
select @COUNT = count(*) from #PAGE;
INSERT INTO #TG_006( ' + @groupBy + ' ,
REGR_TOTAL,
REGR_ALL_1,
REGR_SUM,
UNCHK_OVER_TIME,
CHK_OVER_TIME,
DEF_BAIL_COUNT,
EXAM_MNG,
OVER_6_BAIL_COUNT,
NOT_ENOUGH)
select gb,a1,a2,a3,a4,a5,a6,a7,a8,a9 from #PAGE a where TID between ' + convert ( varchar ( 20 ), @START_ID ) + ' AND ' + convert ( varchar ( 20 ), @END_ID ) + '
drop table #PAGE;
update #TG_006 set UNCHK_OVER_TIME_PCT = risk.HgDiv(UNCHK_OVER_TIME,REGR_ALL_1),
CHK_OVER_TIME_PCT = risk.HgDiv(CHK_OVER_TIME,REGR_ALL_1);
SELECT * FROM #TG_006;
DROP TABLE #TG_006
'
print ( @sql2 )
execute sp_executesql
@sql2 ,
N ' @COUNT int output ' ,
@TOTAL_COUNT output
SET NOCOUNT OFF
RETURN
/**/ /*
create Procedure sp_tg006
@TE_I_E_FLAG varchar(4) ,
@TE_PASS_RANGE varchar(4) ,
@GG_ASSURE VARCHAR(4) ,
@GG_COUNT_TIME DATETIME ,
@GG_COUNT_TIMEEND DATETIME ,
@GG_FLAG varchar(4) ,
@GG_RE_ACCOUNT FLOAT ,
@GG_RE_ACCOUNTEND FLOAT,
@USER_ID varchar(64),
@CUSTOMER_CODE varchar(4),
@PAGE_NUMBER int,
@TOTAL_COUNT int OUTPUT
AS
SET NOCOUNT ON
declare @sqlWhere varchar(1000)
declare @sqlWhere2 varchar(1000)
declare @sqlWhere3 varchar(1000)
set @sqlWhere = ''
set @GG_COUNT_TIMEEND = DateADD(Day,1,@GG_COUNT_TIMEEND)
set @sqlWhere = @sqlWhere + ' AND ((GG_RE_ACCOUNT >= '+ CONVERT(varchar(20),@GG_RE_ACCOUNT) +' and GG_RE_ACCOUNT < ='+CONVERT(varchar(20),@GG_RE_ACCOUNTEND) +') or (GG_FLAG = 0 ))'
if @TE_I_E_FLAG <> '-2'
set @sqlWhere = @sqlWhere + ' and TE_I_E_FLAG = '''+@TE_I_E_FLAG+''''
if @GG_FLAG <> '-2'
set @sqlWhere = @sqlWhere + ' and GG_FLAG = '''+@GG_FLAG+''''
if @TE_PASS_RANGE <> '-2'
set @sqlWhere = @sqlWhere + ' and TE_PASS_RANGE in ( select TE_PASS_RANGE from #CUSTOMER ) '
set @sqlWhere2 = @sqlWhere + ' AND GG_REGR_DATE >= '''+ CONVERT(varchar(20),@GG_COUNT_TIME) +''' and GG_REGR_DATE < '''+CONVERT(varchar(20),@GG_COUNT_TIMEEND)+''''
set @sqlWhere3 = ' ((GG_COUNT_TIME >= '''+ CONVERT(varchar(20),@GG_COUNT_TIME) +''' and GG_COUNT_TIME < '''+CONVERT(varchar(20),@GG_COUNT_TIMEEND)+''') or
(GG_REGR_DATE >= '''+ CONVERT(varchar(20),@GG_COUNT_TIME) +''' and GG_REGR_DATE < '''+CONVERT(varchar(20),@GG_COUNT_TIMEEND)+'''))'+@sqlWhere
set @sqlWhere = @sqlWhere + ' AND GG_COUNT_TIME >= '''+ CONVERT(varchar(20),@GG_COUNT_TIME) +''' and GG_COUNT_TIME < '''+CONVERT(varchar(20),@GG_COUNT_TIMEEND)+''''
declare @groupBy varchar(255)
if @GG_ASSURE = '0'
set @groupBy = 'TE_TRADE_CODE'
else if @GG_ASSURE = '1'
set @groupBy = 'TE_AGENT_CODE'
else
set @groupBy = 'TE_PASS_RANGE'
declare @sql2 nvarchar(4000)
set @sql2 = risk.HgCustomList(@TE_PASS_RANGE)+'select @COUNT = count(distinct('+@groupBy+')) from TG_GUARANTEE where '+@sqlWhere3
print(@sql2)
execute sp_executesql
@sql2,
N'@COUNT int output',
@TOTAL_COUNT output
DECLARE @START_ID INT
DECLARE @END_ID INT
SET @START_ID = (@PAGE_NUMBER - 1) * 15 + 1
SET @END_ID = @PAGE_NUMBER * 15
declare @sql varchar(8000)
set @sql = risk.HgCustomList(@TE_PASS_RANGE)+'
select top '+convert(varchar(20),@END_ID)+ ' '+ @groupBy +',IDENTITY(int,1,1) AS TID into #PAGE from TG_GUARANTEE where '+@sqlWhere3 +' group by '+@groupBy+ ';
CREATE TABLE #TG_006(
'+@groupBy+' VARCHAR(10) PRIMARY KEY NOT NULL,
REGR_TOTAL int null,
REGR_ALL_1 int null,
REGR_SUM numeric(19,2) null,
UNCHK_OVER_TIME int null,
CHK_OVER_TIME int null,
UNCHK_OVER_TIME_PCT numeric(10,2) null,
CHK_OVER_TIME_PCT numeric(10,2) null,
DEF_BAIL_COUNT int null,
EXAM_MNG int null,
OVER_6_BAIL_COUNT int null,
NOT_ENOUGH int
);
INSERT INTO #TG_006('+@groupBy+',
REGR_TOTAL,
REGR_ALL_1,
REGR_SUM,
UNCHK_OVER_TIME,
CHK_OVER_TIME,
DEF_BAIL_COUNT,
EXAM_MNG,
OVER_6_BAIL_COUNT,
NOT_ENOUGH)
SELECT a.'+@groupBy+',
(SELECT COUNT(*) FROM TG_GUARANTEE WHERE '+@groupBy+' = a.'+@groupBy+@sqlWhere2+'),
(SELECT COUNT(*) FROM TG_GUARANTEE WHERE '+@groupBy+' = a.'+@groupBy+@sqlWhere+'),
isnull((SELECT SUM(GG_RE_ACCOUNT) FROM TG_GUARANTEE WHERE '+@groupBy+' = a.'+@groupBy+@sqlWhere2+'),0),
(SELECT COUNT(*) FROM TG_GUARANTEE WHERE '+@groupBy+' = a.'+@groupBy+@sqlWhere+' AND GG_COUNT_TIME < '''+convert(varchar(20),GETDATE())+''' AND GG_C_DATE IS NULL),
(SELECT COUNT(*) FROM TG_GUARANTEE WHERE GG_EXCEED_T_FLAG = 1 and '+@groupBy+' = a.'+@groupBy+@sqlWhere+'),
(SELECT COUNT(*) FROM TG_GUARANTEE WHERE GG_P_FLAG = 1 and '+@groupBy+' = a.'+@groupBy+@sqlWhere2+'),
(SELECT COUNT(*) FROM TG_GUARANTEE WHERE GG_EXCEED_FLAG = 1 and '+@groupBy+' = a.'+@groupBy+@sqlWhere2+'),
(SELECT COUNT(*) FROM TG_GUARANTEE WHERE GG_6_FLAG = 1 and '+@groupBy+' = a.'+@groupBy+@sqlWhere2+'),
(SELECT COUNT(*) FROM TG_GUARANTEE WHERE GG_NO_FLAG = 1 and '+@groupBy+' = a.'+@groupBy+@sqlWhere2+')
from #PAGE a where TID between '+ convert(varchar(20),@START_ID)+ ' AND '+ convert(varchar(20),@END_ID)+'
group by a.'+@groupBy+';
drop table #PAGE;
update #TG_006 set UNCHK_OVER_TIME_PCT = risk.HgDiv(UNCHK_OVER_TIME,REGR_ALL_1),
CHK_OVER_TIME_PCT = risk.HgDiv(CHK_OVER_TIME,REGR_ALL_1);
SELECT * FROM #TG_006;
DROP TABLE #TG_006
'
print(@sql)
exec(@sql)
SET NOCOUNT OFF
RETURN
*/
GO
GRANT EXEC ON sp_tg006 TO PUBLIC
GO
BEGIN
PRINT ' Dropping Procedure sp_tg006 '
DROP Procedure sp_tg006
END
GO
PRINT ' Creating Procedure sp_tg006 '
GO
/**/ /******************************************************************************
** File:
** Name: sp_tg006
** Desc:
**
** This template can be customized:
**
** Return values:
**
** Called by:
**
** Parameters:
** Input Output
** ---------- -----------
*/
create Procedure sp_tg006
/**/ /* Param List */
@TE_I_E_FLAG varchar ( 4 ) , -- 进出口
@TE_PASS_RANGE varchar ( 4 ) , -- 关区范围
@GG_ASSURE VARCHAR ( 4 ) , -- 担保要素选择
@GG_COUNT_TIME DATETIME , -- 起止时间
@GG_COUNT_TIMEEND DATETIME , -- 起止时间
@GG_FLAG varchar ( 4 ) , -- 担保形式
@GG_RE_ACCOUNT FLOAT , -- 担保金额范围(上限)
@GG_RE_ACCOUNTEND FLOAT , -- 担保金额范围(下限)
@USER_ID varchar ( 64 ),
@CUSTOMER_CODE varchar ( 4 ),
@PAGE_NUMBER int ,
@TOTAL_COUNT int OUTPUT
AS
/**/ /******************************************************************************
** File:
** Name: sp_tg006
** Desc:
**
** This GGmplaGG can be customized:
**
** Return values:
**
** Called by:
**
** ParameGGrs:
** Input Output
** ---------- -----------
**
** Auth:chengdj
** Date: 2005-04-08
********************************************************************************/
SET NOCOUNT ON
declare @sqlWhere varchar ( 1000 )
declare @sqlWhere2 varchar ( 1000 )
declare @sqlWhere3 varchar ( 1000 )
set @sqlWhere = ''
set @GG_COUNT_TIMEEND = DateADD ( Day , 1 , @GG_COUNT_TIMEEND ) -- 结束日期加一天
if @GG_FLAG = 1
set @sqlWhere = @sqlWhere + ' AND (GG_RE_ACCOUNT between ' + CONVERT ( varchar ( 20 ), @GG_RE_ACCOUNT ) + ' and ' + CONVERT ( varchar ( 20 ), @GG_RE_ACCOUNTEND ) + ' ) '
if @TE_I_E_FLAG <> ' -2 ' -- 进出口
set @sqlWhere = @sqlWhere + ' and TE_I_E_FLAG = ''' + @TE_I_E_FLAG + ''''
if @GG_FLAG <> ' -2 ' -- 担保形式
set @sqlWhere = @sqlWhere + ' and GG_FLAG = ''' + @GG_FLAG + ''''
if @TE_PASS_RANGE <> ' -2 ' -- 关区范围
set @sqlWhere = @sqlWhere + ' and TE_PASS_RANGE in ( select TE_PASS_RANGE from #CUSTOMER ) '
-- 备案时间 GG_REGR_DATE
set @sqlWhere2 = @sqlWhere + ' AND GG_REGR_DATE between ''' + CONVERT ( varchar ( 20 ), @GG_COUNT_TIME ) + ''' and ''' + CONVERT ( varchar ( 20 ), @GG_COUNT_TIMEEND ) + ''''
-- 企业范围取并集
set @sqlWhere3 = ' ((GG_COUNT_TIME between ''' + CONVERT ( varchar ( 20 ), @GG_COUNT_TIME ) + ''' and ''' + CONVERT ( varchar ( 20 ), @GG_COUNT_TIMEEND ) + ''' ) or
(GG_REGR_DATE between ''' + CONVERT ( varchar ( 20 ), @GG_COUNT_TIME ) + ''' and ''' + CONVERT ( varchar ( 20 ), @GG_COUNT_TIMEEND ) + ''' )) ' + @sqlWhere
-- 超期统计时间 GG_COUNT_TIME
set @sqlWhere = @sqlWhere + ' AND GG_COUNT_TIME between ''' + CONVERT ( varchar ( 20 ), @GG_COUNT_TIME ) + ''' and ''' + CONVERT ( varchar ( 20 ), @GG_COUNT_TIMEEND ) + ''''
-- 参数
-- 读参数表,
-- 输出参数
/**/ /*******************************************************************/
--
--
--
--
/**/ /********************************************************************/
declare @groupBy varchar ( 255 )
if @GG_ASSURE = ' 0 ' -- 一、 1、按经营单位
set @groupBy = ' TE_TRADE_CODE '
else if @GG_ASSURE = ' 1 ' -- - 1、按申报单位
set @groupBy = ' TE_AGENT_CODE '
else -- 1、全部
set @groupBy = ' TE_PASS_RANGE '
/**/ /*declare @sql2 nvarchar(4000)
set @sql2 = risk.HgCustomList(@TE_PASS_RANGE)+'select @COUNT = count(distinct('+@groupBy+')) from TG_GUARANTEE where '+@sqlWhere3
print(@sql2)
execute sp_executesql
@sql2,
N'@COUNT int output',
@TOTAL_COUNT output*/
/**/ /*CREATE TABLE #TG_006(
'+@groupBy+' VARCHAR(10) PRIMARY KEY NOT NULL,
REGR_TOTAL int null,--备案保证金总数 --@sqlWhere2
REGR_SUM int null,--备案保证金总额 --@sqlWhere2
UNCHK_OVER_TIME int null,--超期未核票数--@sqlWhere
CHK_OVER_TIME int null,--超期核销票数--@sqlWhere
UNCHK_OVER_TIME_PCT float null,--超期未核销率--@sqlWhere
CHK_OVER_TIME_PCT float null,--超期核销率 --@sqlWhere
DEF_BAIL_COUNT int null,--发生延期的保证金票数 --@sqlWhere2
EXAM_MNG int null,--审批期限管理 --@sqlWhere2
OVER_6_BAIL_COUNT int null,--超过6个月的保证金票数 --@sqlWhere2
NOT_ENOUGH int --不足额保证金票数 --- 新增 --@sqlWhere2
)
*/
DECLARE @START_ID INT
DECLARE @END_ID INT
SET @START_ID = ( @PAGE_NUMBER - 1 ) * 15 + 1
SET @END_ID = @PAGE_NUMBER * 15
CREATE TABLE #PAGE(
TID int identity ( 1 , 1 ) primary key ,
gb VARCHAR ( 10 ) NOT NULL ,
a1 int null ,
a2 int null ,
a3 numeric( 19 , 2 ) null ,
a4 int null ,
a5 int null ,
a6 int null ,
a7 int null ,
a8 int null ,
a9 int
);
declare @sql varchar ( 8000 )
set @sql = risk.HgCustomList( @TE_PASS_RANGE ) + '
insert into #PAGE(gb,a1,a2,a3,a4,a5,a6,a7,a8,a9)
select ' + @groupBy + ' ,a1,a2,a3,a4,a5,a6,a7,a8,a9
from (SELECT a. ' + @groupBy + ' ,
(SELECT COUNT(*) FROM TG_GUARANTEE WHERE ' + @groupBy + ' = a. ' + @groupBy + @sqlWhere2 + ' ) a1,
(SELECT COUNT(*) FROM TG_GUARANTEE WHERE ' + @groupBy + ' = a. ' + @groupBy + @sqlWhere + ' ) a2,
isnull((SELECT SUM(GG_RE_ACCOUNT) FROM TG_GUARANTEE WHERE ' + @groupBy + ' = a. ' + @groupBy + @sqlWhere2 + ' ),0) a3,
(SELECT COUNT(*) FROM TG_GUARANTEE WHERE ' + @groupBy + ' = a. ' + @groupBy + @sqlWhere + ' AND GG_COUNT_TIME < ''' + convert ( varchar ( 20 ), GETDATE ()) + ''' AND GG_C_DATE IS NULL) a4,
(SELECT COUNT(*) FROM TG_GUARANTEE WHERE GG_EXCEED_T_FLAG = 1 and ' + @groupBy + ' = a. ' + @groupBy + @sqlWhere + ' ) a5,
(SELECT COUNT(*) FROM TG_GUARANTEE WHERE GG_P_FLAG = 1 and ' + @groupBy + ' = a. ' + @groupBy + @sqlWhere2 + ' ) a6,
(SELECT COUNT(*) FROM TG_GUARANTEE WHERE GG_EXCEED_FLAG = 1 and ' + @groupBy + ' = a. ' + @groupBy + @sqlWhere2 + ' )a7,
(SELECT COUNT(*) FROM TG_GUARANTEE WHERE GG_6_FLAG = 1 and ' + @groupBy + ' = a. ' + @groupBy + @sqlWhere2 + ' ) a8,
(SELECT COUNT(*) FROM TG_GUARANTEE WHERE GG_NO_FLAG = 1 and ' + @groupBy + ' = a. ' + @groupBy + @sqlWhere2 + ' ) a9
from TG_GUARANTEE a where ' + @sqlWhere3 + ' group by ' + @groupBy + ' )
n where a1 <> 0 or (a2 <> 0 AND a4 <> 0) or a3 <> 0 or (a2 <> 0 AND a5 <> 0) or a6 <> 0 or a7 <> 0 or a8 <> 0 or a9 <> 0
'
print ( @sql )
exec ( @sql )
declare @sql2 nvarchar ( 4000 )
set @sql2 = '
CREATE TABLE #TG_006(
' + @groupBy + ' VARCHAR(10) PRIMARY KEY NOT NULL,
REGR_TOTAL int null,
REGR_ALL_1 int null,
REGR_SUM numeric(19,2) null,
UNCHK_OVER_TIME int null,
CHK_OVER_TIME int null,
UNCHK_OVER_TIME_PCT numeric(10,2) null,
CHK_OVER_TIME_PCT numeric(10,2) null,
DEF_BAIL_COUNT int null,
EXAM_MNG int null,
OVER_6_BAIL_COUNT int null,
NOT_ENOUGH int
);
select @COUNT = count(*) from #PAGE;
INSERT INTO #TG_006( ' + @groupBy + ' ,
REGR_TOTAL,
REGR_ALL_1,
REGR_SUM,
UNCHK_OVER_TIME,
CHK_OVER_TIME,
DEF_BAIL_COUNT,
EXAM_MNG,
OVER_6_BAIL_COUNT,
NOT_ENOUGH)
select gb,a1,a2,a3,a4,a5,a6,a7,a8,a9 from #PAGE a where TID between ' + convert ( varchar ( 20 ), @START_ID ) + ' AND ' + convert ( varchar ( 20 ), @END_ID ) + '
drop table #PAGE;
update #TG_006 set UNCHK_OVER_TIME_PCT = risk.HgDiv(UNCHK_OVER_TIME,REGR_ALL_1),
CHK_OVER_TIME_PCT = risk.HgDiv(CHK_OVER_TIME,REGR_ALL_1);
SELECT * FROM #TG_006;
DROP TABLE #TG_006
'
print ( @sql2 )
execute sp_executesql
@sql2 ,
N ' @COUNT int output ' ,
@TOTAL_COUNT output
SET NOCOUNT OFF
RETURN
/**/ /*
create Procedure sp_tg006
@TE_I_E_FLAG varchar(4) ,
@TE_PASS_RANGE varchar(4) ,
@GG_ASSURE VARCHAR(4) ,
@GG_COUNT_TIME DATETIME ,
@GG_COUNT_TIMEEND DATETIME ,
@GG_FLAG varchar(4) ,
@GG_RE_ACCOUNT FLOAT ,
@GG_RE_ACCOUNTEND FLOAT,
@USER_ID varchar(64),
@CUSTOMER_CODE varchar(4),
@PAGE_NUMBER int,
@TOTAL_COUNT int OUTPUT
AS
SET NOCOUNT ON
declare @sqlWhere varchar(1000)
declare @sqlWhere2 varchar(1000)
declare @sqlWhere3 varchar(1000)
set @sqlWhere = ''
set @GG_COUNT_TIMEEND = DateADD(Day,1,@GG_COUNT_TIMEEND)
set @sqlWhere = @sqlWhere + ' AND ((GG_RE_ACCOUNT >= '+ CONVERT(varchar(20),@GG_RE_ACCOUNT) +' and GG_RE_ACCOUNT < ='+CONVERT(varchar(20),@GG_RE_ACCOUNTEND) +') or (GG_FLAG = 0 ))'
if @TE_I_E_FLAG <> '-2'
set @sqlWhere = @sqlWhere + ' and TE_I_E_FLAG = '''+@TE_I_E_FLAG+''''
if @GG_FLAG <> '-2'
set @sqlWhere = @sqlWhere + ' and GG_FLAG = '''+@GG_FLAG+''''
if @TE_PASS_RANGE <> '-2'
set @sqlWhere = @sqlWhere + ' and TE_PASS_RANGE in ( select TE_PASS_RANGE from #CUSTOMER ) '
set @sqlWhere2 = @sqlWhere + ' AND GG_REGR_DATE >= '''+ CONVERT(varchar(20),@GG_COUNT_TIME) +''' and GG_REGR_DATE < '''+CONVERT(varchar(20),@GG_COUNT_TIMEEND)+''''
set @sqlWhere3 = ' ((GG_COUNT_TIME >= '''+ CONVERT(varchar(20),@GG_COUNT_TIME) +''' and GG_COUNT_TIME < '''+CONVERT(varchar(20),@GG_COUNT_TIMEEND)+''') or
(GG_REGR_DATE >= '''+ CONVERT(varchar(20),@GG_COUNT_TIME) +''' and GG_REGR_DATE < '''+CONVERT(varchar(20),@GG_COUNT_TIMEEND)+'''))'+@sqlWhere
set @sqlWhere = @sqlWhere + ' AND GG_COUNT_TIME >= '''+ CONVERT(varchar(20),@GG_COUNT_TIME) +''' and GG_COUNT_TIME < '''+CONVERT(varchar(20),@GG_COUNT_TIMEEND)+''''
declare @groupBy varchar(255)
if @GG_ASSURE = '0'
set @groupBy = 'TE_TRADE_CODE'
else if @GG_ASSURE = '1'
set @groupBy = 'TE_AGENT_CODE'
else
set @groupBy = 'TE_PASS_RANGE'
declare @sql2 nvarchar(4000)
set @sql2 = risk.HgCustomList(@TE_PASS_RANGE)+'select @COUNT = count(distinct('+@groupBy+')) from TG_GUARANTEE where '+@sqlWhere3
print(@sql2)
execute sp_executesql
@sql2,
N'@COUNT int output',
@TOTAL_COUNT output
DECLARE @START_ID INT
DECLARE @END_ID INT
SET @START_ID = (@PAGE_NUMBER - 1) * 15 + 1
SET @END_ID = @PAGE_NUMBER * 15
declare @sql varchar(8000)
set @sql = risk.HgCustomList(@TE_PASS_RANGE)+'
select top '+convert(varchar(20),@END_ID)+ ' '+ @groupBy +',IDENTITY(int,1,1) AS TID into #PAGE from TG_GUARANTEE where '+@sqlWhere3 +' group by '+@groupBy+ ';
CREATE TABLE #TG_006(
'+@groupBy+' VARCHAR(10) PRIMARY KEY NOT NULL,
REGR_TOTAL int null,
REGR_ALL_1 int null,
REGR_SUM numeric(19,2) null,
UNCHK_OVER_TIME int null,
CHK_OVER_TIME int null,
UNCHK_OVER_TIME_PCT numeric(10,2) null,
CHK_OVER_TIME_PCT numeric(10,2) null,
DEF_BAIL_COUNT int null,
EXAM_MNG int null,
OVER_6_BAIL_COUNT int null,
NOT_ENOUGH int
);
INSERT INTO #TG_006('+@groupBy+',
REGR_TOTAL,
REGR_ALL_1,
REGR_SUM,
UNCHK_OVER_TIME,
CHK_OVER_TIME,
DEF_BAIL_COUNT,
EXAM_MNG,
OVER_6_BAIL_COUNT,
NOT_ENOUGH)
SELECT a.'+@groupBy+',
(SELECT COUNT(*) FROM TG_GUARANTEE WHERE '+@groupBy+' = a.'+@groupBy+@sqlWhere2+'),
(SELECT COUNT(*) FROM TG_GUARANTEE WHERE '+@groupBy+' = a.'+@groupBy+@sqlWhere+'),
isnull((SELECT SUM(GG_RE_ACCOUNT) FROM TG_GUARANTEE WHERE '+@groupBy+' = a.'+@groupBy+@sqlWhere2+'),0),
(SELECT COUNT(*) FROM TG_GUARANTEE WHERE '+@groupBy+' = a.'+@groupBy+@sqlWhere+' AND GG_COUNT_TIME < '''+convert(varchar(20),GETDATE())+''' AND GG_C_DATE IS NULL),
(SELECT COUNT(*) FROM TG_GUARANTEE WHERE GG_EXCEED_T_FLAG = 1 and '+@groupBy+' = a.'+@groupBy+@sqlWhere+'),
(SELECT COUNT(*) FROM TG_GUARANTEE WHERE GG_P_FLAG = 1 and '+@groupBy+' = a.'+@groupBy+@sqlWhere2+'),
(SELECT COUNT(*) FROM TG_GUARANTEE WHERE GG_EXCEED_FLAG = 1 and '+@groupBy+' = a.'+@groupBy+@sqlWhere2+'),
(SELECT COUNT(*) FROM TG_GUARANTEE WHERE GG_6_FLAG = 1 and '+@groupBy+' = a.'+@groupBy+@sqlWhere2+'),
(SELECT COUNT(*) FROM TG_GUARANTEE WHERE GG_NO_FLAG = 1 and '+@groupBy+' = a.'+@groupBy+@sqlWhere2+')
from #PAGE a where TID between '+ convert(varchar(20),@START_ID)+ ' AND '+ convert(varchar(20),@END_ID)+'
group by a.'+@groupBy+';
drop table #PAGE;
update #TG_006 set UNCHK_OVER_TIME_PCT = risk.HgDiv(UNCHK_OVER_TIME,REGR_ALL_1),
CHK_OVER_TIME_PCT = risk.HgDiv(CHK_OVER_TIME,REGR_ALL_1);
SELECT * FROM #TG_006;
DROP TABLE #TG_006
'
print(@sql)
exec(@sql)
SET NOCOUNT OFF
RETURN
*/
GO
GRANT EXEC ON sp_tg006 TO PUBLIC
GO