return value from exec(string)

Question

return value from exec(string)

AndreQ

SSCommitted

Points: 1540
More actions
May 22, 2003 at 12:10 pm

#81626

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.
TIA
Andre

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

jpipes SSChampion Points: 12230 More actions · Answer 1

Would you mind posting the table structures. I am not sure whether you have an unnormalized structure or not. If so, it may be advantageous to normalize the structure. Please post the DDL for the table, if possible. Thanks,

Jay

AndreQ SSCommitted Points: 1540 More actions · Answer 2

The example I gave was not the best in that it immediate brought up questions of why I was trying to generate the key in that way in the first place.

The situation is more one of coming into a place where they keys are generated in this way, but for each table and I wanted to make it generic.

The question is more: how do I get out results when EXEC a string and its SQL 6.5 compatibility, otherwise I would have used sp_executesql.

I found a great solution at this website:

http://www.sqlteam.com/item.asp?ItemID=4619

where the author recommends creating a temporary table and putting the results inside as an INSERT.

jpipes SSChampion Points: 12230 More actions · Answer 3

I think I could help more if I understood what the overall objective is. Are you trying to get a scalar or table-type value from the procedure? If you are getting a scalar value, use an OUTPUT parameter or the intrinsic RETURNVALUE parameter:



-- Output parameter

DECLARE @OutputParam INT

EXEC proc_GetLastKeyValue @Address, @OutputParam OUTPUT

-- Return Value

DECLARE @r INT

EXEC @r = proc_GetLastKeyValue @Address

Am I anywhere close to what you're asking for?

AndreQ SSCommitted Points: 1540 More actions · Answer 4

No, you're not.

Your last response gives me some good ideas for future projects, but the website that I posted in my last response (the one about temporary tables) gave me a good solution that I'm using.

Thanks anyway.

GRN Hall of Fame Points: 3260 More actions · Answer 5

This one does not need a temp table

USE PUBS

DECLARE @STRnvarchar(200),

@Parm nvarchar(20),

@Cntint

SET @STR = 'SELECT @Cnt= COUNT(*) FROM Authors'

SET @Parm = '@Cnt int output'

EXEC sp_executesql @STR , @Parm ,@Cnt OUT

PRINT @Cnt

AndreQ SSCommitted Points: 1540 More actions · Answer 6

The code that I'm dealing with requires a prefix that may or may not be there. The suffix is just a straight counter with leading 0's.

I would have gone with the Max-Count method if that were not the case.