create proc test
as
SET NOCOUNT ON --加上这句.
create table @tmp
(xmmc varchar(20),
sl numeric(9,2),
xh int
)
insert into #tmp (xmmc,sl,xh) values('',10.00,1)
insert into #tmp (xmmc,sl,xh) values('第二',20.00,2)
update #tmp set xmmc = '测试' where xh = 1
select * from #tmp
as
SET NOCOUNT ON --加上这句.
create table @tmp
(xmmc varchar(20),
sl numeric(9,2),
xh int
)
insert into #tmp (xmmc,sl,xh) values('',10.00,1)
insert into #tmp (xmmc,sl,xh) values('第二',20.00,2)
update #tmp set xmmc = '测试' where xh = 1
select * from #tmp
解决方案 »
- winsock中服务端如何连接多个客户端
- 如何获取验证码??
- 请问各位大哥,如何可以实现对整个文件夹的复制。。。
- 十万火急,高手请指教,我用Inet控件下载文件,用Inet1.OpenURL(UrtFile, icByteArray)时,如何实时得到下载文件的大小和比例啊???
- 高分!api有挑战!
- 一个菜鸟级的循环问题,SOS!!!
- VB里怎么用findwindow、GetWindowProcessID、writeprocessmemory
- vb + sql 6.5
- 急[Microsoft][ODBC驱动程序管理器]未发现数据源名称并且未指定默认驱
- 紧急问题
- 如何用Html Workshop制作帮助
- 如果我用vb写一个仓库管理的软件,但是它有分公司(不再同一个地方),那数据怎么传输呀
as
create table #tmp
(xmmc varchar(20),
sl numeric(9,2),
xh int
)
insert into #tmp (xmmc,sl,xh) values('',10.00,1)
insert into #tmp (xmmc,sl,xh) values('第二',20.00,2)
update #tmp set xmmc = '测试' where xh = 1
select * from #tmp
go
几乎任何可写成批处理的 Transact-SQL 代码都可用于创建存储过程。存储过程的设计规则
存储过程的设计规则包括:
CREATE PROCEDURE 定义本身可包括除下列 CREATE 语句以外的任何数量和类型的 SQL 语句,存储过程中的任意地方都不能使用下列语句: CREATE DEFAULT CREATE TRIGGER
CREATE PROCEDURE CREATE VIEW
CREATE RULE 可在存储过程中创建其它数据库对象。可以引用在同一存储过程中创建的对象,前提是在创建对象后再引用对象。可以在存储过程内引用临时表。如果在存储过程内创建本地临时表,则该临时表仅为该存储过程而存在;退出该存储过程后,临时表即会消失。如果执行调用其它存储过程的存储过程,那么被调用存储过程可以访问由第一个存储过程创建的、包括临时表在内的所有对象。如果执行在远程 Microsoft® SQL Server™ 2000 实例上进行更改的远程存储过程,则不能回滚这些更改。远程存储过程不参与事务处理。存储过程中参数的最大数目为 2100。存储过程中局部变量的最大数目仅受可用内存的限制。根据可用内存的不同,存储过程的最大大小可达 128 MB。
你VB的代码.
Public Function GetRecordsetBySP(ByVal spName As String, _
ByRef paramName() As String, _
ByRef paramType() As String, _
ByRef paramValue() As String, _
ByRef valueLength() As Integer, _
ByRef paramDirection() As Integer, _
ByRef paramNum As Integer) As ADODB.Recordset
Dim conn As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i As Integer
Dim paramArr() As New ADODB.Parameter
Dim cmd As New ADODB.Command
ErrMessage = ""
On Error GoTo ErrExec
Set conn = GetConnection()
With cmd
.CommandText = spName
.CommandType = adCmdStoredProc
.ActiveConnection = conn
End With
If paramNum > 0 Then
ReDim paramArr(0 To paramNum - 1)
End If
For i = 0 To paramNum - 1
Select Case VBA.Trim$(VBA.UCase$(paramType(i)))
Case "VARCHAR"
With paramArr(i)
.Name = paramName(i)
.Type = adVarChar
.Size = valueLength(i)
End With
Case "CHAR"
With paramArr(i)
.Name = paramName(i)
.Type = adChar
.Size = valueLength(i)
End With
Case "NVARCHAR"
With paramArr(i)
.Name = paramName(i)
.Type = adVarWChar
.Size = valueLength(i)
End With
Case "NCHAR"
With paramArr(i)
.Name = paramName(i)
.Type = adWChar
.Size = valueLength(i)
End With
Case "INTEGER"
With paramArr(i)
.Name = paramName(i)
.Type = adInteger
End With
Case "DATETIME"
With paramArr(i)
.Name = paramName(i)
.Type = adDate
End With
Case "FLOAT"
With paramArr(i)
.Name = paramName(i)
.Type = adNumeric
End With
End Select
Select Case paramDirection(i)
Case PARAM_INPUT
paramArr(i).Direction = adParamInput
Case PARAM_INPUTOUTPUT
paramArr(i).Direction = adParamInputOutput
Case PARAM_OUTPUT
paramArr(i).Direction = adParamOutput
Case PARAM_RESULT
paramArr(i).Direction = adParamReturnValue
End Select
paramArr(i).Value = paramValue(i)
cmd.Parameters.Append paramArr(i)
Next i
Set rs = cmd.Execute
Set GetRecordsetBySP = rs
Exit Function
ErrExec:
ErrMessage = "GetRecordsetBySP获取记录集失败.存储过程:" & spName
Set rs = Nothing
Set cmd = Nothing
End Function然后调用此函数,不能得到结果