Re: Retrieving SPROC return value using SPT

From: Fred Taylor (ftaylor_at_mvps.org!REMOVE)
Date: 02/10/05


Date: Wed, 9 Feb 2005 22:32:01 -0700

You cannot use the parenthesis in calling the stored procedure. Don't ask
me why, but the simple act of placing the parenthesis in the command cause
it to not return values.

If you do the syntax in exactly the manner I specified, it does indeed work.

nRetVal = 0
nStatus = SQLEXEC(nSQLHandle,[EXEC YourStoredProcedure ?@nRetVal])

Your parameter must be declared as OUTPUT inside your stored procedure.

-- 
Fred
Microsoft Visual FoxPro MVP
"Newbie" <admin@indohomeware.com> wrote in message 
news:O0u4G2tDFHA.1396@tk2msftngp13.phx.gbl...
> Hi Fred,
>
> I tried that one, too. Still can't worked. Like you suggested, I included 
> a
> var that supposed to receive the RetVal from the sproc (?@nRetVal).
> It turned out to ask me to enter the value for nRetVal. If I inialized the
> nRetVal to a value (say, =0). It still not updated after return from 
> sproc.
> And, BTW, have I mentioned before that I'm not using any output parameter 
> in
> any of my sprocs? (maybe it explains why the code keeps asking me of the
> param value?)
>
> Thks & brgds,
>
> andy
>
> "Fred Taylor" <ftaylor@mvps.org!REMOVE> wrote in message
> news:uov7XlYDFHA.3928@TK2MSFTNGP15.phx.gbl...
>> Take a look at this:
>>
>> http://fox.wikis.com/wc.dll?Wiki~SQLPass-Through~VFP
>>
>> <snip>
>>
>> Contributor FredTaylor
>> I've found that you actually CAN get return values from SPT on a stored
>> procedure, though the syntax is a bit odd:
>>
>> cPassVal = "testing"
>> nRetVal = 0
>>  nResult = SQLEXEC(nConn,[EXEC sp_yourproc ?cPassVal,?@nRetVal])
>> You can't use the "output" keyword, nor can you put the parameters inside
>> parenthesis like a regular call. The "@" seems to be sufficient to tell
> SQL
>> that it's an output parameter.
>>
>> FWIW, I couldn't get the escape sequence call command to work no matter
> what
>> I tried.
>>
>> </snip>
>>
>> --
>> Fred
>> Microsoft Visual FoxPro MVP
>>
>>
>> "Newbie" <admin@indohomeware.com> wrote in message
>> news:%233$aX4SDFHA.2600@TK2MSFTNGP09.phx.gbl...
>> > Thanks guys,
>> >
>> > Actually, it just not for returning an @@IDENTITY only.
>> > In one of my appl, I have several sprocs that returns a value based on 
>> > a
>> > condition.
>> > In my ASP.NET appl, it's easy for me to retrieve it with
>> > sqldataadapter.selectcommand.parameter("@return_value").value
>> > So, i just wondering how to retrieve such return value with an SPT?
>> > All i know about return value from a sproc is just -1,0,1,2. Now 
>> > there's
> a
>> > need to get not only the status of the sproc exec, but also to get the
>> > retval out of my sproc.
>> > Anyway, I tried Jackson's code but it not worked.
>> > Any suggestions?
>> >
>> > Thks in advance,
>> > andy
>> >
>> >
>> > "Fred Taylor" <ftaylor@mvps.org!REMOVE> wrote in message
>> > news:OCSyg6xCFHA.3540@TK2MSFTNGP14.phx.gbl...
>> >> Could that present a problem in a multiuser environment, Anders?  An
>> >> awful
>> >> lot could happen between your two SQLEXEC calls.  I've always found 
>> >> you
>> > have
>> >> to used a stored procedure and have it return the @@Identity when it
> does
>> >> the insert.
>> >>
>> >> --
>> >> Fred
>> >> Microsoft Visual FoxPro MVP
>> >>
>> >>
>> >> "Anders Altberg" <x_pragma@telia.com> wrote in message
>> >> news:OT8p$XsCFHA.4072@TK2MSFTNGP10.phx.gbl...
>> >> > h=SQLCONNECT('TempDB')
>> >> > ?SQLEXEC(h, "CREATE TABLE #Temp (id1 Int IDENTITY, name1 Char(20)")
>> >> > ? SQLEXEC(h,[SELECT @@Identity AS idvalue], [Q1])
>> >> > ? Q1.idvalue && should return NULL
>> >> > ?SQLEXEC(h,[INSERT INTO #temp (name1) VALUES ('AAAA')])
>> >> > ? Q1.idvalue && should now return 1
>> >> >
>> >> > -Anders
>> >> >
>> >> > "Newbie" <admin@indohomeware.com> wrote in message
>> >> > news:O9WiV1nCFHA.328@tk2msftngp13.phx.gbl...
>> >> >> Hi,
>> >> >>
>> >> >> How can I retrieve a return value from a MSSQL server sproc using
> SPT?
>> >> >> For instance, returning an @@IDENTITY.
>> >> >>
>> >> >> Thks & brgds,
>> >> >> andy
>> >> >>
>> >> >>
>> >> >>
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
> 


Relevant Pages

  • Re: SqlDataAdapter - can it be (re)used for 2 SELECT statements
    ... dataadapter for 2 different calls to the same sproc, ... the stored procedure parameter ... //create our DataAdapter object and use it to fill the dataset object ...
    (microsoft.public.dotnet.framework.aspnet)
  • Im so confused
    ... normally from Query Analyzer rather than calling it from any other code so I ... > system stored procedure sp_grantdbaccess checks that there is not an ... > there is not an enclosing transaction. ... Here's my sproc and the ...
    (microsoft.public.sqlserver.security)
  • Tricky Stored Procedure Problem - for SQL Experts only!
    ... In a web application I need to call a stored procedure that lists ... items in a shopping cart. ... The Sproc is pretty complicated as it needs ... ordItemsRS.open cmdOrderItems,,adOpenStatic,adLockReadOnly ...
    (microsoft.public.sqlserver)
  • Sproc with temp tables and input parameters causing trouble with dynamic recordsets - a tricky one!
    ... In a web application I need to call a stored procedure that lists ... items in a shopping cart. ... The Sproc is pretty complicated as it needs ... ordItemsRS.open cmdOrderItems,,adOpenStatic,adLockReadOnly ...
    (microsoft.public.sqlserver.programming)