declare @sql varchar(8000)
declare @nf varchar(20)
declare @yfvarchar(20)
set @sql = 'select m.orderid , m.orderno , m.ContSize , m.ContClass , m.contno '
select @sql = @sql + ' , max(case n.px when ''' + cast(px as varchar) + ''' then yfdx else '''' end) [yfdx' + cast(px as varchar) + ']'
+ ' , max(case n.px when ''' + cast(px as varchar) + ''' then yflx else '''' end) [yflx' + cast(px as varchar) + ']'
+ ' , max(case n.px when ''' + cast(px as varchar) + ''' then yfje else 0 end) [yfje' + cast(px as varchar) + ']'
from (select distinct px from (select * , px = (select count(*) from planyf where orderid = t.orderid and yfid < t.yfid) + 1 from planyf t) o) as a
set @sql = @sql + ' from planorder m ,(select * , px = (select count(*) from planyf where orderid = t.orderid and yfid < t.yfid) + 1 from planyf t) n where m.orderid = n.orderid and and year(m.orderno)=ny and month(M.orderno)=@yf group by m.orderid , m.OrderNo , m.ContSize , m.ContClass , m.contno'
exec(@sql)实在是不知道带参数的存储过程应该怎么写,我这么写了,但是调用的时候,说存储过程中不带参数,我郁闷了
--一例
create procedure InsertName
(
@username varchar(60),
@userPwd varchar(60),
@roleName varchar(20)
)
as
declare @RoleID decimal
begin
select RoleID from UserRole where roleName=@roleName set @RoleID=
end begin
Insert into UserLogin(loginID,username,RoleID)
values(@username,@userPwd,@RoleID)
end
declare @sql varchar(8000)
declare @nf varchar(20)
declare @yf varchar(20)
set @sql = 'select m.orderid , m.orderno , m.ContSize , m.ContClass , m.contno '
select @sql = @sql + ' , max(case n.px when ''' + cast(px as varchar) + ''' then yfdx else '''' end) [yfdx' + cast(px as varchar) + ']'
+ ' , max(case n.px when ''' + cast(px as varchar) + ''' then yflx else '''' end) [yflx' + cast(px as varchar) + ']'
+ ' , max(case n.px when ''' + cast(px as varchar) + ''' then yfje else 0 end) [yfje' + cast(px as varchar) + ']'
from (select distinct px from (select * , px = (select count(*) from planyf where orderid = t.orderid and yfid < t.yfid) + 1 from planyf t) o) as a
set @sql = @sql + ' from planorder m ,(select * , px = (select count(*) from planyf where orderid = t.orderid and yfid < t.yfid) + 1 from planyf t) n where m.orderid = n.orderid and and substring(m.orderno,2,2)='+@nf+' and substring(M.orderno,4,2)='+@yf+' group by m.orderid , m.OrderNo , m.ContSize , m.ContClass , m.contno'
exec(@sql)
Dim adocomm As New ADODB.Command 'Command 对象定义了将对数据源执行的指定命令。
Dim rs1 As New ADODB.Recordset 'Command 对象定义了将对数据源执行的指定命令。
adoconn.ConnectionString = Allconnect 'Adodc1为窗体中的ADO控件,并已成功连接数据库
adoconn.Open
Set adocomm.ActiveConnection = adoconn '指示指定的 Command对象当前所属的 Connection对象。
adocomm.CommandText = "xsfy" '设置Command对象源。
adocomm.CommandType = adCmdStoredProc '通知提供者CommandText属性有什么,它可能包括Command对象的源类型。设置这个属性优化了该命令的执行。
Set rs1 = adocomm.Execute以上的写法是我没有加@nf,@yf的时候,在VB里面是可以运行的那么如果加了参数,应该怎么带进去呢??请告诉下