Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

return value from exec(string) Expand / Collapse
Author
Message
Posted Thursday, May 22, 2003 12:10 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 18, 2003 12:00 AM
Points: 60, Visits: 1
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



Post #12465
Posted Thursday, May 22, 2003 1:08 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, October 21, 2003 12:00 AM
Points: 728, Visits: 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




Post #63511
Posted Thursday, May 22, 2003 1:25 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 18, 2003 12:00 AM
Points: 60, Visits: 1
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.





Post #63512
Posted Thursday, May 22, 2003 1:34 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, October 21, 2003 12:00 AM
Points: 728, Visits: 1
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?




Post #63513
Posted Thursday, May 22, 2003 2:53 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 18, 2003 12:00 AM
Points: 60, Visits: 1
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.



Post #63514
Posted Friday, May 23, 2003 12:10 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 8, 2006 2:26 AM
Points: 170, Visits: 1
This one does not need a temp table
USE PUBS
DECLARE @Str nvarchar(200),
@Parm nvarchar(20),
@Cnt int

SET @Str = 'SELECT @Cnt= COUNT(*) FROM Authors'
SET @Parm = '@Cnt int output'

EXEC sp_executesql @Str , @Parm ,@Cnt OUT

PRINT @Cnt






Post #63515
Posted Friday, May 23, 2003 9:16 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 18, 2003 12:00 AM
Points: 60, Visits: 1
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.



Post #63516
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse