using if...then on one RS, to get output from another RS in an SP

  • 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

    1convention11

    1innovation4

    1consolidation11

    1growth4

    1process8

    1results7

    1planning8

    1execution7

    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

  • if element = 'planning' and result >5 then

    This 'Plannnig' and value '5' is input or the values fetched from the select statement ?

    Thanks,

    Chandru

  • 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,

  • 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

  • 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