Stored Procedure, Return Recordset

• nullGumby

  SSC Veteran

  Points: 292

  More actions

  April 7, 2006 at 9:00 am

  #93745

  I'm using SQL Server 2000 and Access 2003 (front-end).

  I have a stored procedure that basically creates a temporary table:

  DECLARE @Results TABLE (PrimaryKeyID int)

  Inserts some records into it:

  INSERT INTO @Results (PrimaryKeyID)

  SELECT tblMyTable.PrimaryKeyID ...

  And then INNER JOINs "@Results" to another table.  This SELECT statement that INNER JOINs is the last statement in the sproc--and that's what I want to return to my Access 2003 application.

  This is my Access2K3 code:

  Dim cmd As ADODB.Command

  Dim prm As ADODB.Parameter

  Dim rst As ADODB.Recordset

    

  Set cmd = New ADODB.Command

  With cmd

    .ActiveConnection = GetConnection()

    .CommandText = "usp_MySproc"

    .CommandType = adCmdStoredProc

    Set prm = .CreateParameter("@PrimaryKeyID", adInteger, adParamInput, , GetPrimaryKey())

    .Parameters.Append prm

    Set rst = .Execute

  End With

  Anyone see any glaring errors in what I'm trying to do?  Any help is appriciated.  Thanks.

• nullGumby

  SSC Veteran

  Points: 292

  More actions

  April 7, 2006 at 9:13 am

  #631534

  Nevermind...I switched it over to a user-defined function (UDF) and it works just fine.

  Thanks.