存储过程示意如下,和上次不同的是加了BEGIN END,按你的写法每次都会在if @Top=1 后 goto conn_use ,加BEGIN END后不会。
调用方式 exec Proc_Report 1或者exec Proc_Report(这里默认参数是4)。
ASP我不熟悉,不过如果用ADO,那应该差不多的,可以:
set rs=con.execute("exec Proc_Report 1")
Create Procedure Proc_Report
@Top smallint=4
as
set nocount on
declare @condition as varchar(1000)
set @condition=''
if @top not in (1,2,3,4)
begin
RAISERROR ('The Para @Top Must in 1,2,3,4.',16,1)
return
end
if @top=1
begin
set @condition=''
goto conn_use
endif @top=2
begin
set @conditon=' and a=1'
goto conn_use
end
if @top=3
begin
set @condition=' and a=1 and b=1'
goto conn_use
end
if @top=4
begin
set @condition=" and a=2 and b>1"
goto conn_use
end
conn_use:
exec ('select * from table_name where c=1 and d>0 '+ @condition ) set nocount off
调用方式 exec Proc_Report 1或者exec Proc_Report(这里默认参数是4)。
ASP我不熟悉,不过如果用ADO,那应该差不多的,可以:
set rs=con.execute("exec Proc_Report 1")
Create Procedure Proc_Report
@Top smallint=4
as
set nocount on
declare @condition as varchar(1000)
set @condition=''
if @top not in (1,2,3,4)
begin
RAISERROR ('The Para @Top Must in 1,2,3,4.',16,1)
return
end
if @top=1
begin
set @condition=''
goto conn_use
endif @top=2
begin
set @conditon=' and a=1'
goto conn_use
end
if @top=3
begin
set @condition=' and a=1 and b=1'
goto conn_use
end
if @top=4
begin
set @condition=" and a=2 and b>1"
goto conn_use
end
conn_use:
exec ('select * from table_name where c=1 and d>0 '+ @condition ) set nocount off
declare @total as int
exec('select @total=count(column_name) from table_name where c=1
and d>0' +@condition)
我要怎样才能取得@total的值呢。
另外,在sql server中有
if ...
...
else if
...
else if
...
else
这样的写法吗???
if ...
...
else if
...
else if
...
else
你可以用
if(condition)
begin
.......
end
if(condition)
begin
.......
end
来实现
declare @total as int
EXECUTE sp_executesql N'select @Para=count(column_name) from table_name where c=1 and d>0' +@condition,N'@Para int OUTPUT', @Para=@total)
注意把@condition定义成 nvarchar(1000)
sql server中有
if .. else结构,但要注意用BEGIN END。IF (...)
BEGIN
...
END
ELSE
BEGIN
IF (...)
BEGIN
...
END
ELSE
BEGIN
END
END
能不能帮我看看下面的代码啊,关于ASP调用存储过程中的临时表,我在SQL Server Query Analyzer中执行存储过程能够看到看到临时表的内容,但是在asp中就是调不出.我知道我很过份,要求越来越多,但没办法,我都忙了一个多星期了,就是搞不定,只有找你帮忙了!!!
下面是存储过程的代码:
if exists (select name from sysobjects where
name='sp_StatOne_Area' and type='p')
drop procedure sp_StatOne_Area
go
CREATE PROCEDURE sp_StatOne_Area @num int,@loginer varchar(10),@xiaqu varchar(40),@strStartDate varchar(10),@strEndDate varchar(10) AS
declare @startdate as datetime,@enddate as datetime
declare @vehicle_type as varchar(12)
--,@vehicle_type1 as varchar(12),@vehicle_type2 as varchar(12),@vehicle_type3 as varchar(12),@vehicle_type4 as varchar(12),@vehicle_type5 as varchar(12),@vehicle_type6 as varchar(12),@vehicle_type7 as varchar(12)
declare @sendnum as int,@one_hege As int, @repeate_hege As int,@rate_one_hege as varchar(4),@rate_repeate_hege as varchar(4)
declare @trade_num As int,@trade_one_hege As int,@trade_repeate_hege As int,@rate_trade_one_hege as varchar(4),@rate_trade_repeate_hege As varchar(4)
declare @ntrade_num As int,@ntrade_one_hege As int,@ntrade_repeate_hege As int,@rate_ntrade_one_hege as varchar(4),@rate_ntrade_repeate_hege as varchar(4),@fanxiu as int
declare @tmpStation as varchar(50)
select @startdate=cast(@strstartdate as datetime)
select @enddate=cast(@strenddate as datetime)
declare @tongji_type as varchar(12)
--if exists (select name from sysobjects where name='#StatOne' and type='t')
-- truncate table #StatOne
--else
CREATE TABLE #StatOne (
ID int NULL,
[统计类别] [varchar] (12) NULL ,
[送检辆数] [int] NULL ,
[一次合格] [int] NULL ,
[一次合格率] [varchar] (4) NULL ,
[复检合格] [int] NULL ,
[复检合格率] [varchar] (4) NULL ,
[营运辆数] [int] NULL ,
[营运一次合格] [int] NULL ,
[营运一次合格率] [varchar] (4) NULL ,
[营运复检合格] [int] NULL ,
[营运复检合格率] [varchar] (4) NULL ,
[非营运辆数] [int] NULL ,
[非营运一次合格] [int] NULL ,
[非营运一次合格率] [varchar] (4) NULL ,
[非营运复检合格] [int] NULL ,
[非营运复检合格率] [varchar] (4) NULL,
) ON [PRIMARY]
--truncate table #StatOne
insert into #StatOne (ID,统计类别) Values (1,'总计')
insert into #StatOne (ID,统计类别) Values (2,'等级评定')
insert into #StatOne (ID,统计类别) Values (3,'二级维护')
insert into #StatOne (ID,统计类别) Values (4,'汽车大修')
insert into #StatOne (ID,统计类别) Values (5,'其它')
....
....
....
--中间代码太长,省略!
----------计算合格率------------
update #StatOne set 送检辆数=@sendnum,一次合格=@one_hege,一次合格率=@rate_one_hege,
复检合格=@repeate_hege,复检合格率=@rate_repeate_hege,
营运辆数=@trade_num,营运一次合格=@trade_one_hege,营运一次合格率=@rate_trade_one_hege,
营运复检合格=@trade_repeate_hege,营运复检合格率=@rate_trade_repeate_hege,
非营运辆数=@ntrade_num,非营运一次合格=@ntrade_one_hege,非营运一次合格率=@rate_ntrade_one_hege,
非营运复检合格=@ntrade_repeate_hege,非营运复检合格率=@rate_ntrade_repeate_hege
where 统计类别=@vehicle_type
fetch StatOne_Cursor into @vehicle_type
end
close StatOne_Cursor
deallocate StatOne_Cursor
end
select * from #StatOne
下面是asp的代码:
<%
DateBegin=request("DateBegin")
DateEnd=request("DateEnd")
set conn=Server.CreateObject("ADODB.Connection")
conn.open Application("dbSet")
'在前面的Asp文件中已经定义了Application("dbSet")
set rs1=Server.CreateObject("ADODB.Recordset")
Set cm1=Server.CreateObject("ADODB.Command")
With cm1
.ActiveConnection = conn
.CommandType = 4
.CommandText = "sp_StatOne_Area"
.Parameters.Append .CreateParameter("num", 3,1, 1)
.Parameters.Append .CreateParameter("loginer", 129,1, 10)
.Parameters.Append .CreateParameter("Xiaqu", 129,1, 40)
.Parameters.Append .CreateParameter("paramstartdate", 129,1, 10)
.Parameters.Append .CreateParameter("paramenddate", 129,1,10)
.Parameters("num")=1
.Parameters("loginer") ="*"
.Parameters("Xiaqu") ="*"
.Parameters("paramstartdate") = DateBegin
.Parameters("paramenddate") = DateEnd
End With
Set rs1 = cm1.Execute()
%>
<html><head>
<META name=VI60_defaultClientScript content=VBScript>
<meta http-equiv="Content-Language" content="zh-cn">
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
</head>
<body bgcolor="#FFFF99">
<table>
<tr>
<% howmanyfields=rs1.fields.count - 1
for i=0 to howmanyfields
%>
<td><strong><%=rs1(i).name%></strong></td>
<% next %>
</tr>
<% do while not rs1.eof %>
<tr>
<% for i=0 to howmanyfields
thisvalue=rs1(i)
if isnull(thisvalue) then
thisvalue=" "
end if
%>
<td><%=thisvalue%></td>
<%next%>
</tr>
<%rs1.movenext
loop
rs1.close
set rs1=nothing
set conn=nothing
%>
</table>
</body>
</html>
declare @total int
EXECUTE sp_executesql N'select @Para=count(column_name) from table_name where c=1 and d>0' +@condition,N'@Para int OUTPUT', @Para=@total OUTPUT
declare @total int
declare @str nvarchar(1000)
set @str=N'select @Para=count(column_name) from table_name where c=1 and d>0' +@condition
EXECUTE sp_executesql @str,N'@Para int OUTPUT', @Para=@total OUTPUT
ASP我不熟,你试一下把下面两句该一下:
.Parameters.Append .CreateParameter("paramstartdate", 129,1, 10)
.Parameters.Append .CreateParameter("paramenddate", 129,1,10)
改成:
.Parameters.Append .CreateParameter("paramstartdate", 135,1)
.Parameters.Append .CreateParameter("paramenddate", 135,1)
固定表的时候,用asp,sql server测试,都通过了。现在是,我不知道怎样在asp中调存储过程的临时表,我在sql server里面能够看到临时表的信息,asp就是不行!
CHAR:129
VARCHAR:200 .Parameters.Append .CreateParameter("loginer", 129,1, 10)
.Parameters.Append .CreateParameter("Xiaqu", 129,1, 40)
.Parameters.Append .CreateParameter("paramstartdate", 129,1, 10)
.Parameters.Append .CreateParameter("paramenddate", 129,1,10)
改为
.Parameters.Append .CreateParameter("loginer", 200,1, 10)
.Parameters.Append .CreateParameter("Xiaqu", 200,1, 40)
.Parameters.Append .CreateParameter("paramstartdate", 200,1, 10)
.Parameters.Append .CreateParameter("paramenddate", 200,1,10)