--处理@Where SET @Where=N' Where (1>0 '+@Where +N')'--处理查询字符串 SELECT @SQL=@FieldShow+@Where, @SQL=@SQL+N' SET @Rows=@@ROWCOUNT "PRINT @SQLEXEC SP_EXECUTESQL @SQL, N'@Rows INT OUTPUT',@Rows OUTPUT
IF @@ERROR >0 BEGIN SET @lngErrCode=810 RETURN END
SET @lngErrCode=0GO --------------------------------------应用程序前台新建类,函数如下:Public Function GetNcye(ByRef strValues() As String, _ Optional ByVal strWhere As String = "", _ Optional ByVal FieldShow As String = "", _ Optional ByRef lngErrCode As Long = 0) As Boolean On Error GoTo err
Dim sql As String Dim i As Long, j As Long, k As Long, l As Long Dim cd As ADODB.Command Dim rs As ADODB.Recordset
GetNcye = False
If Connect = False Then Exit Function
Set cd = New ADODB.Command cd.CommandText = "e_SaleOrder" cd.CommandType = adCmdStoredProc Set cd.ActiveConnection = g_Cnn
cd.Parameters("@v").Value = 15153 If Len(strWhere) > 0 Then cd.Parameters("@Where").Value = strWhere If Len(strFieldOrder) > 0 Then cd.Parameters("@FieldShow").Value = FieldShow
g_Cnn.Errors.Clear
Set rs = New ADODB.Recordset With rs.AbsolutePage .CursorLocation = adUseClient .CursorType = adOpenForwardOnly .LockType = adLockReadOnly End With Set rs = cd.Execute
as
sql_statement
例:
数据库端假设存储过程ncye是这样设的CREATE PROCEDURE ncye@strTableName NVARCHAR(100)=N'',
@Rows INT=0 OUTPUT,
@Where NVARCHAR(4000)=N'',
@FieldShow NVARCHAR(3000)=N'',
@FieldOrder NVARCHAR(1000)=N'',
@lngErrCode INT=0 OUTPUT ASDECLARE @sql NVARCHAR(4000)SET NOCOUNT ON
--处理@Where
SET @Where=N' Where (1>0 '+@Where +N')'--处理查询字符串
SELECT
@SQL=@FieldShow+@Where,
@SQL=@SQL+N' SET @Rows=@@ROWCOUNT "PRINT @SQLEXEC SP_EXECUTESQL @SQL,
N'@Rows INT OUTPUT',@Rows OUTPUT
IF @@ERROR >0
BEGIN
SET @lngErrCode=810
RETURN
END
SET @lngErrCode=0GO
--------------------------------------应用程序前台新建类,函数如下:Public Function GetNcye(ByRef strValues() As String, _
Optional ByVal strWhere As String = "", _
Optional ByVal FieldShow As String = "", _
Optional ByRef lngErrCode As Long = 0) As Boolean
On Error GoTo err
Dim sql As String
Dim i As Long, j As Long, k As Long, l As Long
Dim cd As ADODB.Command
Dim rs As ADODB.Recordset
GetNcye = False
If Connect = False Then Exit Function
Set cd = New ADODB.Command
cd.CommandText = "e_SaleOrder"
cd.CommandType = adCmdStoredProc
Set cd.ActiveConnection = g_Cnn
cd.Parameters("@v").Value = 15153
If Len(strWhere) > 0 Then cd.Parameters("@Where").Value = strWhere
If Len(strFieldOrder) > 0 Then cd.Parameters("@FieldShow").Value = FieldShow
g_Cnn.Errors.Clear
Set rs = New ADODB.Recordset
With rs.AbsolutePage
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
End With
Set rs = cd.Execute
'返回值从这儿取
lngErrCode = cd.Parameters("@lngErrCode").Value
'记录集在这儿取,如果有需要,相可以把rs转为数组或XML
If lngErrCode = 0 Then
i = cd.Parameters("@Rows").Value
j = rs.Fields.Count - 1
ReDim strValues(i, j)
If i > 0 Then
rs.MoveFirst
For k = 0 To j
strValues(0, k) = rs.Fields(k).Name
Next
For k = 1 To i
For l = 0 To j
strValues(k, l) = rs.Fields(l) & ""
Next
rs.MoveNext
Next
End If
GetNcye = True
End If
aa:
rs.Close
Set rs = Nothing
DisconnectExit Functionerr:
MsgBox err.Description
Exit FunctionEnd Function
中间一段写错了,从自己程序中COPY下来的,不好意思…… Set cd = New ADODB.Command
cd.CommandText = "ncye"
cd.CommandType = adCmdStoredProc
Set cd.ActiveConnection = g_Cnn
If Len(strWhere) > 0 Then cd.Parameters("@Where").Value = strWhere
If Len(strFieldOrder) > 0 Then cd.Parameters("@FieldShow").Value = FieldShow