Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ltgrady
Starting Member
9 Posts |
 Posted - 2007-09-20 : 00:59:10
|
| I have a very long and complicated stored procedure that returns back product search results. We then load those results into a biz object that we bind to a gridview on an asp.net page.Most of the SProc is spent building a dynamic SQL string. Then at the end I execute it callingEXEC(@searchSQL)Those are my records returned back. Well now we've decided we need audit tables for future reporting and tracking. I need to fill in a table with the search criteria and then another table with teh search results. So now after my EXEC(@searchSQL) line above I call an initiator SProc which creates a Service Broker message that is sent to my target (audit) database. The xml is inserted into an SQL audit table.So it something like thisEXEC usp_SearchCriteriaAudit @userID,@userBrand,...criteria stuff..., @sourceI pass in the criteria from this original query and it's sent through SSB and saved.Now my problem. I also need to pass the results into a SSB message that's also sent to the audit table and saved into an audit table.So I need to get the results I normally get from EXEC(@searchSQL) and pass them into another initiator stored procedure, while still returning the results to the biz object we use for our asp.net form.I'm not sure how to do this. Looking for some suggestions. Thanks. |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-20 : 01:13:17
|
| Why not just store the results of the Sproc into a temporary table, then you can process it as many times as you wish.You would be much better off parameterising your dynamic SQL, e.g. by using sp_ExecuteSQL instead of EXEC(@searchSQL)And if your data is within the database to start with, and you want to audit it, I find the concept of converting it to XML and passing it around the place pretty scary compared to just inserting it into a table!Kristen |
 |
|
|
ltgrady
Starting Member
9 Posts |
Posted - 2007-09-20 : 10:28:37
|
| The reason we're converting data to xml is because we're using SSB. The search form is our most used form, it gets constant use by a ton of users. We figure SSB allowed us to send the audit data out asynchronously. So we just fire off a message and move on, we don't have to wait for the write or anything else, we can return our search results to the biz object and form as quickly as possible.We're sending the data to a seperate database (on same server). |
|
|
|
|
|
|
Outputing dynamic sql results to two destinations