DESPERATE help needed....

Hello all,

I am a total newbie to SQL. I created this sp and then with C++,
called it. My return value was 100 (which it was)

CREATE PROCEDURE sp_StoreIPs
@.IPSource varchar(16),
@.IPTarget varchar(16),
@.TimeDate varchar(20),
@.Name varchar(250)
as
declare @.iReturn int
Set @.iReturn = 100
return @.iReturn
GO

Then when I added a INSERT statement like ...

CREATE PROCEDURE sp_StoreIPs
@.IPSource varchar(16),
@.IPTarget varchar(16),
@.TimeDate varchar(20),
@.Name varchar(250)
as
declare @.iReturn int
Insert into LookUP (IPSource, IPTarget,TimeDate, Name) Values
(@.IPSource,@.IPTarget,@.TimeDate,@.Name)
Set @.iReturn = 100
return @.iReturn
GO

My return value was 0. I am assuming the the INSERT statement is
returning the 0 but how can I get around this?

Thanks
Ralph Krausse
www.consiliumsoft.com
Use the START button? Then you need CSFastRunII...
A new kind of application launcher integrated in the taskbar!
ScreenShot - http://www.consiliumsoft.com/ScreenShot.jpg"Ralph Krausse" wrote:

<snip
> CREATE PROCEDURE sp_StoreIPs
> @.IPSource varchar(16),
> @.IPTarget varchar(16),
> @.TimeDate varchar(20),
> @.Name varchar(250)
> as
> declare @.iReturn int
> Insert into LookUP (IPSource, IPTarget,TimeDate, Name) Values
> (@.IPSource,@.IPTarget,@.TimeDate,@.Name)
> Set @.iReturn = 100
> return @.iReturn
> GO
>
> My return value was 0. I am assuming the the INSERT statement is
> returning the 0 but how can I get around this?

<snip
Ralph,

I think you're half correct: an insert can return a message about records
affected... but I don't know why that would affect the return value unless
the insert failed: are you checking to insure successful execution? My
guess would be that the INSERT is failing.

In any event, to suppress the record count coming back as a message, you can
use SET NOCOUNT ON/OFF:

CREATE PROCEDURE blah
AS

SET NOCOUNT ON

INSERT SomeTable (fld) VALUES ('a')

SET NOCOUNT OFF

RETURN 100
GO

And the NOCOUNT setting can be handy even if this isn't your problem: it's
always nice to eliminate unnecessary network chitchat :)

Craig|||[posted and mailed, please reply in news]

Ralph Krausse (gordingin@.consiliumsoft.com) writes:
> I am a total newbie to SQL. I created this sp and then with C++,
> called it. My return value was 100 (which it was)

And what API did you use to call the procedure from C++?

> CREATE PROCEDURE sp_StoreIPs

Don't use the sp_ prefix for the names of stored procedure. This prefix
is reserved for system procedures, and SQL Server first looks for
procedures with this prefix in the master database.

> Then when I added a INSERT statement like ...
>...
> My return value was 0. I am assuming the the INSERT statement is
> returning the 0 but how can I get around this?

As Craig said, the INSERT statement generates a kind of result set to
inform of the number of rows consumed. If you don't get that result
set, you will not get the return value, nor the value of output parameter,
since these are not availble until all result sets have been consumed.

Since I don't know which API you are using, I cannot say which methods you
should use, but you should always make it a habit to fetch all result set.

SET NOCOUNT ON, which Craig suggested, is a very good idea, if you are
not interested in these record counts, since they cause extra round-trips
to the server. However, result sets may appear of other reasons, for
instance a trigger with a SELECT statement in it. (Which is poor practice,
but accidents happens.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp