Asp.net Return Error From Stored Procedure
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn sql server stored procedure raiserror more about Stack Overflow the company Business Learn more about hiring developers or posting how to display message in sql stored procedure ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community
Sql Return Message From Stored Procedure
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Return a message from a stored procedure to C# app http://forums.asp.net/t/1739122.aspx?How+can+I+return+a+text+message+error+from+a+stored+procedure+ up vote 2 down vote favorite 1 I have a stored procedure that adds a user and at each permission I add, I want to start building a success message. My stored procedure runs fine but how do I get that success message back into a message dialog in my app? I want to display the below @text in a messagebox in my C# app. DECLARE @text NVARCHAR(1000) http://stackoverflow.com/questions/11260418/return-a-message-from-a-stored-procedure-to-c-sharp-app SET @text = 'This is line 1.' + CHAR(13)+CHAR(10) + 'This is line 2.' SELECT @text This is my call in my C# app: public DataTable CreateOrDropUser(string dataBase, string procedure, SqlParameter[] parameters) { try { if (dataBase.Length > 0) { procedure = dataBase + ".." + procedure; } //Set procedure to DBNAME..ProcedureName SqlCommand cmd1 = new SqlCommand(procedure, con); cmd1.CommandType = CommandType.StoredProcedure; foreach (SqlParameter p in parameters) { if (p != null) { cmd1.Parameters.Add(p); } } con.Open(); DataTable dt = new DataTable(); SqlDataAdapter da = new SqlDataAdapter(cmd1); da.Fill(dt); con.Close(); MessageBox.Show("Success"); //This should display the @text variable in my proc return dt; } catch (Exception ex) { try { if (con.State == ConnectionState.Open) { con.Close(); } } catch { MessageBox.Show("Could not connect to database. Check settings. " + ex.Message, "Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } MessageBox.Show(ex.Message); return null; } } My Stored proc, Just focus on sections by all the prints, that's the text i'm adding: ALTER PROCEDURE [dbo].[AdminDevUserCreate] @SQLLoginName varchar(50), @SQLLoginPass varchar(50) AS DECLARE @text NVARCHAR(1000)OUTPUT --PRINT 'Create SQL Login' SET @text = 'Create SQL Login ' + @SQLLoginName -- USE [Master] EXEC(' USE [master] CREATE LOGIN [' + @SQLLoginName + '] WITH PASSWORD=''' + @SQLLoginPass + ''', DEFAULT_DATABASE=[TestAudit], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF') --PRINT 'Add Server Rol
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about http://stackoverflow.com/questions/26728041/how-to-capture-error-in-sql-server-stored-procedure Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up How to capture error in SQL Server stored procedure? up vote 0 down vote stored procedure favorite I need to capture the error and send it to the web application. For example, the below is my stored procedure: CREATE PROC ADDNAME (@FirstName VARCHAR(10), @LastName VARCHAR(10)) AS BEGIN BEGIN TRY BEGIN TRAN INSERT INTO EMPLOYEE(FirstName, LastName) VALUES (@FirstName, @LastName) END TRY BEGIN CATCH ROLLBACK TRAN PRINT @@ERRORMESSAGE END CATCH END Here, how to capture the error and send it to the web application? Normally, which is the from stored procedure best method to handle stored procedure errors from web application? I am using ASP.NET & C# as a front end. I need your suggestions. sql-server-2008 stored-procedures error-handling share|improve this question edited Nov 4 '14 at 6:20 asked Nov 4 '14 at 5:01 thevan 3,4583492152 you can use RAISERROR(Transact-SQL) try this link msdn.microsoft.com/en-us/library/ms178592.aspx –Andamon A. Abilar Nov 4 '14 at 5:16 Yes. In catch block, I generate the error by the things specified in the above link. But how do we send the error to the front end? –thevan Nov 4 '14 at 5:23 add a comment| 2 Answers 2 active oldest votes up vote 2 down vote accepted First, you cannot rollback in BEGIN CATCH. Not without checking XACT_STATE() first. Unless the xact_state() is 1, you cannot run ROLLBACK. Think about the trivial case when the exception is 1205 (a deadlock has occurred) in which case you get the exception after your transaction was forcefully rolled back. See Exception handling and nested transactions for a correct pattern that mixes transactions and error handling. Second, your stored procedure does not need transaction and error handling as is. Unless the real code is much more complex, your procedure doesn't add any value. Lastly, to raise an err