Passing output parameter from procedure to variable

I have a stored procedure that runs a query and the result of the query is a
varchar. I would like to use an output parameter to get the value and then
pass that value into a variable to use elsewhere. Is this possible?
ThanksSure.
Example:
use northwind
go
create procedure usp_get_companyname
@.customerid nchar(5),
@.companyname nvarchar(40) output
as
set nocount on
set @.companyname = (select companyname from customers where customerid =
@.customerid)
return @.@.error
go
declare @.cn nvarchar(40)
execute usp_get_companyname @.customerid = 'alfki', @.companyname = @.cn output
print @.cn
go
drop procedure usp_get_companyname
go
AMB
"Andy" wrote:

> I have a stored procedure that runs a query and the result of the query is
a
> varchar. I would like to use an output parameter to get the value and the
n
> pass that value into a variable to use elsewhere. Is this possible?
> Thanks|||Something like this?
use pubs
go
create proc first
@.au_lname varchar(50),
@.au_id varchar(11) OUTPUT
as
SELECT @.au_id = au_id from authors where au_lname = @.au_lname
RETURN (0)
GO
create proc second
@.au_id varchar(11)
as
select * from titleauthor where au_id = @.au_id
RETURN (0)
GO
declare @.lname varchar(50), @.id varchar(11)
set @.lname = 'white'
exec first @.lname, @.id output
select @.id
exec second @.id
go
declare @.lname varchar(50), @.id varchar(11)
set @.lname = 'green'
exec first @.lname, @.id output
select @.id
exec second @.id
go
drop proc first
drop proc second
Keith
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:0289E9D3-8AB3-481D-8869-92E6CFD12FF2@.microsoft.com...
> I have a stored procedure that runs a query and the result of the query is
a
> varchar. I would like to use an output parameter to get the value and
then
> pass that value into a variable to use elsewhere. Is this possible?
> Thanks

Passing Out Parameter to Sybase Stored Proc from RS

I am defining the Output parameter from Sybase Stored Proc.
How can I define the OUT Parm in RS and display that parm result in Header?
Any help is appreciated.I don't think you can. Instead have your last statement be a select
statement.
BTW, did you solve the problem of what provider to use. Did you go with ODBC
or were you able to stick with OleDB.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sujay" <Sujay@.discussions.microsoft.com> wrote in message
news:CEDAB7E2-590C-4B74-B937-8FDFA73433A8@.microsoft.com...
> I am defining the Output parameter from Sybase Stored Proc.
> How can I define the OUT Parm in RS and display that parm result in
Header?
> Any help is appreciated.|||Bruce,
We are sticking with OLE-DB.
The problem using OLE-DB was , I cannot pass char or varchar datatype as a
i/p parameter to a stored proc.
I got the alternate solution to this problem:
I can define the report as command type of "Text" and call the proc in the
following way:
="Proc_Name "+chr(34)+parameters!strParm.value+chr(34) + "," +
chr(34)+parameters!dtParm.value+chr(34)
"Bruce L-C [MVP]" wrote:
> I don't think you can. Instead have your last statement be a select
> statement.
> BTW, did you solve the problem of what provider to use. Did you go with ODBC
> or were you able to stick with OleDB.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Sujay" <Sujay@.discussions.microsoft.com> wrote in message
> news:CEDAB7E2-590C-4B74-B937-8FDFA73433A8@.microsoft.com...
> > I am defining the Output parameter from Sybase Stored Proc.
> > How can I define the OUT Parm in RS and display that parm result in
> Header?
> >
> > Any help is appreciated.
>
>|||Sujay,
I am trying to pass a string parameter to ASE OLE DB Provider for Sybase.
Where exactly do you use the syntax you mention below? The SQL pane in the
report designer does not appear to allow anything other than the string that
will be passed to the OLE DB provider (even quotes).
Thanks,
John
"Sujay" wrote:
> Bruce,
> We are sticking with OLE-DB.
> The problem using OLE-DB was , I cannot pass char or varchar datatype as a
> i/p parameter to a stored proc.
> I got the alternate solution to this problem:
> I can define the report as command type of "Text" and call the proc in the
> following way:
> ="Proc_Name "+chr(34)+parameters!strParm.value+chr(34) + "," +
> chr(34)+parameters!dtParm.value+chr(34)
>
> "Bruce L-C [MVP]" wrote:
> > I don't think you can. Instead have your last statement be a select
> > statement.
> >
> > BTW, did you solve the problem of what provider to use. Did you go with ODBC
> > or were you able to stick with OleDB.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Sujay" <Sujay@.discussions.microsoft.com> wrote in message
> > news:CEDAB7E2-590C-4B74-B937-8FDFA73433A8@.microsoft.com...
> > > I am defining the Output parameter from Sybase Stored Proc.
> > > How can I define the OUT Parm in RS and display that parm result in
> > Header?
> > >
> > > Any help is appreciated.
> >
> >
> >|||You put this in the generic query designer. Your query can be an expression.
Personally I would use the & instead of a + sign. What he is doing is
enclosing any strings in single quote marks.
Also note that parameters are case sensitive.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John" <John@.discussions.microsoft.com> wrote in message
news:6D8F5670-454D-47E0-97A6-AAD3E695599A@.microsoft.com...
> Sujay,
> I am trying to pass a string parameter to ASE OLE DB Provider for Sybase.
> Where exactly do you use the syntax you mention below? The SQL pane in
the
> report designer does not appear to allow anything other than the string
that
> will be passed to the OLE DB provider (even quotes).
> Thanks,
> John
> "Sujay" wrote:
> > Bruce,
> >
> > We are sticking with OLE-DB.
> > The problem using OLE-DB was , I cannot pass char or varchar datatype as
a
> > i/p parameter to a stored proc.
> > I got the alternate solution to this problem:
> > I can define the report as command type of "Text" and call the proc in
the
> > following way:
> > ="Proc_Name "+chr(34)+parameters!strParm.value+chr(34) + "," +
> > chr(34)+parameters!dtParm.value+chr(34)
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > I don't think you can. Instead have your last statement be a select
> > > statement.
> > >
> > > BTW, did you solve the problem of what provider to use. Did you go
with ODBC
> > > or were you able to stick with OleDB.
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Sujay" <Sujay@.discussions.microsoft.com> wrote in message
> > > news:CEDAB7E2-590C-4B74-B937-8FDFA73433A8@.microsoft.com...
> > > > I am defining the Output parameter from Sybase Stored Proc.
> > > > How can I define the OUT Parm in RS and display that parm result in
> > > Header?
> > > >
> > > > Any help is appreciated.
> > >
> > >
> > >sql

Passing Cursor as SP output parameter

