>>>Are you sure? You cannot use SQL Server cursor in set oriented commands, like SELECT.
>>
>>I see. Then UDF is the only choice. I'll try to come with one. Basically, here is the functionality required: (Just want to know, if I'm thinking in the right direction)
>>==========================================================
>>UDF - returns a table and return code
>>input parameters State, Category, fieldlist
>>output table result
>>
>>Pseudo-code
>>Checks, if State+'Credit' database exists
>>if yes,
>> StrSQL = 'select ' + @FieldList + ' from '+ @State +'Credit..CreditInfo where CredID in (select ...)'
>> excute this SQL to a table, which would be returned
>>else
>> return error code
>>
>>---------------------------------------------------
>>Main SP:
>>invoke this function for State="CT", Category = "B", then for "MA", "B", union both results to the final output result of SP
>>
>>same for category = "L"
>>same for category = "F"
>>So, the function would be called 6 times and 3 final resultsets would be created.
>>============================================================
>>Do you think, it's possible to write such procedure? Do you see problems in the proposed scenario?
>>
>>Thanks in advance.
>
>I don't think UDF() is an answer in this case. Can you build it as one query with UNION's?

I need 3 different resultsets returned from SP depending on category. But I think, you're right, and for simplicity I would program without UDF for now.

I thought, it would be nice to have a function, which can return a resulting table with variable fields (passed as function parameter).