My suggestion is to have multiple stored procedure and let your data access component call the correct one.

>BTW, in triggers can you have rollback transaction without begin transaction at the top, as you showed on page 55 of your book?

Yes, you can have a ROLLBACK TRANSACTION within a trigger. A trigger is part of the transaction, you could even consider it part of the statement that did the data manipulation.

-Mike

>Hi Mike,
>
>I've showed the simplified version. The field list is much longer and I want to have a flexibility to pass it to a function.
>
>>Why are you using Dynamic SQL?
>>This is harder. The first question is: do you really need to? Could you use derive tables or views to remove the need for creates a bunch of result sets that later get combined. If not, you have your choice of temporary tables or table variables with table variables being the perferred choice, as long as your running at least SQL Server 2000.
>>
>>-Mike
>
>I want to have an SP, which would retrieve 3 resultsets (fields are different depending on category) and in addition calculate total before tax, full number of records and total after tax per each resultset, I also want to update Cart table with Checkout = 'Y', Total = sum of these, Number of recs, Total after tax.
>
>I can create 3 select statements for my resultset, but I'm in dark about items 2 and 3. Do you have suggestions? TaxRate is different depending on the state.
>
>================
>BTW, in triggers can you have rollback transaction without begin transaction at the top, as you showed on page 55 of your book?
>
>Thanks.