|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Return via Return Value or Selectvalue vs a select statement in my Stored Procedure. For example, I have a SP: ************************************************************************************* CREATE PROCEDURE CopyFromTemplate ( @ClientID varChar(20),@PositionIDFrom int,@PositionIDTo int ) As Insert ScreenQuestions (ClientID,PositionID,Version,QuestionUnique,Question,QuestionType,SortOrder, Answers,Weight) Select ClientID,@PositionIDTo,Version,QuestionUnique,Question,QuestionType,SortOrder,Answers,Weight From ScreenQuestionsTemplates Where ClientID = @ClientID and PositionID = @PositionIDFrom Select @@RowCount GO ************************************************************************************ This will return the number of rows affected. I could also do: *************************************************************************************** CREATE PROCEDURE CopyFromTemplate ( @ClientID varChar(20),@PositionIDFrom int,@PositionIDTo int ) As Insert ScreenQuestions (ClientID,PositionID,Version,QuestionUnique,Question,QuestionType,SortOrder, Answers,Weight) Select ClientID,@PositionIDTo,Version,QuestionUnique,Question,QuestionType,SortOrder,Answers,Weight From ScreenQuestionsTemplates Where ClientID = @ClientID and PositionID = @PositionIDFrom Return @@RowCount ****************************************************************************************** Which is better? Thanks, Tom Return at the end of your SP should be reserved for @@error. Standard
practice is to return the success/failure error code as the return value. If you want to retuen a value the other option is to use an OUTPUT parameter as in the following: CREATE PROCEDURE CopyFromTemplate ( @ClientID varChar(20),@PositionIDFrom int,@PositionIDTo int, @RetValue int OUTPUT ) As Insert ScreenQuestions (ClientID,PositionID,Version,QuestionUnique,Question,QuestionType,SortOrder, Answers,Weight) Select ClientID,@PositionIDTo,Version,QuestionUnique,Question,QuestionType,SortOrder,Answers,Weight From ScreenQuestionsTemplates Where ClientID = @ClientID and PositionID = @PositionIDFrom SET @RetValue = @@RowCount RETURN @@error GO You would call this like this: DECLARE @Rows INT EXEC CopyFromTemplate 'A10092', 10, 20, @Rows OUTPUT Hope that helps. "tshad" <tscheider***@ftsolutions.com> wrote in message << snip >>news:u5pBVi5RFHA.2604@TK2MSFTNGP10.phx.gbl... >I was just curious on the pros and cons of returning a value via a return >value vs a select statement in my Stored Procedure. Show quote > Which is better?
Show quote
"Michael C#" <x**@abcdef.com> wrote in message I'm not sure how I would call this in Asp.Net.news:Augae.16611$ZQ1.8234@fe11.lga... > Return at the end of your SP should be reserved for @@error. Standard > practice is to return the success/failure error code as the return value. > If you want to retuen a value the other option is to use an OUTPUT > parameter as in the following: > > CREATE PROCEDURE CopyFromTemplate > ( > @ClientID varChar(20),@PositionIDFrom int,@PositionIDTo int, @RetValue int > OUTPUT > ) As > > Insert ScreenQuestions > (ClientID,PositionID,Version,QuestionUnique,Question,QuestionType,SortOrder, > Answers,Weight) > Select > ClientID,@PositionIDTo,Version,QuestionUnique,Question,QuestionType,SortOrder,Answers,Weight > From ScreenQuestionsTemplates > Where ClientID = @ClientID and PositionID = @PositionIDFrom > > SET @RetValue = @@RowCount > > RETURN @@error > > GO > > You would call this like this: > > DECLARE @Rows INT > EXEC CopyFromTemplate 'A10092', 10, 20, @Rows OUTPUT In asp.net, I would just read what was returned (from the select statement). For a return, I would set up a return parameter. Would "SET @RetValue = @@RowCount" return the value similar to the select where I would would just read it like a record? Thanks, Tom Show quote > > Hope that helps. > > "tshad" <tscheider***@ftsolutions.com> wrote in message > news:u5pBVi5RFHA.2604@TK2MSFTNGP10.phx.gbl... >>I was just curious on the pros and cons of returning a value via a return >>value vs a select statement in my Stored Procedure. > > << snip >> > >> Which is better? > > In ASP.NET you would set this up as a Parameterized Query, the @RetValue
Parameter would be defined as ParameterDirection.Output, all others would be defined as (default) Input. It would look something like this (not tested): ' Assumes you are using the System.Data.SqlClient Dim SqlCon As New SqlConnection("connection-string") Dim SqlCmd As SqlCommand Dim RowCount As Integer SqlCon.Open() Try SqlCmd = New SqlCommand("EXEC dbo.CopyFromTemplate @ClientID, @PositionIDFrom, @PositionIDTo, @RetValue") SqlCmd.Parameters.Add("@ClientID", SqlDbType.VarChar, 20).Value = "A1238" SqlCmd.Parameters.Add("@PositionIDFrom", SqlDbType.Int).Value = 10 SqlCmd.Parameters.Add("@PositionIDTo", SqlDbType.Int).Value = 20 SqlCmd.Parameters.Add("@RetValue", SqlDbType.Int).Direction = ParameterDirection.Output SqlCmd.ExecuteNonQuery() RowCount = Convert.ToInt32(SqlCmd.Parameters("@RetValue").Value) Catch ex As Exception MessageBox.Show(ex.Message) Finally If Not (SqlCmd Is Nothing) Then SqlCmd.Dispose() End If If Not (SqlCon Is Nothing) Then SqlCon.Dispose() End If End Try Show quote "tshad" <tscheider***@ftsolutions.com> wrote in message news:%23nVlfv5RFHA.3296@TK2MSFTNGP15.phx.gbl... > "Michael C#" <x**@abcdef.com> wrote in message > news:Augae.16611$ZQ1.8234@fe11.lga... >> Return at the end of your SP should be reserved for @@error. Standard >> practice is to return the success/failure error code as the return value. >> If you want to retuen a value the other option is to use an OUTPUT >> parameter as in the following: >> >> CREATE PROCEDURE CopyFromTemplate >> ( >> @ClientID varChar(20),@PositionIDFrom int,@PositionIDTo int, @RetValue >> int OUTPUT >> ) As >> >> Insert ScreenQuestions >> (ClientID,PositionID,Version,QuestionUnique,Question,QuestionType,SortOrder, >> Answers,Weight) >> Select >> ClientID,@PositionIDTo,Version,QuestionUnique,Question,QuestionType,SortOrder,Answers,Weight >> From ScreenQuestionsTemplates >> Where ClientID = @ClientID and PositionID = @PositionIDFrom >> >> SET @RetValue = @@RowCount >> >> RETURN @@error >> >> GO >> >> You would call this like this: >> >> DECLARE @Rows INT >> EXEC CopyFromTemplate 'A10092', 10, 20, @Rows OUTPUT > > I'm not sure how I would call this in Asp.Net. > > In asp.net, I would just read what was returned (from the select > statement). For a return, I would set up a return parameter. > > Would "SET @RetValue = @@RowCount" return the value similar to the select > where I would would just read it like a record? > > Thanks, > > Tom >> >> Hope that helps. >> >> "tshad" <tscheider***@ftsolutions.com> wrote in message >> news:u5pBVi5RFHA.2604@TK2MSFTNGP10.phx.gbl... >>>I was just curious on the pros and cons of returning a value via a return >>>value vs a select statement in my Stored Procedure. >> >> << snip >> >> >>> Which is better? >> >> > >
Show quote
"Michael C#" <x**@abcdef.com> wrote in message Thanks, that helps a lot.news:a5hae.2507$RP1.1935@fe10.lga... > In ASP.NET you would set this up as a Parameterized Query, the @RetValue > Parameter would be defined as ParameterDirection.Output, all others would > be defined as (default) Input. It would look something like this (not > tested): > > ' Assumes you are using the System.Data.SqlClient > Dim SqlCon As New SqlConnection("connection-string") > Dim SqlCmd As SqlCommand > Dim RowCount As Integer > SqlCon.Open() > Try > SqlCmd = New SqlCommand("EXEC dbo.CopyFromTemplate @ClientID, > @PositionIDFrom, @PositionIDTo, @RetValue") > SqlCmd.Parameters.Add("@ClientID", SqlDbType.VarChar, 20).Value = > "A1238" > SqlCmd.Parameters.Add("@PositionIDFrom", SqlDbType.Int).Value = 10 > SqlCmd.Parameters.Add("@PositionIDTo", SqlDbType.Int).Value = 20 > SqlCmd.Parameters.Add("@RetValue", SqlDbType.Int).Direction = > ParameterDirection.Output > SqlCmd.ExecuteNonQuery() > RowCount = Convert.ToInt32(SqlCmd.Parameters("@RetValue").Value) > Catch ex As Exception > MessageBox.Show(ex.Message) > Finally > If Not (SqlCmd Is Nothing) Then > SqlCmd.Dispose() > End If > If Not (SqlCon Is Nothing) Then > SqlCon.Dispose() > End If > End Try Tom Show quote > > > "tshad" <tscheider***@ftsolutions.com> wrote in message > news:%23nVlfv5RFHA.3296@TK2MSFTNGP15.phx.gbl... >> "Michael C#" <x**@abcdef.com> wrote in message >> news:Augae.16611$ZQ1.8234@fe11.lga... >>> Return at the end of your SP should be reserved for @@error. Standard >>> practice is to return the success/failure error code as the return >>> value. If you want to retuen a value the other option is to use an >>> OUTPUT parameter as in the following: >>> >>> CREATE PROCEDURE CopyFromTemplate >>> ( >>> @ClientID varChar(20),@PositionIDFrom int,@PositionIDTo int, @RetValue >>> int OUTPUT >>> ) As >>> >>> Insert ScreenQuestions >>> (ClientID,PositionID,Version,QuestionUnique,Question,QuestionType,SortOrder, >>> Answers,Weight) >>> Select >>> ClientID,@PositionIDTo,Version,QuestionUnique,Question,QuestionType,SortOrder,Answers,Weight >>> From ScreenQuestionsTemplates >>> Where ClientID = @ClientID and PositionID = @PositionIDFrom >>> >>> SET @RetValue = @@RowCount >>> >>> RETURN @@error >>> >>> GO >>> >>> You would call this like this: >>> >>> DECLARE @Rows INT >>> EXEC CopyFromTemplate 'A10092', 10, 20, @Rows OUTPUT >> >> I'm not sure how I would call this in Asp.Net. >> >> In asp.net, I would just read what was returned (from the select >> statement). For a return, I would set up a return parameter. >> >> Would "SET @RetValue = @@RowCount" return the value similar to the select >> where I would would just read it like a record? >> >> Thanks, >> >> Tom >>> >>> Hope that helps. >>> >>> "tshad" <tscheider***@ftsolutions.com> wrote in message >>> news:u5pBVi5RFHA.2604@TK2MSFTNGP10.phx.gbl... >>>>I was just curious on the pros and cons of returning a value via a >>>>return value vs a select statement in my Stored Procedure. >>> >>> << snip >> >>> >>>> Which is better? >>> >>> >> >> > > This is an old post, but I was curious on how you would get the Error value
that was sent from this Stored Procedure? It shows how to get the Return Value, but not the Error (Return @@error). Thanks, Tom Show quote "tshad" <tscheider***@ftsolutions.com> wrote in message news:ORt9no6RFHA.3560@TK2MSFTNGP14.phx.gbl... > "Michael C#" <x**@abcdef.com> wrote in message > news:a5hae.2507$RP1.1935@fe10.lga... >> In ASP.NET you would set this up as a Parameterized Query, the @RetValue >> Parameter would be defined as ParameterDirection.Output, all others would >> be defined as (default) Input. It would look something like this (not >> tested): >> >> ' Assumes you are using the System.Data.SqlClient >> Dim SqlCon As New SqlConnection("connection-string") >> Dim SqlCmd As SqlCommand >> Dim RowCount As Integer >> SqlCon.Open() >> Try >> SqlCmd = New SqlCommand("EXEC dbo.CopyFromTemplate @ClientID, >> @PositionIDFrom, @PositionIDTo, @RetValue") >> SqlCmd.Parameters.Add("@ClientID", SqlDbType.VarChar, 20).Value = >> "A1238" >> SqlCmd.Parameters.Add("@PositionIDFrom", SqlDbType.Int).Value = 10 >> SqlCmd.Parameters.Add("@PositionIDTo", SqlDbType.Int).Value = 20 >> SqlCmd.Parameters.Add("@RetValue", SqlDbType.Int).Direction = >> ParameterDirection.Output >> SqlCmd.ExecuteNonQuery() >> RowCount = Convert.ToInt32(SqlCmd.Parameters("@RetValue").Value) >> Catch ex As Exception >> MessageBox.Show(ex.Message) >> Finally >> If Not (SqlCmd Is Nothing) Then >> SqlCmd.Dispose() >> End If >> If Not (SqlCon Is Nothing) Then >> SqlCon.Dispose() >> End If >> End Try > > Thanks, that helps a lot. > > Tom >> >> >> "tshad" <tscheider***@ftsolutions.com> wrote in message >> news:%23nVlfv5RFHA.3296@TK2MSFTNGP15.phx.gbl... >>> "Michael C#" <x**@abcdef.com> wrote in message >>> news:Augae.16611$ZQ1.8234@fe11.lga... >>>> Return at the end of your SP should be reserved for @@error. Standard >>>> practice is to return the success/failure error code as the return >>>> value. If you want to retuen a value the other option is to use an >>>> OUTPUT parameter as in the following: >>>> >>>> CREATE PROCEDURE CopyFromTemplate >>>> ( >>>> @ClientID varChar(20),@PositionIDFrom int,@PositionIDTo int, @RetValue >>>> int OUTPUT >>>> ) As >>>> >>>> Insert ScreenQuestions >>>> (ClientID,PositionID,Version,QuestionUnique,Question,QuestionType,SortOrder, >>>> Answers,Weight) >>>> Select >>>> ClientID,@PositionIDTo,Version,QuestionUnique,Question,QuestionType,SortOrder,Answers,Weight >>>> From ScreenQuestionsTemplates >>>> Where ClientID = @ClientID and PositionID = @PositionIDFrom >>>> >>>> SET @RetValue = @@RowCount >>>> >>>> RETURN @@error >>>> >>>> GO >>>> >>>> You would call this like this: >>>> >>>> DECLARE @Rows INT >>>> EXEC CopyFromTemplate 'A10092', 10, 20, @Rows OUTPUT >>> >>> I'm not sure how I would call this in Asp.Net. >>> >>> In asp.net, I would just read what was returned (from the select >>> statement). For a return, I would set up a return parameter. >>> >>> Would "SET @RetValue = @@RowCount" return the value similar to the >>> select where I would would just read it like a record? >>> >>> Thanks, >>> >>> Tom >>>> >>>> Hope that helps. >>>> >>>> "tshad" <tscheider***@ftsolutions.com> wrote in message >>>> news:u5pBVi5RFHA.2604@TK2MSFTNGP10.phx.gbl... >>>>>I was just curious on the pros and cons of returning a value via a >>>>>return value vs a select statement in my Stored Procedure. >>>> >>>> << snip >> >>>> >>>>> Which is better? >>>> >>>> >>> >>> >> >> > > You would change this line:
Catch ex As Exception To this: Catch ex As SqlException Then you could retrieve the error number like this: Dim ErrorNumber As Integer = ex.Number SqlException.Number corresponds directly to the master.dbo.sysmessages table. Show quote "tshad" <tscheider***@ftsolutions.com> wrote in message news:%23iMZelyWFHA.3676@TK2MSFTNGP10.phx.gbl... > This is an old post, but I was curious on how you would get the Error > value that was sent from this Stored Procedure? > > It shows how to get the Return Value, but not the Error (Return @@error). > > Thanks, > > Tom > "tshad" <tscheider***@ftsolutions.com> wrote in message > news:ORt9no6RFHA.3560@TK2MSFTNGP14.phx.gbl... >> "Michael C#" <x**@abcdef.com> wrote in message >> news:a5hae.2507$RP1.1935@fe10.lga... >>> In ASP.NET you would set this up as a Parameterized Query, the @RetValue >>> Parameter would be defined as ParameterDirection.Output, all others >>> would be defined as (default) Input. It would look something like this >>> (not tested): >>> >>> ' Assumes you are using the System.Data.SqlClient >>> Dim SqlCon As New SqlConnection("connection-string") >>> Dim SqlCmd As SqlCommand >>> Dim RowCount As Integer >>> SqlCon.Open() >>> Try >>> SqlCmd = New SqlCommand("EXEC dbo.CopyFromTemplate @ClientID, >>> @PositionIDFrom, @PositionIDTo, @RetValue") >>> SqlCmd.Parameters.Add("@ClientID", SqlDbType.VarChar, 20).Value = >>> "A1238" >>> SqlCmd.Parameters.Add("@PositionIDFrom", SqlDbType.Int).Value = 10 >>> SqlCmd.Parameters.Add("@PositionIDTo", SqlDbType.Int).Value = 20 >>> SqlCmd.Parameters.Add("@RetValue", SqlDbType.Int).Direction = >>> ParameterDirection.Output >>> SqlCmd.ExecuteNonQuery() >>> RowCount = Convert.ToInt32(SqlCmd.Parameters("@RetValue").Value) >>> Catch ex As Exception >>> MessageBox.Show(ex.Message) >>> Finally >>> If Not (SqlCmd Is Nothing) Then >>> SqlCmd.Dispose() >>> End If >>> If Not (SqlCon Is Nothing) Then >>> SqlCon.Dispose() >>> End If >>> End Try >> >> Thanks, that helps a lot. >> >> Tom >>> >>> >>> "tshad" <tscheider***@ftsolutions.com> wrote in message >>> news:%23nVlfv5RFHA.3296@TK2MSFTNGP15.phx.gbl... >>>> "Michael C#" <x**@abcdef.com> wrote in message >>>> news:Augae.16611$ZQ1.8234@fe11.lga... >>>>> Return at the end of your SP should be reserved for @@error. Standard >>>>> practice is to return the success/failure error code as the return >>>>> value. If you want to retuen a value the other option is to use an >>>>> OUTPUT parameter as in the following: >>>>> >>>>> CREATE PROCEDURE CopyFromTemplate >>>>> ( >>>>> @ClientID varChar(20),@PositionIDFrom int,@PositionIDTo int, @RetValue >>>>> int OUTPUT >>>>> ) As >>>>> >>>>> Insert ScreenQuestions >>>>> (ClientID,PositionID,Version,QuestionUnique,Question,QuestionType,SortOrder, >>>>> Answers,Weight) >>>>> Select >>>>> ClientID,@PositionIDTo,Version,QuestionUnique,Question,QuestionType,SortOrder,Answers,Weight >>>>> From ScreenQuestionsTemplates >>>>> Where ClientID = @ClientID and PositionID = @PositionIDFrom >>>>> >>>>> SET @RetValue = @@RowCount >>>>> >>>>> RETURN @@error >>>>> >>>>> GO >>>>> >>>>> You would call this like this: >>>>> >>>>> DECLARE @Rows INT >>>>> EXEC CopyFromTemplate 'A10092', 10, 20, @Rows OUTPUT >>>> >>>> I'm not sure how I would call this in Asp.Net. >>>> >>>> In asp.net, I would just read what was returned (from the select >>>> statement). For a return, I would set up a return parameter. >>>> >>>> Would "SET @RetValue = @@RowCount" return the value similar to the >>>> select where I would would just read it like a record? >>>> >>>> Thanks, >>>> >>>> Tom >>>>> >>>>> Hope that helps. >>>>> >>>>> "tshad" <tscheider***@ftsolutions.com> wrote in message >>>>> news:u5pBVi5RFHA.2604@TK2MSFTNGP10.phx.gbl... >>>>>>I was just curious on the pros and cons of returning a value via a >>>>>>return value vs a select statement in my Stored Procedure. >>>>> >>>>> << snip >> >>>>> >>>>>> Which is better? >>>>> >>>>> >>>> >>>> >>> >>> >> >> > >
Show quote
"Michael C#" <ho***@boutdat.com> wrote in message Would this also give you the message, if there is one from the sysmessages news:uC9IWzyWFHA.2288@TK2MSFTNGP14.phx.gbl... > You would change this line: > > Catch ex As Exception > > To this: > > Catch ex As SqlException > > Then you could retrieve the error number like this: > > Dim ErrorNumber As Integer = ex.Number > > SqlException.Number corresponds directly to the master.dbo.sysmessages > table. table? I assume that I could pass the error as my own number as well. Thanks, Tom Show quote > > "tshad" <tscheider***@ftsolutions.com> wrote in message > news:%23iMZelyWFHA.3676@TK2MSFTNGP10.phx.gbl... >> This is an old post, but I was curious on how you would get the Error >> value that was sent from this Stored Procedure? >> >> It shows how to get the Return Value, but not the Error (Return @@error). >> >> Thanks, >> >> Tom >> "tshad" <tscheider***@ftsolutions.com> wrote in message >> news:ORt9no6RFHA.3560@TK2MSFTNGP14.phx.gbl... >>> "Michael C#" <x**@abcdef.com> wrote in message >>> news:a5hae.2507$RP1.1935@fe10.lga... >>>> In ASP.NET you would set this up as a Parameterized Query, the >>>> @RetValue Parameter would be defined as ParameterDirection.Output, all >>>> others would be defined as (default) Input. It would look something >>>> like this (not tested): >>>> >>>> ' Assumes you are using the System.Data.SqlClient >>>> Dim SqlCon As New SqlConnection("connection-string") >>>> Dim SqlCmd As SqlCommand >>>> Dim RowCount As Integer >>>> SqlCon.Open() >>>> Try >>>> SqlCmd = New SqlCommand("EXEC dbo.CopyFromTemplate @ClientID, >>>> @PositionIDFrom, @PositionIDTo, @RetValue") >>>> SqlCmd.Parameters.Add("@ClientID", SqlDbType.VarChar, 20).Value = >>>> "A1238" >>>> SqlCmd.Parameters.Add("@PositionIDFrom", SqlDbType.Int).Value = 10 >>>> SqlCmd.Parameters.Add("@PositionIDTo", SqlDbType.Int).Value = 20 >>>> SqlCmd.Parameters.Add("@RetValue", SqlDbType.Int).Direction = >>>> ParameterDirection.Output >>>> SqlCmd.ExecuteNonQuery() >>>> RowCount = Convert.ToInt32(SqlCmd.Parameters("@RetValue").Value) >>>> Catch ex As Exception >>>> MessageBox.Show(ex.Message) >>>> Finally >>>> If Not (SqlCmd Is Nothing) Then >>>> SqlCmd.Dispose() >>>> End If >>>> If Not (SqlCon Is Nothing) Then >>>> SqlCon.Dispose() >>>> End If >>>> End Try >>> >>> Thanks, that helps a lot. >>> >>> Tom >>>> >>>> >>>> "tshad" <tscheider***@ftsolutions.com> wrote in message >>>> news:%23nVlfv5RFHA.3296@TK2MSFTNGP15.phx.gbl... >>>>> "Michael C#" <x**@abcdef.com> wrote in message >>>>> news:Augae.16611$ZQ1.8234@fe11.lga... >>>>>> Return at the end of your SP should be reserved for @@error. >>>>>> Standard practice is to return the success/failure error code as the >>>>>> return value. If you want to retuen a value the other option is to >>>>>> use an OUTPUT parameter as in the following: >>>>>> >>>>>> CREATE PROCEDURE CopyFromTemplate >>>>>> ( >>>>>> @ClientID varChar(20),@PositionIDFrom int,@PositionIDTo int, >>>>>> @RetValue int OUTPUT >>>>>> ) As >>>>>> >>>>>> Insert ScreenQuestions >>>>>> (ClientID,PositionID,Version,QuestionUnique,Question,QuestionType,SortOrder, >>>>>> Answers,Weight) >>>>>> Select >>>>>> ClientID,@PositionIDTo,Version,QuestionUnique,Question,QuestionType,SortOrder,Answers,Weight >>>>>> From ScreenQuestionsTemplates >>>>>> Where ClientID = @ClientID and PositionID = @PositionIDFrom >>>>>> >>>>>> SET @RetValue = @@RowCount >>>>>> >>>>>> RETURN @@error >>>>>> >>>>>> GO >>>>>> >>>>>> You would call this like this: >>>>>> >>>>>> DECLARE @Rows INT >>>>>> EXEC CopyFromTemplate 'A10092', 10, 20, @Rows OUTPUT >>>>> >>>>> I'm not sure how I would call this in Asp.Net. >>>>> >>>>> In asp.net, I would just read what was returned (from the select >>>>> statement). For a return, I would set up a return parameter. >>>>> >>>>> Would "SET @RetValue = @@RowCount" return the value similar to the >>>>> select where I would would just read it like a record? >>>>> >>>>> Thanks, >>>>> >>>>> Tom >>>>>> >>>>>> Hope that helps. >>>>>> >>>>>> "tshad" <tscheider***@ftsolutions.com> wrote in message >>>>>> news:u5pBVi5RFHA.2604@TK2MSFTNGP10.phx.gbl... >>>>>>>I was just curious on the pros and cons of returning a value via a >>>>>>>return value vs a select statement in my Stored Procedure. >>>>>> >>>>>> << snip >> >>>>>> >>>>>>> Which is better? >>>>>> >>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > > SqlException object's .Message property returns the error message(s).
Show quote "tshad" <tscheider***@ftsolutions.com> wrote in message news:%23qxH93yWFHA.1240@TK2MSFTNGP14.phx.gbl... > "Michael C#" <ho***@boutdat.com> wrote in message > news:uC9IWzyWFHA.2288@TK2MSFTNGP14.phx.gbl... >> You would change this line: >> >> Catch ex As Exception >> >> To this: >> >> Catch ex As SqlException >> >> Then you could retrieve the error number like this: >> >> Dim ErrorNumber As Integer = ex.Number >> >> SqlException.Number corresponds directly to the master.dbo.sysmessages >> table. > > Would this also give you the message, if there is one from the sysmessages > table? > > I assume that I could pass the error as my own number as well. > > Thanks, > > Tom >> >> "tshad" <tscheider***@ftsolutions.com> wrote in message >> news:%23iMZelyWFHA.3676@TK2MSFTNGP10.phx.gbl... >>> This is an old post, but I was curious on how you would get the Error >>> value that was sent from this Stored Procedure? >>> >>> It shows how to get the Return Value, but not the Error (Return >>> @@error). >>> >>> Thanks, >>> >>> Tom >>> "tshad" <tscheider***@ftsolutions.com> wrote in message >>> news:ORt9no6RFHA.3560@TK2MSFTNGP14.phx.gbl... >>>> "Michael C#" <x**@abcdef.com> wrote in message >>>> news:a5hae.2507$RP1.1935@fe10.lga... >>>>> In ASP.NET you would set this up as a Parameterized Query, the >>>>> @RetValue Parameter would be defined as ParameterDirection.Output, all >>>>> others would be defined as (default) Input. It would look something >>>>> like this (not tested): >>>>> >>>>> ' Assumes you are using the System.Data.SqlClient >>>>> Dim SqlCon As New SqlConnection("connection-string") >>>>> Dim SqlCmd As SqlCommand >>>>> Dim RowCount As Integer >>>>> SqlCon.Open() >>>>> Try >>>>> SqlCmd = New SqlCommand("EXEC dbo.CopyFromTemplate @ClientID, >>>>> @PositionIDFrom, @PositionIDTo, @RetValue") >>>>> SqlCmd.Parameters.Add("@ClientID", SqlDbType.VarChar, 20).Value = >>>>> "A1238" >>>>> SqlCmd.Parameters.Add("@PositionIDFrom", SqlDbType.Int).Value = 10 >>>>> SqlCmd.Parameters.Add("@PositionIDTo", SqlDbType.Int).Value = 20 >>>>> SqlCmd.Parameters.Add("@RetValue", SqlDbType.Int).Direction = >>>>> ParameterDirection.Output >>>>> SqlCmd.ExecuteNonQuery() >>>>> RowCount = Convert.ToInt32(SqlCmd.Parameters("@RetValue").Value) >>>>> Catch ex As Exception >>>>> MessageBox.Show(ex.Message) >>>>> Finally >>>>> If Not (SqlCmd Is Nothing) Then >>>>> SqlCmd.Dispose() >>>>> End If >>>>> If Not (SqlCon Is Nothing) Then >>>>> SqlCon.Dispose() >>>>> End If >>>>> End Try >>>> >>>> Thanks, that helps a lot. >>>> >>>> Tom >>>>> >>>>> >>>>> "tshad" <tscheider***@ftsolutions.com> wrote in message >>>>> news:%23nVlfv5RFHA.3296@TK2MSFTNGP15.phx.gbl... >>>>>> "Michael C#" <x**@abcdef.com> wrote in message >>>>>> news:Augae.16611$ZQ1.8234@fe11.lga... >>>>>>> Return at the end of your SP should be reserved for @@error. >>>>>>> Standard practice is to return the success/failure error code as the >>>>>>> return value. If you want to retuen a value the other option is to >>>>>>> use an OUTPUT parameter as in the following: >>>>>>> >>>>>>> CREATE PROCEDURE CopyFromTemplate >>>>>>> ( >>>>>>> @ClientID varChar(20),@PositionIDFrom int,@PositionIDTo int, >>>>>>> @RetValue int OUTPUT >>>>>>> ) As >>>>>>> >>>>>>> Insert ScreenQuestions >>>>>>> (ClientID,PositionID,Version,QuestionUnique,Question,QuestionType,SortOrder, >>>>>>> Answers,Weight) >>>>>>> Select >>>>>>> ClientID,@PositionIDTo,Version,QuestionUnique,Question,QuestionType,SortOrder,Answers,Weight >>>>>>> From ScreenQuestionsTemplates >>>>>>> Where ClientID = @ClientID and PositionID = @PositionIDFrom >>>>>>> >>>>>>> SET @RetValue = @@RowCount >>>>>>> >>>>>>> RETURN @@error >>>>>>> >>>>>>> GO >>>>>>> >>>>>>> You would call this like this: >>>>>>> >>>>>>> DECLARE @Rows INT >>>>>>> EXEC CopyFromTemplate 'A10092', 10, 20, @Rows OUTPUT >>>>>> >>>>>> I'm not sure how I would call this in Asp.Net. >>>>>> >>>>>> In asp.net, I would just read what was returned (from the select >>>>>> statement). For a return, I would set up a return parameter. >>>>>> >>>>>> Would "SET @RetValue = @@RowCount" return the value similar to the >>>>>> select where I would would just read it like a record? >>>>>> >>>>>> Thanks, >>>>>> >>>>>> Tom >>>>>>> >>>>>>> Hope that helps. >>>>>>> >>>>>>> "tshad" <tscheider***@ftsolutions.com> wrote in message >>>>>>> news:u5pBVi5RFHA.2604@TK2MSFTNGP10.phx.gbl... >>>>>>>>I was just curious on the pros and cons of returning a value via a >>>>>>>>return value vs a select statement in my Stored Procedure. >>>>>>> >>>>>>> << snip >> >>>>>>> >>>>>>>> Which is better? >>>>>>> >>>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > > "Michael C#" <ho***@boutdat.com> wrote in message OK.news:e6j3zB9WFHA.2700@TK2MSFTNGP12.phx.gbl... > SqlException object's .Message property returns the error message(s). Thanks, Tom Show quote > > "tshad" <tscheider***@ftsolutions.com> wrote in message > news:%23qxH93yWFHA.1240@TK2MSFTNGP14.phx.gbl... >> "Michael C#" <ho***@boutdat.com> wrote in message >> news:uC9IWzyWFHA.2288@TK2MSFTNGP14.phx.gbl... >>> You would change this line: >>> >>> Catch ex As Exception >>> >>> To this: >>> >>> Catch ex As SqlException >>> >>> Then you could retrieve the error number like this: >>> >>> Dim ErrorNumber As Integer = ex.Number >>> >>> SqlException.Number corresponds directly to the master.dbo.sysmessages >>> table. >> >> Would this also give you the message, if there is one from the >> sysmessages table? >> >> I assume that I could pass the error as my own number as well. >> >> Thanks, >> >> Tom >>> >>> "tshad" <tscheider***@ftsolutions.com> wrote in message >>> news:%23iMZelyWFHA.3676@TK2MSFTNGP10.phx.gbl... >>>> This is an old post, but I was curious on how you would get the Error >>>> value that was sent from this Stored Procedure? >>>> >>>> It shows how to get the Return Value, but not the Error (Return >>>> @@error). >>>> >>>> Thanks, >>>> >>>> Tom >>>> "tshad" <tscheider***@ftsolutions.com> wrote in message >>>> news:ORt9no6RFHA.3560@TK2MSFTNGP14.phx.gbl... >>>>> "Michael C#" <x**@abcdef.com> wrote in message >>>>> news:a5hae.2507$RP1.1935@fe10.lga... >>>>>> In ASP.NET you would set this up as a Parameterized Query, the >>>>>> @RetValue Parameter would be defined as ParameterDirection.Output, >>>>>> all others would be defined as (default) Input. It would look >>>>>> something like this (not tested): >>>>>> >>>>>> ' Assumes you are using the System.Data.SqlClient >>>>>> Dim SqlCon As New SqlConnection("connection-string") >>>>>> Dim SqlCmd As SqlCommand >>>>>> Dim RowCount As Integer >>>>>> SqlCon.Open() >>>>>> Try >>>>>> SqlCmd = New SqlCommand("EXEC dbo.CopyFromTemplate @ClientID, >>>>>> @PositionIDFrom, @PositionIDTo, @RetValue") >>>>>> SqlCmd.Parameters.Add("@ClientID", SqlDbType.VarChar, 20).Value = >>>>>> "A1238" >>>>>> SqlCmd.Parameters.Add("@PositionIDFrom", SqlDbType.Int).Value = 10 >>>>>> SqlCmd.Parameters.Add("@PositionIDTo", SqlDbType.Int).Value = 20 >>>>>> SqlCmd.Parameters.Add("@RetValue", SqlDbType.Int).Direction = >>>>>> ParameterDirection.Output >>>>>> SqlCmd.ExecuteNonQuery() >>>>>> RowCount = Convert.ToInt32(SqlCmd.Parameters("@RetValue").Value) >>>>>> Catch ex As Exception >>>>>> MessageBox.Show(ex.Message) >>>>>> Finally >>>>>> If Not (SqlCmd Is Nothing) Then >>>>>> SqlCmd.Dispose() >>>>>> End If >>>>>> If Not (SqlCon Is Nothing) Then >>>>>> SqlCon.Dispose() >>>>>> End If >>>>>> End Try >>>>> >>>>> Thanks, that helps a lot. >>>>> >>>>> Tom >>>>>> >>>>>> >>>>>> "tshad" <tscheider***@ftsolutions.com> wrote in message >>>>>> news:%23nVlfv5RFHA.3296@TK2MSFTNGP15.phx.gbl... >>>>>>> "Michael C#" <x**@abcdef.com> wrote in message >>>>>>> news:Augae.16611$ZQ1.8234@fe11.lga... >>>>>>>> Return at the end of your SP should be reserved for @@error. >>>>>>>> Standard practice is to return the success/failure error code as >>>>>>>> the return value. If you want to retuen a value the other option is >>>>>>>> to use an OUTPUT parameter as in the following: >>>>>>>> >>>>>>>> CREATE PROCEDURE CopyFromTemplate >>>>>>>> ( >>>>>>>> @ClientID varChar(20),@PositionIDFrom int,@PositionIDTo int, >>>>>>>> @RetValue int OUTPUT >>>>>>>> ) As >>>>>>>> >>>>>>>> Insert ScreenQuestions >>>>>>>> (ClientID,PositionID,Version,QuestionUnique,Question,QuestionType,SortOrder, >>>>>>>> Answers,Weight) >>>>>>>> Select >>>>>>>> ClientID,@PositionIDTo,Version,QuestionUnique,Question,QuestionType,SortOrder,Answers,Weight >>>>>>>> From ScreenQuestionsTemplates >>>>>>>> Where ClientID = @ClientID and PositionID = @PositionIDFrom >>>>>>>> >>>>>>>> SET @RetValue = @@RowCount >>>>>>>> >>>>>>>> RETURN @@error >>>>>>>> >>>>>>>> GO >>>>>>>> >>>>>>>> You would call this like this: >>>>>>>> >>>>>>>> DECLARE @Rows INT >>>>>>>> EXEC CopyFromTemplate 'A10092', 10, 20, @Rows OUTPUT >>>>>>> >>>>>>> I'm not sure how I would call this in Asp.Net. >>>>>>> >>>>>>> In asp.net, I would just read what was returned (from the select >>>>>>> statement). For a return, I would set up a return parameter. >>>>>>> >>>>>>> Would "SET @RetValue = @@RowCount" return the value similar to the >>>>>>> select where I would would just read it like a record? >>>>>>> >>>>>>> Thanks, >>>>>>> >>>>>>> Tom >>>>>>>> >>>>>>>> Hope that helps. >>>>>>>> >>>>>>>> "tshad" <tscheider***@ftsolutions.com> wrote in message >>>>>>>> news:u5pBVi5RFHA.2604@TK2MSFTNGP10.phx.gbl... >>>>>>>>>I was just curious on the pros and cons of returning a value via a >>>>>>>>>return value vs a select statement in my Stored Procedure. >>>>>>>> >>>>>>>> << snip >> >>>>>>>> >>>>>>>>> Which is better? >>>>>>>> >>>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics