>>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?