November 9, 2009 at 6:19 am
the code below
CREATE PROCEDURE [test]
@User_Id as int
AS
BEGIN
SELECT TOP 1000 [USER_ID]
,[element]
,[result]
FROM [xxx].[dbo].[myView]
WHERE q='i' and USER_ID=@user_id
gives me a list several records, with an element, and a result for each, for a single user. The user_id is passed in. Result set looks like
user Element score
1 convention 11
1 innovation 4
1 consolidation 11
1 growth 4
1 process 8
1 results 7
1 planning 8
1 execution 7
Rather than return the result of the select, I want to challenge the results, and if conditions are met,
return the output from a 'SELECT' from another table, an example in psuedo code might look like like
if element = 'planning' and result >5 then
select text_out,
FROM tbl_txt_output
WHERE text_out_id = 12
I'm hoping this can be done inside a single stored procedure. Please note that the table/s making up myView, and tbl_text_out have no relationship.
Any advice welcome.
Cheers
November 9, 2009 at 6:36 am
if element = 'planning' and result >5 then
This 'Plannnig' and value '5' is input or the values fetched from the select statement ?
Thanks,
Chandru
November 9, 2009 at 2:06 pm
Chandru -734144 (11/9/2009)
if element = 'planning' and result >5 then
This 'Plannnig' and value '5' is input or the values fetched from the select statement ?
Thanks,
Chandru
It could be input, or fixed in the SP. Does not come from the select,
November 9, 2009 at 4:50 pm
Sorry, but it sounds to me like a bad design idea. If you want to return different rowsets (columns, data types) from a procedure depending on the input, how will you handle this in the application code? Wouldn't it be better to write a second procedure and decide which one to call in the business layer?
Regards
Piotr
...and your only reply is slàinte mhath
November 9, 2009 at 7:23 pm
Thanks for reply
The application builds a report containing many blocks of text from a table containing them. There are many rules which are applied to the list of results to generate these text blocks for the report. The design may indeed be a poor one, but it would clean up the application code no end, making it easier to format the report. For a single rule the code might look something like
set rs=objconn.Execute(Select * from myView where user_id= session(user_id)
dim myResult
do while rs.eof
if rs('element')= 'planning' then myResult = rs('result').
if myResult>5 then
set rs2=objconn.Execute(Select text_out from tbl_text_out where text_out_id = 25)
loop
response.write rs2.text_out
set rs=nothing
set rs2=nothing
whats happening here is
Get all the list of results
find the one we are interested in
Check if its a value we want,
if it is, then get the appropriate text value from the text_out table
Present the text_out onto the screen.
If the stored procedure handled it, it might look like
set rs=objConn.Execute(SP_Rule1) & 'user_id
response.write(rs.text_out_id).
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply