Advertisement

01.02.2008 at 01:43PM PST, ID: 23054284
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!
9.2

Getting return values from a SQL stored procedure

Asked by Bonnie_K in Microsoft Access Database

Tags:

Hi,

I'm sure the answer to this question is somewhere in this database already, but I am having trouble finding it.  I am new to stored procedures.

I created a stored procedure in SQL and got some code to place in an access module to run the procedure.

How do I get some return values from the stored procedure?  I pasted the entire stored procedure below.  I would like to get back the @Return_Status, and possibly some of the @Rowcount_... values.  Also below is the code I am using in the access module.

Sorry if I am pasting too much code...

Thanks,
Bonnie



Public Function Archive() As Boolean
    Dim adoCon As ADODB.Connection
    Dim adoCmd As ADODB.Command
    Dim sql As String
    Dim sConnect As String
    Dim Result As String
   
    Set adoCon = New ADODB.Connection
    Set adoCmd = New ADODB.Command
   
    sConnect = "Provider='sqloledb';Data Source='ROSSAPP';Initial Catalog='Archive_royal_live'; User ID ='sa';Password='*****';"
    Debug.Print sConnect
    adoCon.ConnectionString = sConnect
    adoCon.Open
    adoCmd.ActiveConnection = adoCon
    With adoCmd
        .CommandText = "dbo.RC_Archive_JOB_STAGE"
        .CommandType = adCmdStoredProc
        .Execute
    End With
   
    Set adoCon = Nothing

End FunctionStart Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:

ALTER PROCEDURE [dbo].[RC_Archive_JOB_STAGE]
AS
 
/*Declare Variables*/
DECLARE @Rowcount_Pre_Archive INT
DECLARE @Rowcount_Select INT
DECLARE @Rowcount_Append INT
DECLARE @Rowcount_Delete INT
DECLARE @Rowcount_Post_Archive INT
DECLARE @Return_Status INT
DECLARE @A_Year NCHAR(4)
DECLARE @A_Period NCHAR(2)
DECLARE @A_Cut_Off DATETIME
DECLARE @A_Log_DB_Key INT
DECLARE @Table_Name NVARCHAR(100)
DECLARE @Table_Name_Archive NVARCHAR(100)
DECLARE @Table_Name_Short NVARCHAR(100)
DECLARE @Database_Name NVARCHAR(100)
DECLARE @String NVARCHAR(4000)
DECLARE @String_Delete NVARCHAR(4000)
DECLARE @String_Append NVARCHAR(4000)
DECLARE @Join_Statement NVARCHAR(4000)
DECLARE @Fields NVARCHAR(4000) 
 
 
 
BEGIN
 
/********************************************************************************************/
/*Set Variables - EDIT THESE*/
/*These three variables are self-explanatory and need to be edited for each table*/
SELECT @Table_Name = 'man_DEV.dbo.JOB_STAGE'
SELECT @Table_Name_Archive = 'Archive_man_live.dbo.JOB_STAGE'
SELECT @Table_Name_Short = 'JOB_STAGE'
 
/*This is the join used to create the temp table and determines which records are archived*/
SELECT @Join_Statement = 'RIGHT OUTER JOIN Archive_man_live.dbo.JOBS ON man_DEV.dbo.JOB_STAGE.FACTORY = Archive_man_live.dbo.JOBS.FACTORY 
AND man_DEV.dbo.JOB_STAGE.JOB_NUMBER = Archive_man_live.dbo.JOBS.JOB_NUMBER 
WHERE (man_DEV.dbo.JOB_STAGE.GEM_DBKEY IS NOT NULL)'
 
/*This is the field list, change the db name to fin or man as appropriate*/
SELECT @Fields = coalesce(@Fields + ',' , '' ) + name 
from Archive_man_live.dbo.syscolumns 
where id =(select id from Archive_man_live.dbo.sysobjects where name = @Table_Name_Short) and iscomputed = 0 and autoval is null
 
/*This delete string, comment out for testing*/
SELECT @String_Delete = ''
/*SELECT @String_Delete = 'DELETE FROM ' + @Table_Name + ' FROM ' + @Table_Name + @Join_Statement*/
 
