Big stored procedures which are building QUERY dynamically according to parameters are used sometime for large web applications.
Bad luck starts when such procedure gives error, and as query itself build at run time, user may frustrates catching the right error.
Also,Debugging becomes challenging with it.
One way is to use PRINT statement at end of procedure, so when stored procedure executed it displays DYNAMIC QUERY.
In SQL Server Management studio we can easily view the printed query.
But what if you want it in ASP.NET environemt?
SqlConneciton class has
SqlConnection.InfoMessage event, which occurs, when SQL Servers returns a warning or informational message.
First create ‘SqlConnection’ object.
string connectionString = “Your Connection String…”;
SqlConnection myConn = new SqlConnection(connectionString);
Then, Relate method to event.
myConn.InfoMessage += new SqlInfoMessageEventHandler(MyConnection _InfoMessage);
Your method may look like following:
System.Text.StringBuilder sbLog = new System.Text.StringBuilder();
void MyConnection_InfoMessage(object sender, SqlInfoMessageEventArgs e)
Execute following code and test output with ‘sbLog.ToString()’.
SqlCommand cmd = new SqlCommand();
if (cn.State != ConnectionState.Open)
cmd.Connection = myConn;
cmd.CommandText = “SP_That_Prints_Query”;
cmd.CommandType = CommandType.StoredProcedure;
‘sbLog.ToString()’ will give you dynamic query generated in your stored procedure.