I'd like to write a generic procedure that will accept a table name and seed value and return a unique ID. The routine will check the seed value against the table, and if its exists, increment a counter by one.
For example, table is 'Address' and the seed is 'CustKey'. If count of CustKey > 1, try CustKey1. If CustKey1 is there, then CustKey2 and so on. I would like it to return the unique ID.
I've tried to exec('select count(*) from ' + @tableName + ' where IdentityCol = ' + testKey ) and that will return 0 or more, but how would I test what the returned value is?
If this is not the best approach, what approach could I use? For the exec('string'), @@ROWCOUNT was unaffected. Also, it wouldn't recognize any declared variable outside of itself.
Any help is appreciated.