/********************************************************************************************/
 
 
/*Set Variables - NO NEED TO EDIT THESE*/
SELECT @A_Year = dbo.Archive_Control.Archive_Year, @A_Period = dbo.Archive_Control.Archive_Period, @A_Cut_Off = dbo.Archive_Control.Archive_Cut_Off	
FROM dbo.Archive_Control
/*Here is the append statement, this may not need to be edited*/
SELECT @String_Append = 
'INSERT INTO ' + @Table_Name_Archive + ' (' + @Fields + ') SELECT '+ @Fields + ' FROM ' + @Table_Name + 
' RIGHT OUTER JOIN #Temp_Table ON ' + @Table_Name + '.GEM_DBKEY = #Temp_Table.TEMP_DBKEY'
 
END
 
/*Populate Archive log*/
BEGIN
INSERT INTO Archive_royal_live.dbo.Archive_Log
(
Archive_Table,
Archive_Start
)
SELECT 
@Table_Name,
GetDate()
END
 
/*Get Archive Log Key*/
BEGIN
SELECT @A_Log_DB_Key = MAX(DB_KEY) FROM dbo.Archive_Log
END
 
/*Get Pre Archive Row Count*/
BEGIN
SELECT @String = 'SELECT ' + @Table_Name + '.GEM_DBKEY FROM ' + @Table_Name
EXEC (@String)
SET @Rowcount_Pre_Archive = @@ROWCOUNT
END
 
/*Create Temp Table with records to append to archive table*/
BEGIN
CREATE TABLE #Temp_Table(TEMP_DBKEY INT PRIMARY KEY)
 
SELECT @String = 'INSERT INTO #Temp_Table (TEMP_DBKEY) SELECT ' + @Table_Name + '.GEM_DBKEY FROM ' + @Table_Name + ' ' + @Join_Statement
EXEC (@String)
 
SET @Rowcount_Select = @@ROWCOUNT
END
 
/*Append to archive table*/
BEGIN
EXEC (@String_Append)
 
SET @Rowcount_Append = @@ROWCOUNT
END
 
/*If the number of records selected equals the number appended, delete original records*/
IF @Rowcount_Select = @Rowcount_Append
 
BEGIN
Set @Return_Status = 1
 
EXEC (@String_Delete)
 
SET @Rowcount_Delete = @@ROWCOUNT
END
 
ELSE
 
BEGIN
Set @Return_Status = 2
END
 
/*Get rowcount of original table after archive operation*/
BEGIN
SELECT @String = 'SELECT ' + @Table_Name + '.GEM_DBKEY FROM ' + @Table_Name
EXEC (@String)
 
SET @Rowcount_Post_Archive = @@ROWCOUNT
END 
 
/*Update Archive Log*/
BEGIN
UPDATE Archive_royal_live.dbo.Archive_Log
SET 
Archive_Cut_Off = @A_Cut_Off,
Rows_Pre_Archive = @Rowcount_Pre_Archive,
Rows_Selected = @Rowcount_Select,
Rows_Inserted = @Rowcount_Append,
Rows_Deleted = @Rowcount_Delete,
Rows_Post_Archive = @Rowcount_Post_Archive,
Archive_Status = @Return_Status,
Archive_Date_Time = GetDate(),
Archive_End = GetDate()
WHERE DB_KEY = @A_Log_DB_Key
END
 
/*Drop Temp Table*/
DROP TABLE #Temp_Table
 
Keywords: Getting return values from a SQL store…
Title
1 rowcount with ASP
2 @@rowcount not working
3 Stored Procedures, comparing rowco…
4 Returning the @@ROWCOUNT from a …
5 RowCount
6 SQLOLEDB/ADO Identity Crisis!
 
Loading Advertisement...
 
[+][-]01.02.2008 at 05:48PM PST, ID: 20569795

View this solution now by starting your 30-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zone: Microsoft Access Database
Tags: VBA / SQL
Sign Up Now!
Solution Provided By: LPurvis
Participating Experts: 2
Solution Grade: A
 
 
[+][-]01.02.2008 at 10:30PM PST, ID: 20571041

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]01.03.2008 at 01:38AM PST, ID: 20571590

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]01.03.2008 at 06:05AM PST, ID: 20572848

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 30-day free trial to view this Author Comment or ask the Experts your question.

 
 
Loading Advertisement...
20081112-EE-VQP-44 / EE_QW_2_20070628