Hi All.

Maybe someone in here could help on this too....

Uusally I can return a value from a stored procedure without any problem.
Today I ran into something I cannot figure out.

Basically....what I am doing is a couple of inserts or updates depending on
what is being passed.

So in the storedproc tag, I am passing the necessary values along with 1
output param. I was using an INOUT param, but I figured I would play it
safe since it wasn't working.

So...in the stored procedure, I do some conditions inside transaction
statements...

I don't have the code with me right now as I am home, but I figure if I can
give you the general idea, you may know what the problem is.

So I have something like this...

BEGIN TRAN
IF team_ID is null
BEGIN
IF target_ID > 0
BEGIN
--- INSERT processing
SET @OutPutVariable = Scope_Identity()
END

IF target_ID < 0
BEGIN
--- INSERT processing
SET @OutPutVariable = Scope_Identity()
END
END

IF team_ID is not null
BEGIN
-- UPDATE Processing
END

COMMIT TRAN

SELECT @OutPutVariable

If I run this procedure through enterprise, i get what I need....the value
of the last inserted record. When I do it through CF, I always get 0 OR
nothing at all.

If I do a SELECT 100, I get a return value of 100 of course, so it seems
like it's out of scope.

Any ideas?

Member (mcleanap@REMOVETHISSPAMrogers.com) writes:
> If I run this procedure through enterprise, i get what I need....the value
> of the last inserted record. When I do it through CF, I always get 0 OR
> nothing at all.

The standard recipe is to add a SET NOCOUNT ON to the procedure.

However, you should investigate using an OUTPUT variable instead. There is
a big performance difference between getting a single value in a output
variable than in a result set. That is not a difference you see for an
occasional call, but if you were calling the procedure 1000 times in
direct succession, you would certainly see benefit.

Unfortunately, since I don't know Cold Fusion, I cannot show how to
handle output variables in Cold Fusion.
--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

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

Yes...I am using an OUTPUT variable.

I think the SET NOCOUNT might work. Someone else suggested this as well.

I will paste my code, maybe you will notice something that could be causing
the null or 0 return

-------- Store Procedure -------------

CREATE PROCEDURE dbo.sp_REC_MOD_TEAMS

@NewTeamID numeric OUTPUT,
@team_id numeric = null,
@target_id numeric,
@team_name varchar(50)

AS

BEGIN TRAN

IF @team_id is null
BEGIN
IF @target_id > 0
BEGIN

insert into REC_Teams
(team_name)
value
(@team_name)

@NewTeamID = SCOPE_IDENTITY()

Insert into REC_Teams_Rec_LK
(target_id, team_id)
values
(@target_id, @NewTeamID)

IF @@ERROR THEN ROLLBACK TRAN
END

IF @target_id < 0
BEGIN
insert into REC_Teams
(team_name)
value
(@team_name)

@NewTeamID = SCOPE_IDENTITY()

Insert into REC_Teams_Cos_LK
(target_id, team_id)
values
(@target_id, @NewTeamID)

IF @@ERROR THEN ROLLBACK TRAN
END

END

IF @team_id is not null
BEGIN
Update REC_Teams
SET team_name = @team_name
WHERE team_id = @team_id

SET @NewTeamID = @team_id
END

COMMIT TRAN

-- Return NewTeamID
SELECT @NewTeamID

----- If I do a SELECT 1 or anything else here, I can get that value
returned to me in coldfusion. It's like it loses the @NewTeamID somewhere.
Does it try to get the identity of the other sql statements even though they
do not actually run since they are in 1 condition of the IF statement?



---------- Call from Coldfusion --------------------

<cfstoredproc procedure = "sp_REC_MOD_TEAMS" dataSource =
"#Application.DSN#">
<cfprocresult name = results>
<cfprocparam type = "OUT" CFSQLType = CF_SQL_NUMERIC variable =
"NewTeamID" dbVarName = "@NewTeamID">
<cfprocparam type = "IN" CFSQLType = CF_SQL_NUMERIC variable =
"team_ID" dbVarName = "@team_ID" >
<cfprocparam type = "IN" CFSQLType = CF_SQL_NUMERIC variable =
"target_ID" dbVarName = "@target_ID" >
<cfprocparam type = "IN" CFSQLType = CF_SQL_VARCHAR variable =
"team_name" dbVarName = "@team_na" >
</cfstoredproc>

----- Check output ----------

<cfoutput>#results#</cfoutput>
<cfoutput>#NewTeamID#</cfoutput>



"Erland Sommarskog" <sommar@algonet.se> wrote in message
news:Xns94206A05652Yazorman@127.0.0.1...
> Member (mcleanap@REMOVETHISSPAMrogers.com) writes:
> > If I run this procedure through enterprise, i get what I need....the
value
> > of the last inserted record. When I do it through CF, I always get 0 OR
> > nothing at all.
>
> The standard recipe is to add a SET NOCOUNT ON to the procedure.
>
> However, you should investigate using an OUTPUT variable instead. There is
> a big performance difference between getting a single value in a output
> variable than in a result set. That is not a difference you see for an
> occasional call, but if you were calling the procedure 1000 times in
> direct succession, you would certainly see benefit.
>
> Unfortunately, since I don't know Cold Fusion, I cannot show how to
> handle output variables in Cold Fusion.
> --
> Erland Sommarskog, SQL Server MVP, sommar@algonet.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp

Member (mcleanap@REMOVETHISSPAMrogers.com) writes:
> CREATE PROCEDURE dbo.sp_REC_MOD_TEAMS

Don't call your procedures sp_something. The sp_ prefix is reserved for
system procedures, and SQL Server will first look for this in master.

> IF @@ERROR THEN ROLLBACK TRAN

This is not even correct T-SQL syntax. You compare @@error to something,
and there is no THEN in IF statements in T-SQL.

> -- Return NewTeamID
> SELECT @NewTeamID

You have @NewTeamID as an OUTPUT parameter, so this SELECT is
superfluous. This SELECT statement produces a result set.

In T-SQL you could call the procedure this way:

DECLARE @newid numeric
EXEC sp_REC_MOD_TEAMS @newid OUTPUT, NULL, 12, 'Önnestad BI'
SELECT @newid

> Does it try to get the identity of the other sql statements even though
> they do not actually run since they are in 1 condition of the IF
> statement?

It appears that there is something about getting values from output
parameters in Cold Fusion, but as I said I don't know Cold Fusion, so
I cannot assist.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

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

Sorry...I never copied and pasted from what I was doing.

It was supposed to be

IF @@ERROR <> 0 ROLLBACK TRAN

I didn't know that the SELECT produced a result set though. So setting the
output variable anywhere in the SP, would get that value for me?

I'm quite new to stored procedures, so thank you for the tips.


"Erland Sommarskog" <sommar@algonet.se> wrote in message
news:Xns9420718C96AA2Yazorman@127.0.0.1...
> Member (mcleanap@REMOVETHISSPAMrogers.com) writes:
> > CREATE PROCEDURE dbo.sp_REC_MOD_TEAMS
>
> Don't call your procedures sp_something. The sp_ prefix is reserved for
> system procedures, and SQL Server will first look for this in master.
>
> > IF @@ERROR THEN ROLLBACK TRAN
>
> This is not even correct T-SQL syntax. You compare @@error to something,
> and there is no THEN in IF statements in T-SQL.
>
> > -- Return NewTeamID
> > SELECT @NewTeamID
>
> You have @NewTeamID as an OUTPUT parameter, so this SELECT is
> superfluous. This SELECT statement produces a result set.
>
> In T-SQL you could call the procedure this way:
>
> DECLARE @newid numeric
> EXEC sp_REC_MOD_TEAMS @newid OUTPUT, NULL, 12, 'Önnestad BI'
> SELECT @newid
>
> > Does it try to get the identity of the other sql statements even though
> > they do not actually run since they are in 1 condition of the IF
> > statement?
>
> It appears that there is something about getting values from output
> parameters in Cold Fusion, but as I said I don't know Cold Fusion, so
> I cannot assist.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@algonet.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp

What is supposed to happen if @target_id = 0?

Its not catered for