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 |
|
lloydsj
Starting Member
7 Posts |
 Posted - 2003-05-29 : 08:09:57
|
| Hi,What I am trying to find out is whether my problem can be done totally through an SQL Stored Procedure, or whether I need to do some .NET coding with it too?Basically I am trying to save a record to a table, but before I can save it I need to get some information from 2 other tables. Sounds easy… wait. From the first table (Users) (which is the easy one) I just find the current users record and from that find what ‘RuleSet’ they belong to. Then the 2nd table (Rules) contains all the rules (SQL Strings), the RuleSet the rule belongs to, and the Info I want to get out.Users (Table)Username , RuleSetRules (Table)Rule (which is an SQL String) , RuleSet , InfoNow here comes where my problem is. With this 2nd table, out of the rules that belong to the RuleSet of the current user, I need to execute the SQL String stored in the Rule field and therefore find out which rule returns some data (only 1 of the rules in a set will return any data, the others will return nothing). Therefore I don’t need to worry about whether I execute ALL the rules in the RuleSet or just loop through until I find one that returns some data. Once I have found the Rule that returns some data, I just need to save the Info field that is related to that Rule (ie. The Info field in the Rules table).I know that I can solve this problem by having a Stored Proc to return the current users rule set. Then using code in .NET to loop through this set and find the one that returns some data. Then use another stored procedure to save the record with the details I have retrieved. Job complete.So… can I do this all in 1 stored procedure… or do I need to follow the 2 SP and code path?Cheers. |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-05-29 : 08:36:07
|
| I'm not clear on what exactly it is that you need to "save".Anyway, it sounds as if you should be able to do this in one procedure, if not one sql statement. You probably need to get yourself out of the idea of "loops" as you may not really have to loop through anything at all. You may find EXISTS helpful.-------Moo. |
 |
|
|
lloydsj
Starting Member
7 Posts |
Posted - 2003-05-29 : 08:43:38
|
| What I am saving is...A load of details that the user fills in on screen TOGETHER with the INFO field from the Rules table.However the only way to find out which INFO field to save with the details, is to check the current users RuleSet in the Users table, AND THEN execute the SQL Strings that are stored in the Rule field for the RuleSet of the current user. (ie. there will be numerous rules for each RuleSet, but only by executing them can I find out which is the correctly one to use, and therefore get the INFO from).Hope that clears it up. |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-05-29 : 09:09:16
|
| You can do this in the SP, but why don't you just add a column to the Rules table that indicates whether a Rule returns Data?Sam |
|
|
|
lloydsj
Starting Member
7 Posts |
Posted - 2003-05-29 : 10:57:15
|
Because all the rules are capable of returning data!!! ... but because the Rule (SQL String stored in the Rules table) is dependant on the current user and the details which they have entered on screen, it means that only 1 of the Rules WILL actually return anything. The rest will return an empty record set. However for a different user, or if they entered different things on screen, it may be a different rule which returns something.If anyone understands what Im trying to do... it would be helpful if you could give me some advice of how to do it... rather than just saying "Yep its possible!"  |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-05-29 : 12:21:18
|
quote:
the Rule (SQL String stored in the Rules table) is dependant on the current user and the details which they have entered on screen
So this rule (SQL String) has parameters and the user data entered needs to be substituted. I didn't see this info in the first post, and it does explain why each row needs to be independently tested.Are the SQL rules parameterized like:SELECT * FROM MyTable where User=@UserThe information is scanty, a detailed answer including how to do the parameter substitution requires more information. Roughly speaking you can tryEXEC mySQLIF @@ROWCOUNT > 0 BEGIN -- Something was returned from the queryENDYou might find help if you read about EXEC in BOL.Sam |
|
|
|
|
|
|
|
|
Can I do this in 1 stored procedure?