>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.
If it's not broken, fix it until it is.
My Blog