怎样在SQL Server存储过程中使用临时表？

[icyer]

我建立了一个存储过程：
CREATE PROCEDURE Test @CustomerID char(5)
AS

CREATE TABLE #tb
(
col char(5)
)
INSERT INTO #tb (col) VALUES (@CustomerID)
SELECT col FROM #tb
DROP TABLE #tb

然后在VB中调用：
Dim param As New ADODB.Parameter
param.Direction = adParamInput
param.Name = "@CustomerID"
param.Size = 5
param.Type = adChar
param.Value = "aaaaa"
Dim cmd As New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandText = "Test"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append param

Dim rs As New ADODB.Recordset
Set rs = cmd.Execute

现在得到的rs，状态是关闭的。也就是说rs.State为0，无论做什么操作都会报错。
应该怎么做？
Thanks.

[saucer]

try


CREATE PROCEDURE Test @CustomerID char(5)
AS

CREATE TABLE #tb
(
col char(5)
)

set nocount on
INSERT INTO #tb (col) VALUES (@CustomerID)
set nocount off
SELECT col FROM #tb
DROP TABLE #tb

[nan1102]

Dim rs As New ADODB.Recordset
xx = "aaaaa"
str = "exec test '" & xx & "'
rs.Open str, cnnmydb, adOpenStatic, adLockReadOnly
If Not (rs.EOF And rs.BOF) Then
………………
end if

'你好，这样是试一下，还不行的话，可以先不要删掉临时表看看结果。最主要的是
你的存储过程在sql查询分析器中是否通过。(你的应该没问题)

[icyer]

Thanks.