Stored Proc not returning my Output
-
Hi, I've written a stored procedure which should return a specific value and has an additional OUTPUT parameter. Here goes the code:
CREATE PROCEDURE dbo.Update_AddresseInfo ( @UserID int, @DateModified DateTime, @ElementID int, @Street varchar(50), @City varchar(50), @ZIP varchar(50), @NewDateModified DateTime OUTPUT ) AS declare @RetVal int declare @DateNow DateTime set @DateNow = GetDate() -- do some stuff UPDATE dbo.AdresseInfo SET Street = @Street, ZIP = @ZIP, City = @City, DateModified = @DateNow WHERE ID = @ElementID if @@ERROR = 0 begin set @NewDateModified = @DateNow set @Retval = 0 end else begin set @Retval = 2 end -- log this event exec LogEvent @UserID, 1, 1 select @Retval RETURNUnfortunately the NewDateModified does not contain what I expect, eventhough I've created a SqlParameter object with the correct Direction. I guess the problem is somewhere in my SQL code. Any help is greatly appreceated. MatthiasIf eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs. (Lounge/David Wulff)
-
Hi, I've written a stored procedure which should return a specific value and has an additional OUTPUT parameter. Here goes the code:
CREATE PROCEDURE dbo.Update_AddresseInfo ( @UserID int, @DateModified DateTime, @ElementID int, @Street varchar(50), @City varchar(50), @ZIP varchar(50), @NewDateModified DateTime OUTPUT ) AS declare @RetVal int declare @DateNow DateTime set @DateNow = GetDate() -- do some stuff UPDATE dbo.AdresseInfo SET Street = @Street, ZIP = @ZIP, City = @City, DateModified = @DateNow WHERE ID = @ElementID if @@ERROR = 0 begin set @NewDateModified = @DateNow set @Retval = 0 end else begin set @Retval = 2 end -- log this event exec LogEvent @UserID, 1, 1 select @Retval RETURNUnfortunately the NewDateModified does not contain what I expect, eventhough I've created a SqlParameter object with the correct Direction. I guess the problem is somewhere in my SQL code. Any help is greatly appreceated. MatthiasIf eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs. (Lounge/David Wulff)
-
Hi Michael, thanks for your response. Yes, I've thought about this as well. But even putting it outside the IF doesn't yield correct results. I'm posting an extract of the code which is actually executing the SP here. Maybe I'm just blind...
int nResult = 0; DateTime dtResult = new DateTime(0); SqlCommand cmd = new SqlCommand(ProcName, Open()); try { cmd.CommandType = System.Data.CommandType.StoredProcedure; -- do stuff and and all params... cmd.Parameters.Add(new SqlParameter("ElementID", ElementID)); cmd.Parameters.Add(new SqlParameter("DateModified", DateModified)); // add the DateTime output param SqlParameter prmNewDtModif = new SqlParameter("NewDateModified", dtResult); prmNewDtModif.Direction = System.Data.ParameterDirection.Output; cmd.Parameters.Add(prmNewDtModif); nResult = (Int32) cmd.ExecuteScalar(); } // catch stuff omitted...I've just put the stuff in which seems to be relevant. Variable names are selfexplanatory I guess. Any ideas? MatthiasIf eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs. (Lounge/David Wulff)
-
Hi, I've written a stored procedure which should return a specific value and has an additional OUTPUT parameter. Here goes the code:
CREATE PROCEDURE dbo.Update_AddresseInfo ( @UserID int, @DateModified DateTime, @ElementID int, @Street varchar(50), @City varchar(50), @ZIP varchar(50), @NewDateModified DateTime OUTPUT ) AS declare @RetVal int declare @DateNow DateTime set @DateNow = GetDate() -- do some stuff UPDATE dbo.AdresseInfo SET Street = @Street, ZIP = @ZIP, City = @City, DateModified = @DateNow WHERE ID = @ElementID if @@ERROR = 0 begin set @NewDateModified = @DateNow set @Retval = 0 end else begin set @Retval = 2 end -- log this event exec LogEvent @UserID, 1, 1 select @Retval RETURNUnfortunately the NewDateModified does not contain what I expect, eventhough I've created a SqlParameter object with the correct Direction. I guess the problem is somewhere in my SQL code. Any help is greatly appreceated. MatthiasIf eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs. (Lounge/David Wulff)
-
Hi Michael, thanks for your response. Yes, I've thought about this as well. But even putting it outside the IF doesn't yield correct results. I'm posting an extract of the code which is actually executing the SP here. Maybe I'm just blind...
int nResult = 0; DateTime dtResult = new DateTime(0); SqlCommand cmd = new SqlCommand(ProcName, Open()); try { cmd.CommandType = System.Data.CommandType.StoredProcedure; -- do stuff and and all params... cmd.Parameters.Add(new SqlParameter("ElementID", ElementID)); cmd.Parameters.Add(new SqlParameter("DateModified", DateModified)); // add the DateTime output param SqlParameter prmNewDtModif = new SqlParameter("NewDateModified", dtResult); prmNewDtModif.Direction = System.Data.ParameterDirection.Output; cmd.Parameters.Add(prmNewDtModif); nResult = (Int32) cmd.ExecuteScalar(); } // catch stuff omitted...I've just put the stuff in which seems to be relevant. Variable names are selfexplanatory I guess. Any ideas? MatthiasIf eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs. (Lounge/David Wulff)
-
Hi DTrent! Thanks for your reply. Your sole proposal has answered my question ("out of the command object..."??? :)) I assumed, that the value gets automatically assigned once the SqlCommand.ExecuteScalar() call returns. I didn't know that I have to retrieve the SqlParameter.Value property and assign it then to the variable which I passed to that parameter. Seems kind of weird to me, in the end I'm asking why am I assigning the variable in the first place? For those interested, here the code:
int nResult = 0; DateTime dtResult = new DateTime(0); SqlCommand cmd = new SqlCommand(ProcName, Open()); try { cmd.CommandType = System.Data.CommandType.StoredProcedure; -- do stuff and and all params... cmd.Parameters.Add(new SqlParameter("ElementID", ElementID)); cmd.Parameters.Add(new SqlParameter("DateModified", DateModified)); // add the DateTime output param SqlParameter prmNewDtModif = new SqlParameter("NewDateModified", dtResult); prmNewDtModif.Direction = System.Data.ParameterDirection.Output; cmd.Parameters.Add(prmNewDtModif); nResult = (Int32) cmd.ExecuteScalar(); } // catch stuff omitted... // To make the code work, uncomment the following line. // Omitting the following line, was actually the problem // but I thought, that the value (cause it got passed to the SqlParameter Ctor) // gets assigned automatically. // dtResult = (DateTime) prmNewDtModif.Value; // start using the dtResult variable if (dtResult < dtSomething) { // go do stuff... }Thanks for your help! MatthiasIf eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs. (Lounge/David Wulff)