November 2, 2011 at 10:16 pm
HI all,
i want to write log of error occured in stored Procedure,how i can write down SP For that.Here is my code.
string ErrorMessage = "";
// create db
Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = db.GetStoredProcCommand("usp_be_DeleteCityData");
db.AddInParameter(dbCommand, "@CITYID", DbType.Int64, this.CITYID);
db.AddOutParameter(dbCommand, "@ErrorMessage", DbType.String, 150);
int iResult = db.ExecuteNonQuery(dbCommand);
if (iResult > 0) //If Any record is updated in Database then return True Else Return False;
return true;
else
{
Logger.Write(ErrorMessage);
return false;
}
Incase of delete how i ll write an sp which ll output @ErrorMessage with error??
November 3, 2011 at 7:38 am
Can you more clearly explain what you are trying to do?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 4, 2011 at 11:25 am
You have a couple of options:
1. Instead of using an output parameter in the stored procedure, allow the error to come up to the .NET application. Wrap the .NET SP call in a TRY CATCH and catch the exception. CATCH (SQLException sqlEx) {Logger.Write(sqlEx.ToString()}. You don't have to use ToString, you could be more specific.
2. Use TRY CATCH in the stored procedure and put the results of the ERROR_MESSAGE() function into the @ErrorMessage output variable.
I'd be more inclined to use #1.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 9, 2011 at 2:18 am
SSCrazy:
Here is what i want to do....
Case 1:Save OR Delete Data into database
string ErrorMessage = "";
Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = db.GetStoredProcCommand("usp_be_SaveCityData");
db.AddInParameter(dbCommand, "@City", DbType.String, this.CITY);
db.AddOutParameter(dbCommand, "@ErrorMessage", DbType.String, 150);
int iResult = db.ExecuteNonQuery(dbCommand);
ErrorMessage = db.GetParameterValue(dbCommand, "@ErrorMessage").ToString();
if (iResult > 0) //If Any record is updated in Database then return True Else Return False;
return true;
else
{
Logger.Write(ErrorMessage);
return false;
}
IN this case how i will treat ErrorMessage in store procedure?please give me a simple Store procedure which will implement @ErrorMessage and will be Ok with this code.
Case 2:
Select/Display Data
here is code for this case....i want to have select query in this SP
Int64 Error;
//Create database
Database db = DatabaseFactory.CreateDatabase();
//Add Sp and command parameters
DbCommand oCmd = db.GetStoredProcCommand("usp_be_DisplayCityList");
db.AddOutParameter(oCmd, "@Error", DbType.Int64, 10);
DataSet dsResult = db.ExecuteDataSet(oCmd);
Error = Convert.ToInt64(db.GetParameterValue(oCmd, "@Error"));
if (Error == 1100)
{
var query = from o in dsResult.Tables[0].AsEnumerable()
select new CitiesList
{
CITYID = o.Field<int>("CITYID"),
CITY = o.Field<string>("CITY"),
Keywords = o.Field<string>("Keywords")
};
List<CitiesList> lstDisplay = new List<CitiesList>();
lstDisplay.AddRange(query);
return lstDisplay;
}
//If No Records ,Return NULL
return null;
Here how i will treat Error in SP.give me a simple SP which ll satisfy this code?
November 9, 2011 at 7:04 am
If you insist on passing back the error message as an output parameter instead of handling the error condition in .NET then your stored procedure(s) need to do something like this:
CREATE PROCEDURE SCHEMA.proc_name
(
@parameter datatype,
@value datatype,
@ErrorMessage nvarchar(2048) OUTPUT
)
AS
SET NOCOUNT ON;
BEGIN TRY;
BEGIN TRANSACTION;
UPDATE TABLE
SET COLUMN = VALUE
WHERE
COLUMN = @parameter;
IF @@TRANCOUNT >0
BEGIN;
COMMIT TRANSACTION;
END;
END TRY
BEGIN CATCH
SET @ErrorMessage = ERROR_MESSAGE();
IF XACT_STATE() <> 0
BEGIN;
ROLLBACK TRANSACTION
END;
END CATCH;
If you have specific conditions where you want to pass back a custom error message you can use RAISERROR which will send control to the CATCH block.
For a simple select you are unlikely to get an error, but you can still use the TRY...CATCH but you wouldn't need the transaction handling.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 9, 2011 at 7:19 am
I'm with Jack on this one. Let your exception bubble back up to your .net application. In the two examples of code you provided you have different datatypes you are trying to "handle". The first one was a varchar and the second one an int64. Just have sql call raiseerror and put your .net code in a try-catch. That will be a lot easier than going from the sql side.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 9, 2011 at 7:27 am
Actually i am working on already developed application,where i am adding new features and modifying existing system.The previous developer have used these output parameters instead of using .Net.i do not know about the code he is using...like @Error==1100 and might be he is using Custom Error Message which we can not provide in .Net
November 9, 2011 at 7:33 am
engrshafiq4 (11/9/2011)
Actually i am working on already developed application,where i am adding new features and modifying existing system.The previous developer have used these output parameters instead of using .Net.i do not know about the code he is using...like @Error==1100 and might be he is using Custom Error Message which we can not provide in .Net
1100 is not a custom error from SQL Server, well, not using RAISERROR. You can set an output parameter to any value (valid for the data type) you want, so it still could be a custom error, just not what I consider a custom error.
If you are working on an existing application then there are existing SP's being called that you should be able to view and see how the previous developer did things and mimic that.
I still suggest fixing the code since you are in it. Handling the errors in the established standard would be a good thing.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply