I am trying to call an SP within another SP, but the result being returned
from the inner SP is Null, whereas I should expect it to return a 1 or 0...
Here is the code:
ALTER Proc Returns_RecordPostClean
@.SerialNo int,
@.LineID int,
@.PostCleanDate smalldatetime,
@.PostCleanResult varchar(10),
@.PostCleanText varchar(50),
@.Exothermed tinyint,
@.Turned bit = 0
As
Set NoCount On
Begin Tran
Update Cleans
Set PostCleanDate = @.PostCleanDate,
PostCleanResult = @.PostCleanResult,
PostCleanText = @.PostCleanText,
Exothermed = @.Exothermed,
Turned = @.Turned
Where SerialNo = @.SerialNo
and LineID = @.LineID
Declare @.Status varchar(30)
Declare @.PFE bit
Declare @.Result int
Select @.PFE=O.PFE
From Orders O
inner join OrderDetail D on D.OrderID = O.OrderID
where D.LineID = @.LineID
If @.PostCleanResult = 'Passed'
If @.PFE = 1
Set @.Status = 'PFE Credit Note'
Else
Begin
Set @.Status = 'Complete'
Exec @.Result=Common_UpdateOrderStatus @.LineID
<========= calling 2nd SP
End
Else
Set @.Status = 'Quarantined'
Update OrderDetail
Set Status = @.Status
Where LineID = @.LineID
If @.@.RowCount = 1 and @.Result = 1
Begin
Select @.Result as Success
Commit tran
End
Else
Begin
Select @.Result as Success
rollback tran
End
Set NoCount Off
If I call the 2nd SP outside this SP, it works fine. But for reasons I can't
be bothered going in to, it really needs to be call from within this SP.
This is actually the first time I've asked one SP to call another, so I'm
not sure if I'm missing something..
Thanks
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]Can you post the code of the second sp?
AMB
"CJM" wrote:
> I am trying to call an SP within another SP, but the result being returned
> from the inner SP is Null, whereas I should expect it to return a 1 or 0..
.
> Here is the code:
> ALTER Proc Returns_RecordPostClean
> @.SerialNo int,
> @.LineID int,
> @.PostCleanDate smalldatetime,
> @.PostCleanResult varchar(10),
> @.PostCleanText varchar(50),
> @.Exothermed tinyint,
> @.Turned bit = 0
> As
> Set NoCount On
> Begin Tran
> Update Cleans
> Set PostCleanDate = @.PostCleanDate,
> PostCleanResult = @.PostCleanResult,
> PostCleanText = @.PostCleanText,
> Exothermed = @.Exothermed,
> Turned = @.Turned
> Where SerialNo = @.SerialNo
> and LineID = @.LineID
> Declare @.Status varchar(30)
> Declare @.PFE bit
> Declare @.Result int
> Select @.PFE=O.PFE
> From Orders O
> inner join OrderDetail D on D.OrderID = O.OrderID
> where D.LineID = @.LineID
> If @.PostCleanResult = 'Passed'
> If @.PFE = 1
> Set @.Status = 'PFE Credit Note'
> Else
> Begin
> Set @.Status = 'Complete'
> Exec @.Result=Common_UpdateOrderStatus @.LineID
> <========= calling 2nd SP
> End
> Else
> Set @.Status = 'Quarantined'
>
> Update OrderDetail
> Set Status = @.Status
> Where LineID = @.LineID
> If @.@.RowCount = 1 and @.Result = 1
> Begin
> Select @.Result as Success
> Commit tran
> End
> Else
> Begin
> Select @.Result as Success
> rollback tran
> End
> Set NoCount Off
>
> If I call the 2nd SP outside this SP, it works fine. But for reasons I can
't
> be bothered going in to, it really needs to be call from within this SP.
> This is actually the first time I've asked one SP to call another, so I'm
> not sure if I'm missing something..
> Thanks
>
> --
> cjmnews04@.REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>
>|||Sure, here it is:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER Proc Common_UpdateOrderStatus
@.LineID int
As
Declare @.Total int, @.Complete int, @.OrderID int
Select
@.OrderID=(Select O.OrderID
from Orders O
inner join OrderDetail D on D.OrderID = O.OrderID
where D.LineID = @.LineID),
@.Total=(Select Count(*)
from OrderDetail D
inner join Orders O on O.OrderID = D.OrderID
inner join OrderDetail D2 on D2.OrderID = D.OrderID
where D2.LineID = @.LineID),
@.Complete=(Select Count(*)
from OrderDetail D
inner join Orders O on O.OrderID = D.OrderID
inner join OrderDetail D2 on D2.OrderID = D.OrderID
where D2.LineID = @.LineID
and D.Status = 'Complete' or D.Status = 'Cancelled')
If @.Complete = @.Total
Begin
/* all lines complete or cancelled */
Update Orders
Set Status = 'Complete'
Where OrderID = @.OrderID
If @.@.RowCount = 1
Select 1 as Success
Else
Select 0 as Success
End
Else
Select 2 As Success
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:5021F829-D752-4C84-B154-ACF772380731@.microsoft.com...
> Can you post the code of the second sp?
>
> AMB
>|||CJM wrote:
> Sure, here it is:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> ALTER Proc Common_UpdateOrderStatus
> @.LineID int
> As
> Declare @.Total int, @.Complete int, @.OrderID int
> Select
> @.OrderID=(Select O.OrderID
> from Orders O
> inner join OrderDetail D on D.OrderID = O.OrderID
> where D.LineID = @.LineID),
> @.Total=(Select Count(*)
> from OrderDetail D
> inner join Orders O on O.OrderID = D.OrderID
> inner join OrderDetail D2 on D2.OrderID = D.OrderID
> where D2.LineID = @.LineID),
> @.Complete=(Select Count(*)
> from OrderDetail D
> inner join Orders O on O.OrderID = D.OrderID
> inner join OrderDetail D2 on D2.OrderID = D.OrderID
> where D2.LineID = @.LineID
> and D.Status = 'Complete' or D.Status = 'Cancelled')
> If @.Complete = @.Total
> Begin
> /* all lines complete or cancelled */
> Update Orders
> Set Status = 'Complete'
> Where OrderID = @.OrderID
> If @.@.RowCount = 1
> Select 1 as Success
> Else
> Select 0 as Success
> End
> Else
> Select 2 As Success
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:5021F829-D752-4C84-B154-ACF772380731@.microsoft.com...
You need to use a RETURN statement to return a... return value. Do not
use a result set as it will play havok with your application if not
managed carefully.
Instead of:
Select 0
Use:
Return 0
David Gugick
Imceda Software
www.imceda.com|||CJM,
A sp returns a value using the RETURN keyword, mainly to indicate success or
fail (0 - success, another int value diff from 0 to indicate fail).
If you want to return a value with a specific meaning, is better to use an
output parameter.
ALTER Proc Common_UpdateOrderStatus
@.LineID int,
@.op int output
As
...
if @.@.error <> 0 return 1
If @.Complete = @.Total
Begin
/* all lines complete or cancelled */
Update Orders
Set Status = 'Complete'
Where OrderID = @.OrderID
If @.@.RowCount = 1
set @.op = 1
Else
set @.op = 0
End
Else
set @.op = 2
return 0
go
declare @.i int
declare @.Result int
Exec @.Result=Common_UpdateOrderStatus @.LineID, @.i int output
print @.Result
print @.i
AMB
"CJM" wrote:
> Sure, here it is:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> ALTER Proc Common_UpdateOrderStatus
> @.LineID int
> As
> Declare @.Total int, @.Complete int, @.OrderID int
> Select
> @.OrderID=(Select O.OrderID
> from Orders O
> inner join OrderDetail D on D.OrderID = O.OrderID
> where D.LineID = @.LineID),
> @.Total=(Select Count(*)
> from OrderDetail D
> inner join Orders O on O.OrderID = D.OrderID
> inner join OrderDetail D2 on D2.OrderID = D.OrderID
> where D2.LineID = @.LineID),
> @.Complete=(Select Count(*)
> from OrderDetail D
> inner join Orders O on O.OrderID = D.OrderID
> inner join OrderDetail D2 on D2.OrderID = D.OrderID
> where D2.LineID = @.LineID
> and D.Status = 'Complete' or D.Status = 'Cancelled')
> If @.Complete = @.Total
> Begin
> /* all lines complete or cancelled */
> Update Orders
> Set Status = 'Complete'
> Where OrderID = @.OrderID
> If @.@.RowCount = 1
> Select 1 as Success
> Else
> Select 0 as Success
> End
> Else
> Select 2 As Success
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:5021F829-D752-4C84-B154-ACF772380731@.microsoft.com...
>
Showing posts with label null. Show all posts
Showing posts with label null. Show all posts
Thursday, March 29, 2012
Wednesday, March 21, 2012
ExecuteScalar returns null
I am using the following C# code and T-SQL to get result object from a
SQL Server database. When my application runs, the ExecuteScalar
returns "10/24/2006 2:00:00 PM" if inserting a duplicated record. It
returns null for all other conditions. Does anyone know why? Does
anyone know how to get the output value? Thanks.
There was no way for you to know it (except maybe by browsing through some
of the previous questions in this newsgroup before posting yours - always a
recommended practice) , but this is a classic ADO newsgroup. ADO.Net bears
very little resemblance to classic ADO so, while you may be lucky enough to
find a dotnet-knowledgeable person here who can answer your question, you
can eliminate the luck factor by posting your question to a group where
those dotnet-knowledgeable people hang out. I suggest
microsoft.public.dotnet.framework.adonet.
This is extremely misguided. Not only is it grossly inefficient, retrieving
all the records that meet the requirements, it is also preventing you from
retrieving your output value. SQL Server does not send RETURN and OUTPUT
parameter values to the client until all resultsets are sent. The above
select statement is creating a resultset that wwill be sent to the client.
I'm not sure what the point of the above concatenation is: are you trying to
present a datetime in a particular format? If so, are you aware that
You do realize that because of the intervening statements, the @.@.rowcount
function returns a different value than was returned the first time you used
it ... ? @.@.error and @.@.rowcount are only useful if used immediately after
the statement you wish to test. New statements cause these functions to
return new values.
Bob Barrows
SQL Server database. When my application runs, the ExecuteScalar
returns "10/24/2006 2:00:00 PM" if inserting a duplicated record. It
returns null for all other conditions. Does anyone know why? Does
anyone know how to get the output value? Thanks.
-- C# --
aryParams = {'10/24/2006 2pm', '10/26/2006 3pm', 2821077, null};
object oRtnObject = null;
StoredProcCommandWrapper =
myDb.GetStoredProcCommandWrapper(strStoredProcName ,aryParams);
oRtnObject = myDb.ExecuteScalar(StoredProcCommandWrapper);
-- T-SQL --
ALTER PROCEDURE [dbo].[procmyCalendarInsert]
@.pBegin datetime,
@.pEnd datetime,
@.pUserId int,
@.pOutput varchar(200) output
AS
BEGIN
SET NOCOUNT ON;
select * from myCalendar
where beginTime >= @.pBegin and endTime <= @.pEnd and userId = @.pUserId
if @.@.rowcount <0
begin
print 'Path 1'
set @.pOutput = 'Duplicated reservation'
select @.pOutput as 'Result'
return -1
end
else
begin
print 'Path 2'
-- check if upperlimit (2) is reached
select rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30)))
,count(rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30))))
from myCalendar
group by rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30)))
having count(rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30)))) =2
and (rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30))) =
rtrim(cast(@.pBegin as varchar(20)))+ ', ' + rtrim(cast(@.pEnd as
varchar(20))))
-- If the @.@.rowcount is not equal to 0 then
-- at the time between @.pBegin and @.pEnd the maximum count of 2 is
reached
if @.@.rowcount <0
begin
print 'Path 3'
set @.pOutput = '2 reservations are already taken for the hours'
select @.pOutput as 'Result'
return -1
end
else
begin
print 'Path 4'
--safe to insert
insert dbo.myCalendar(beginTime, endTime,userId)
values (@.pBegin, @.pEnd, @.pUserId)
if @.@.error = 0
begin
print 'Path 4:1 @.@.error=' + cast(@.@.error as varchar(1))
print 'Path 4:1 @.@.rowcount=' + cast(@.@.rowcount as varchar(1))
set @.pOutput = 'Reservation succeeded'
select @.pOutput as 'Result'
return 0
end
else
begin
print 'Path 4:2 @.@.rowcount=' + cast(@.@.rowcount as varchar(1))
set @.pOutput = 'Failed to make reservation'
select @.pOutput as 'Result'
return -1
end
end
end
ENDjs wrote:
Quote:
Originally Posted by
I am using the following C# code
There was no way for you to know it (except maybe by browsing through some
of the previous questions in this newsgroup before posting yours - always a
recommended practice) , but this is a classic ADO newsgroup. ADO.Net bears
very little resemblance to classic ADO so, while you may be lucky enough to
find a dotnet-knowledgeable person here who can answer your question, you
can eliminate the luck factor by posting your question to a group where
those dotnet-knowledgeable people hang out. I suggest
microsoft.public.dotnet.framework.adonet.
But read on:
Quote:
Originally Posted by
and T-SQL to get result object from a
SQL Server database. When my application runs, the ExecuteScalar
returns "10/24/2006 2:00:00 PM" if inserting a duplicated record. It
returns null for all other conditions. Does anyone know why? Does
anyone know how to get the output value? Thanks.
>
-- C# --
aryParams = {'10/24/2006 2pm', '10/26/2006 3pm', 2821077, null};
object oRtnObject = null;
StoredProcCommandWrapper =
myDb.GetStoredProcCommandWrapper(strStoredProcName ,aryParams);
oRtnObject = myDb.ExecuteScalar(StoredProcCommandWrapper);
>
-- T-SQL --
ALTER PROCEDURE [dbo].[procmyCalendarInsert]
@.pBegin datetime,
@.pEnd datetime,
@.pUserId int,
@.pOutput varchar(200) output
AS
BEGIN
SET NOCOUNT ON;
>
select * from myCalendar
where beginTime >= @.pBegin and endTime <= @.pEnd and userId = @.pUserId
>
if @.@.rowcount <0
This is extremely misguided. Not only is it grossly inefficient, retrieving
all the records that meet the requirements, it is also preventing you from
retrieving your output value. SQL Server does not send RETURN and OUTPUT
parameter values to the client until all resultsets are sent. The above
select statement is creating a resultset that wwill be sent to the client.
If you want to verify if records exist, use IF EXISTS, as in
IF EXISTS (select * from myCalendar
where beginTime >= @.pBegin and endTime <= @.pEnd and userId = @.pUserId)
This is more efficient because it does not retrieve a resultset, it only
verifies that the records meeting therequirements exist. If you really want
a count of the records that meet the requirements (which does not seem to be
te case here) you should use:
declare @.cnt int
Set @.cnt= (select count(*) from myCalendar
where beginTime >= @.pBegin and endTime <= @.pEnd and userId = @.pUserId)
Because the result is assigned to a variable, no resultset is created that
needs to be sent to the client.
Quote:
Originally Posted by
begin
print 'Path 1'
set @.pOutput = 'Duplicated reservation'
select @.pOutput as 'Result'
return -1
end
else
begin
print 'Path 2'
-- check if upperlimit (2) is reached
select rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30)))
,count(rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30))))
from myCalendar
group by rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30)))
having count(rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30)))) =2
and (rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30))) =
rtrim(cast(@.pBegin as varchar(20)))+ ', ' + rtrim(cast(@.pEnd as
varchar(20))))
I'm not sure what the point of the above concatenation is: are you trying to
present a datetime in a particular format? If so, are you aware that
Quote:
Originally Posted by
>
-- If the @.@.rowcount is not equal to 0 then
-- at the time between @.pBegin and @.pEnd the maximum count of 2 is
reached
You do realize that because of the intervening statements, the @.@.rowcount
function returns a different value than was returned the first time you used
it ... ? @.@.error and @.@.rowcount are only useful if used immediately after
the statement you wish to test. New statements cause these functions to
return new values.
Anyways, you already determined above that the records exist. Why bother
checking again?
Quote:
Originally Posted by
>
if @.@.rowcount <0
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
ExecuteScalar returns 0 (null) but INSERT is successful.
I have code that has worked just fine for some time, and now all of the sudden I am having an issue. I have a simple INSERT statement built and then make the following call:
RecordID = cmd.ExecuteScalar
I have never had a problem with this before. The RecordID of the newly inserted record is returned into the RecordID Integer varibale. All of the sudden, the varibale has a value of 0 (null I assume is being returned), but yet the INSERT worked just fine. I can check the table in SQL and it is populated with no issues.
No exception is thrown of any type or anything. Does anybody know what may be happening?
Can you give us a peek at the procedure?
|||Need to check the SP, you should have the Select statement which returns the newly inserted value..
|||Yes, thanks for the quick response. I must of had a brain fart or something because I did not have theSELECT @.@.IDENTITY
at the end of the SQL. I am not sure how it was working before, but I know I needed to add this at the end of the INSERT statement.
The only thing I can figure is that I copy and paste so much code to use as a template, that when I wrote this SQL from scratch I forgot to add it, and never really paid attention when I was copying the INSERT statements before.
Thank you!
|||You may have a lingering problem; you probably should be using SCOPE_IDENTITY() function instead of @.@.IDENTITY.
You might want to give a look at a couple of previous posts related to SCOPE_IDENTITY() versus @.@.IDENTITY here and here.
|||Its better to use the SCOPE_IDENTITY function..
Select Scope_Identity()
|||Sounds good to me; I actually saw that as well in the MSDN example.
Could you tell me why it is better (performance, etc.)?
Thanks,
|||@.@.IDENTITY hold the global value (across the scope)
Scope_Identity hold the current Scope value.
When there is a concurrency user try to insert the value on your table, the @.@.identity has the very latest data, which may not be inserted by your current scope, but the scope_identity always have the value whichever your current scope inserted.
ExecuteScalar returns 0 (null) but INSERT is successful.
I have code that has worked just fine for some time, and now all of the sudden I am having an issue. I have a simple INSERT statement built and then make the following call:
RecordID = cmd.ExecuteScalar
I have never had a problem with this before. The RecordID of the newly inserted record is returned into the RecordID Integer varibale. All of the sudden, the varibale has a value of 0 (null I assume is being returned), but yet the INSERT worked just fine. I can check the table in SQL and it is populated with no issues.
No exception is thrown of any type or anything. Does anybody know what may be happening?
Can you give us a peek at the procedure?
|||Need to check the SP, you should have the Select statement which returns the newly inserted value..
|||Yes, thanks for the quick response. I must of had a brain fart or something because I did not have theSELECT @.@.IDENTITY
at the end of the SQL. I am not sure how it was working before, but I know I needed to add this at the end of the INSERT statement.
The only thing I can figure is that I copy and paste so much code to use as a template, that when I wrote this SQL from scratch I forgot to add it, and never really paid attention when I was copying the INSERT statements before.
Thank you!
|||You may have a lingering problem; you probably should be using SCOPE_IDENTITY() function instead of @.@.IDENTITY.
You might want to give a look at a couple of previous posts related to SCOPE_IDENTITY() versus @.@.IDENTITY here and here.
|||Its better to use the SCOPE_IDENTITY function..
Select Scope_Identity()
|||Sounds good to me; I actually saw that as well in the MSDN example.
Could you tell me why it is better (performance, etc.)?
Thanks,
|||@.@.IDENTITY hold the global value (across the scope)
Scope_Identity hold the current Scope value.
When there is a concurrency user try to insert the value on your table, the @.@.identity has the very latest data, which may not be inserted by your current scope, but the scope_identity always have the value whichever your current scope inserted.
Friday, March 9, 2012
Execute SQL TaSk
I am trying to run a Direct Input SQL query to SELECT MAX value of ColA. This query when I run on Query Window runs fine - means it is NOT a NULL. I get a max value.
When I run this query on a SSIS package outputing to a variable - I get the error. -
[Execute SQL Task] Error: An error occurred while assigning a value to variable "MAXROWKEYID": "The type of the value being assigned to variable "User::MAXROWKEYID" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ".
I need this output to be used for the subsequent steps which follows this.
I am using Value Type as Double. This was the default when I migrated this DTS package from SQL 2000. I use the proper Single Row for the Result Set and for outputing the Result Set, I use the correct Variable Name.
Help will be greatly appreciated...
Thanks.
SAM.
Obvious first question, what is the type of ColA?
I had some trouble with populating Double variabls recently, I couldn't load it from a field of type decimal. I had to cast the value as a float first.
-Jamie
|||ColA is my ROWKEYID - it is bigint datatype.
-Sam
|||Try making the variable type "Int32" or "Int64".
-Jamie
|||Kept the value type as Double and when I tried to make this as
SELECT CAST(MAX(ROWKEYID) as FLOAT) from Table on SSIS package, it runs fine, successfully, but the value does not change. I mean, when I run on this Query window -the value is different.
But when I try -
SELECT Max(ROWKEYID) FROM Table and making the variable type as Int32 or Int64, the step fails.
-Sam
|||After debugging the task, I found that the value returned by the MAX function on a bigint column is of type String. Thats why your task failed when you used variables of type Int32, Int64 or Double. I see the same behavior when MAX function is called on a decimal column. However, MAX function on a int column returns Int32 type and float column returns a Double type.
Subscribe to:
Posts (Atom)