Hi,
I have a SP that uses a temporary table and cursor to give all employee IDs
for a passwed managerid at all level of a hierarchy using a structuretable
that has parentId and childId. The SP runs successfully, but when I am
trying to use the output cursor I get the following error:
Calling SQL:
DECLARE @.childCursor cursor
EXEC dco.getPersonIdChild 'EMGR', 'STR', 6852, @.childCursor OUT
FETCH @.childCursor
CLOSE @.childCursor
DEALLOCATE @.ChildCursor
output:
The variable '@.childCursor' does not currently have a cursor allocated to it
.
Server: Msg 16950, Level 16, State 2, Line 4
The variable '@.childCursor' does not currently have a cursor allocated to it
.
Server: Msg 16950, Level 16, State 2, Line 5
The variable '@.childCursor' does not currently have a cursor allocated to it
.
Any help on this problem will be greatly appreciated.
SangDid you define the childCuror variable as an OUT parm in the proc?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sang" <Sang@.discussions.microsoft.com> wrote in message
news:C7D4D59F-3E84-42BC-B132-67DAF449AB6B@.microsoft.com...
> Hi,
> I have a SP that uses a temporary table and cursor to give all employee ID
s
> for a passwed managerid at all level of a hierarchy using a structuretable
> that has parentId and childId. The SP runs successfully, but when I am
> trying to use the output cursor I get the following error:
> Calling SQL:
> DECLARE @.childCursor cursor
> EXEC dco.getPersonIdChild 'EMGR', 'STR', 6852, @.childCursor OUT
> FETCH @.childCursor
> CLOSE @.childCursor
> DEALLOCATE @.ChildCursor
> output:
> The variable '@.childCursor' does not currently have a cursor allocated to
it.
> Server: Msg 16950, Level 16, State 2, Line 4
> The variable '@.childCursor' does not currently have a cursor allocated to
it.
> Server: Msg 16950, Level 16, State 2, Line 5
> The variable '@.childCursor' does not currently have a cursor allocated to
it.
> Any help on this problem will be greatly appreciated.
> Sang
>|||Yes, please see the following:
ALTER PROCEDURE dco.getPersonIdChild (
@.Code CodeExtraLong,
@.CodeTypeCode CHAR(3),
@.PersonIdParent UniqueId,
@.childCursor CURSOR VARYING OUT
)
AS
"Tibor Karaszi" wrote:

> Did you define the childCuror variable as an OUT parm in the proc?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Sang" <Sang@.discussions.microsoft.com> wrote in message
> news:C7D4D59F-3E84-42BC-B132-67DAF449AB6B@.microsoft.com...
>|||I think you need to give us more to go on. Below work fine for me:
USE pubs
GO
CREATE PROC p @.c cursor varying OUT
AS
SET @.c = CURSOR LOCAL FOR SELECT au_lname FROM authors
OPEN @.c
GO
DECLARE @.oc cursor
EXEC p @.c = @.oc OUTPUT
FETCH NEXT FROM @.oc
GO
DROP PROC p
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sang" <Sang@.discussions.microsoft.com> wrote in message
news:1E16242A-4AD9-4F61-8781-13F833E85369@.microsoft.com...
> Yes, please see the following:
> ALTER PROCEDURE dco.getPersonIdChild (
> @.Code CodeExtraLong,
> @.CodeTypeCode CHAR(3),
> @.PersonIdParent UniqueId,
> @.childCursor CURSOR VARYING OUT
> )
> AS
> "Tibor Karaszi" wrote:
>|||The SP is the following:
ALTER PROCEDURE dco.getPersonIdChild (
@.Code CodeExtraLong,
@.CodeTypeCode CHAR(3),
@.PersonIdParent UniqueId,
@.childCursor CURSOR VARYING OUT
)
AS
BEGIN
DECLARE
@.Level smallint,
@.PersonIdParentC UniqueID
-- Let the intial set of children for the passed parentId
SET @.Level = 1
IF OBJECT_ID ('#tempTable') IS NOT NULL
Drop Table #tempTable
CREATE TABLE #TempTable (PersonIdChild UniqueId, Depth smallint, Checked
bit)
INSERT INTO #TempTable
SELECT PersonIdChild, @.Level AS "Depth", 0 AS "Checked"
FROM PersonStructures
WHERE Code = @.Code
AND CodeTypeCode = @.CodeTypeCode
AND PersonIdParent = @.PersonIdParent
AND EndDate is NULL
WHILE ((SELECT count(*) from #TempTable WHERE Checked = 0 ) > 0)
BEGIN
DECLARE Children CURSOR
FOR
SELECT PersonIdChild
FROM #TempTable
WHERE Depth = @.Level
OPEN Children
FETCH Children into @.PersonIdParentC
WHILE (@.@.FETCH_STATUS =0)
BEGIN
-- PRINT '@.Level is ' + CAST(@.Level AS CHAR)
IF ((SELECT Count(*) from PersonStructures
WHERE Code = @.Code
AND CodeTypeCode = @.CodeTypeCode
AND PersonIdParent = @.PersonIdParentC
AND EndDate is NULL) > 0 )
BEGIN
INSERT INTO #TempTable
SELECT PersonIdChild, @.Level + 1, 0
FROM PersonStructures
WHERE Code = @.Code
AND CodeTypeCode = @.CodeTypeCode
AND PersonIdParent = @.PersonIdParentC
AND EndDate is NULL
END
-- Now Update the checked column for the record just processed
UPDATE #TempTable
SET Checked = 1
WHERE PersonIdChild = @.PersonIdParentC
FETCH NEXT FROM Children INTO @.PersonIdParentC
END
CLOSE Children
DEALLOCATE Children
SET @.Level = @.Level + 1
END
DECLARE s CURSOR
LOCAL
FOR SELECT PersonIdChild, Depth
FROM #TempTable
SET @.childCursor = s
OPEN @.childCursor
RETURN (0)
END
"Tibor Karaszi" wrote:

> I think you need to give us more to go on. Below work fine for me:
> USE pubs
> GO
> CREATE PROC p @.c cursor varying OUT
> AS
> SET @.c = CURSOR LOCAL FOR SELECT au_lname FROM authors
> OPEN @.c
> GO
> DECLARE @.oc cursor
> EXEC p @.c = @.oc OUTPUT
> FETCH NEXT FROM @.oc
> GO
> DROP PROC p
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Sang" <Sang@.discussions.microsoft.com> wrote in message
> news:1E16242A-4AD9-4F61-8781-13F833E85369@.microsoft.com...
>sql

Passing back more than 1 output parameter to VBA code

I have a stored procedure which has 2 output parameters, namely @.RecCnt and
@.RetCode. In the stored procedure, I am using the SET statements to pass the
data back. I am calling the stored procedure from my VBA code. I use
objCmd.Execute options:=adExecuteNoRecords.
I am able to retrieve only the @.RetCode value and not the @.RecCnt value.
Could any of you tell me what is wrong?
VBA Code:
--
Set objCmd = New ADODB.Command
With objCmd
.CommandText = "sp_addback_selectcount_CorpAcctCtr"
.NAME = "sp_addback_selectcount_CorpAcctCtr"
.CommandType = adCmdStoredProc
'Create parameter list for objCmd
.Parameters.Append .CreateParameter("Corp", adVarChar, adParamInput,
3, vstrCorp)
.Parameters.Append .CreateParameter("Acct", adVarChar, adParamInput,
5, vstrAcct)
.Parameters.Append .CreateParameter("Ctr", adVarChar, adParamInput, 5,
vstrCtr)
.Parameters.Append .CreateParameter("RecCnt", adInteger,
adParamOutput, 4)
.Parameters.Append .CreateParameter("RetCode", adBoolean,
adParamOutput, 1)
.ActiveConnection = objCon
.Execute options:=adExecuteNoRecords
End With
If objCmd.Parameters("RetCode").Value Then
rlngRecCnt = objCmd.Parameters("RecCnt").Value
fnGetAddbackCnt = True
End If
--Store Procedure
CREATE PROCEDURE sp_lotusdata_load_from_recon
@.Corp nvarchar(3),
@.Acct nvarchar(5),
@.Ctr nvarchar(5),
@.RecsAffected int OUTPUT,
@.RetCode bit OUTPUT
AS
-- local variables
DECLARE @.ErrorNum smallint
DECLARE @.RecCnt smallint
-- initialization
SET @.RetCode = 0
SET @.ErrorNum = 0
-- logic
SELECT @.RecCnt = COUNT(*)
FROM RECON
WHERE CORP = @.Corp
AND ACCT = @.Acct
AND CTR = @.Ctr
IF @.RecCnt > 0
BEGIN
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'lotusdata'
AND type = 'U')
BEGIN
DROP TABLE LOTUSDATA
SELECT RECON.*
INTO LotusData
FROM RECON
WHERE CORP = @.Corp
AND ACCT = @.Acct
AND CTR = @.Ctr
END
END
SELECT @.ErrorNum = @.@.ERROR
IF @.ErrorNum = 0
BEGIN
SET @.RecsAffected = @.RecCnt
SET @.RetCode = 1
END
GOSoooorrryyy...Goofed up the code and stored procedure...
Here is the correct one:
VBA Code:
......
.....
Set objCmd = New ADODB.Command
With objCmd
.CommandText = "sp_lotusdata_load_from_recon"
.NAME = "sp_lotusdata_load_from_recon"
.CommandType = adCmdStoredProc
'Create parameter list for oCmd
.Parameters.Append .CreateParameter("Acct", adVarChar, adParamInput,
Len(vstrAcct), vstrAcct)
.Parameters.Append .CreateParameter("Corp", adVarChar, adParamInput,
Len(vstrCorp), vstrCorp)
.Parameters.Append .CreateParameter("Ctr", adVarChar, adParamInput,
Len(vstrCtr), vstrCtr)
.Parameters.Append .CreateParameter("RecsAffected", adInteger,
adParamOutput, 4)
.Parameters.Append .CreateParameter("RetCode", adBoolean,
adParamOutput, 1)
.ActiveConnection = objCon
'-- execute the proc
.Execute options:=adExecuteNoRecords
'-- return success if stored proc is successful
If .Parameters("RetCode").Value = True Then
rintRecsAffected = .Parameters("RecsAffected")
fnLoadLotusData = True
End If
....
...
Stored Procedure Code:
--
CREATE PROCEDURE sp_lotusdata_load_from_recon
@.Corp nvarchar(3),
@.Acct nvarchar(5),
@.Ctr nvarchar(5),
@.RecsAffected int OUTPUT,
@.RetCode bit OUTPUT
AS
-- local variables
DECLARE @.ErrorNum smallint
DECLARE @.RecCnt smallint
-- initialization
SET @.RetCode = 0
SET @.ErrorNum = 0
-- logic
SELECT @.RecCnt = COUNT(*)
FROM RECON
WHERE CORP = @.Corp
AND ACCT = @.Acct
AND CTR = @.Ctr
IF @.RecCnt > 0
BEGIN
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'lotusdata'
AND type = 'U')
BEGIN
DROP TABLE LOTUSDATA
SELECT RECON.*
INTO LotusData
FROM RECON
WHERE CORP = @.Corp
AND ACCT = @.Acct
AND CTR = @.Ctr
END
END
SELECT @.ErrorNum = @.@.ERROR
IF @.ErrorNum = 0
BEGIN
SET @.RecsAffected = @.RecCnt
SET @.RetCode = 1
END
GO
Sorry for the error.
Regards,
Paddy
"Paddy" wrote:

> I have a stored procedure which has 2 output parameters, namely @.RecCnt an
d
> @.RetCode. In the stored procedure, I am using the SET statements to pass t
he
> data back. I am calling the stored procedure from my VBA code. I use
> objCmd.Execute options:=adExecuteNoRecords.
> I am able to retrieve only the @.RetCode value and not the @.RecCnt value.
> Could any of you tell me what is wrong?
> VBA Code:
> --
> Set objCmd = New ADODB.Command
> With objCmd
> .CommandText = "sp_addback_selectcount_CorpAcctCtr"
> .NAME = "sp_addback_selectcount_CorpAcctCtr"
> .CommandType = adCmdStoredProc
> 'Create parameter list for objCmd
> .Parameters.Append .CreateParameter("Corp", adVarChar, adParamInput,
> 3, vstrCorp)
> .Parameters.Append .CreateParameter("Acct", adVarChar, adParamInput,
> 5, vstrAcct)
> .Parameters.Append .CreateParameter("Ctr", adVarChar, adParamInput,
5,
> vstrCtr)
> .Parameters.Append .CreateParameter("RecCnt", adInteger,
> adParamOutput, 4)
> .Parameters.Append .CreateParameter("RetCode", adBoolean,
> adParamOutput, 1)
> .ActiveConnection = objCon
> .Execute options:=adExecuteNoRecords
> End With
>
> If objCmd.Parameters("RetCode").Value Then
> rlngRecCnt = objCmd.Parameters("RecCnt").Value
> fnGetAddbackCnt = True
> End If
> --Store Procedure
> CREATE PROCEDURE sp_lotusdata_load_from_recon
> @.Corp nvarchar(3),
> @.Acct nvarchar(5),
> @.Ctr nvarchar(5),
> @.RecsAffected int OUTPUT,
> @.RetCode bit OUTPUT
> AS
> -- local variables
> DECLARE @.ErrorNum smallint
> DECLARE @.RecCnt smallint
> -- initialization
> SET @.RetCode = 0
> SET @.ErrorNum = 0
> -- logic
> SELECT @.RecCnt = COUNT(*)
> FROM RECON
> WHERE CORP = @.Corp
> AND ACCT = @.Acct
> AND CTR = @.Ctr
> IF @.RecCnt > 0
> BEGIN
> IF EXISTS (SELECT name
> FROM sysobjects
> WHERE name = N'lotusdata'
> AND type = 'U')
> BEGIN
> DROP TABLE LOTUSDATA
> SELECT RECON.*
> INTO LotusData
> FROM RECON
> WHERE CORP = @.Corp
> AND ACCT = @.Acct
> AND CTR = @.Ctr
> END
> END
> SELECT @.ErrorNum = @.@.ERROR
> IF @.ErrorNum = 0
> BEGIN
> SET @.RecsAffected = @.RecCnt
> SET @.RetCode = 1
> END
> GO|||Paddy (Paddy@.discussions.microsoft.com) writes:
> I have a stored procedure which has 2 output parameters, namely @.RecCnt
> and @.RetCode. In the stored procedure, I am using the SET statements to
> pass the data back. I am calling the stored procedure from my VBA code.
> I use objCmd.Execute options:=adExecuteNoRecords.
> I am able to retrieve only the @.RetCode value and not the @.RecCnt value.
> Could any of you tell me what is wrong?
How do you conclude that you can not retriev the RecCnt value?
I don't think it should really matter, but it is a good idea to align
the names in the parameters collection with the actual parameters names.
Thus, the names should be @.Corp, @.Acct, @.Ctr, @.RowsAffected and
@.RetCode. Furthermore there is one parameter missing. That is, each
stored procedure has a return value, which in ADO you declare as a the
first parameter and as adParamReturnValue. Then again, I think it's find
to not include that paraemeter.

> .CommandText = "sp_addback_selectcount_CorpAcctCtr"
The sp_ prefix is reserved for system objects, and SQL Server first
looks in the master database for these. Don't use it, in your own code.

> .Parameters.Append .CreateParameter("RecCnt", adInteger,
> adParamOutput, 4)
> .Parameters.Append .CreateParameter("RetCode", adBoolean,
> adParamOutput, 1)
I think adParamInputOutput are more appropriate as that is what they
are.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The above code always return RecsAffected value as 0. When I run the sp in
Query Analyser it shows the correct data.
Please help.
Regards,
Paddy
"Paddy" wrote:
> Soooorrryyy...Goofed up the code and stored procedure...
> Here is the correct one:
> VBA Code:
> ......
> .....
> Set objCmd = New ADODB.Command
> With objCmd
> .CommandText = "sp_lotusdata_load_from_recon"
> .NAME = "sp_lotusdata_load_from_recon"
> .CommandType = adCmdStoredProc
> 'Create parameter list for oCmd
> .Parameters.Append .CreateParameter("Acct", adVarChar, adParamInput,
> Len(vstrAcct), vstrAcct)
> .Parameters.Append .CreateParameter("Corp", adVarChar, adParamInput,
> Len(vstrCorp), vstrCorp)
> .Parameters.Append .CreateParameter("Ctr", adVarChar, adParamInput,
> Len(vstrCtr), vstrCtr)
> .Parameters.Append .CreateParameter("RecsAffected", adInteger,
> adParamOutput, 4)
> .Parameters.Append .CreateParameter("RetCode", adBoolean,
> adParamOutput, 1)
> .ActiveConnection = objCon
> '-- execute the proc
> .Execute options:=adExecuteNoRecords
> '-- return success if stored proc is successful
> If .Parameters("RetCode").Value = True Then
> rintRecsAffected = .Parameters("RecsAffected")
> fnLoadLotusData = True
> End If
> ....
> ...
> Stored Procedure Code:
> --
> CREATE PROCEDURE sp_lotusdata_load_from_recon
> @.Corp nvarchar(3),
> @.Acct nvarchar(5),
> @.Ctr nvarchar(5),
> @.RecsAffected int OUTPUT,
> @.RetCode bit OUTPUT
> AS
> -- local variables
> DECLARE @.ErrorNum smallint
> DECLARE @.RecCnt smallint
> -- initialization
> SET @.RetCode = 0
> SET @.ErrorNum = 0
> -- logic
> SELECT @.RecCnt = COUNT(*)
> FROM RECON
> WHERE CORP = @.Corp
> AND ACCT = @.Acct
> AND CTR = @.Ctr
> IF @.RecCnt > 0
> BEGIN
> IF EXISTS (SELECT name
> FROM sysobjects
> WHERE name = N'lotusdata'
> AND type = 'U')
> BEGIN
> DROP TABLE LOTUSDATA
> SELECT RECON.*
> INTO LotusData
> FROM RECON
> WHERE CORP = @.Corp
> AND ACCT = @.Acct
> AND CTR = @.Ctr
> END
> END
> SELECT @.ErrorNum = @.@.ERROR
> IF @.ErrorNum = 0
> BEGIN
> SET @.RecsAffected = @.RecCnt
> SET @.RetCode = 1
> END
> GO
> Sorry for the error.
> Regards,
> Paddy
> "Paddy" wrote:
>|||Hi, Erland,
Please read my 2nd and 3rd message. I copied the wrong code in the message.
I posted the code which has problem in my second message.
I debugged the VBA code and know it is returning 0.
I knew about sp_ prefix, but used it for easily distinguish stored
procedures. I know there is some performance penalties.
Anyway, is there anything wrong in the way I am setting the output parameter
in the stored procedure?
Thanks.
Paddy
"Erland Sommarskog" wrote:

> Paddy (Paddy@.discussions.microsoft.com) writes:
> How do you conclude that you can not retriev the RecCnt value?
> I don't think it should really matter, but it is a good idea to align
> the names in the parameters collection with the actual parameters names.
> Thus, the names should be @.Corp, @.Acct, @.Ctr, @.RowsAffected and
> @.RetCode. Furthermore there is one parameter missing. That is, each
> stored procedure has a return value, which in ADO you declare as a the
> first parameter and as adParamReturnValue. Then again, I think it's find
> to not include that paraemeter.
>
> The sp_ prefix is reserved for system objects, and SQL Server first
> looks in the master database for these. Don't use it, in your own code.
>
> I think adParamInputOutput are more appropriate as that is what they
> are.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Paddy (Paddy@.discussions.microsoft.com) writes:
> The above code always return RecsAffected value as 0. When I run the sp in
> Query Analyser it shows the correct data.
But RetCode is still True then?
When you run from QA, I suspect that you run as as or dbo, but how
do run the application? Does that account have CREATE TABLE permissions?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx