| Author |
Topic |
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
 Posted - 2004-09-17 : 11:07:21
|
I'm trying to create a generic sproc which can execute a string (that contains some valid SQL) and assign the return value to a parameter I have (there will always be only one return value as the SQL contains SELECT TOP 1 ...so for example I have the string 'SELECT TOP 1 SMS_CLIN_CONTACT_LOCK from PARAMETER ORDER BY SMS_CLIN_CONTACT_LOCK DESC' in the variable @SQLIDThis will always return one value. I want to assign that value to the variable @tmpID.How do I do this?? when I try EXEC @TMP = @SQLID I get an error that the sproc SELECT TOP 1 SMS_CLIN_CONTACT_LOCK from PARAMETER ORDER BY SMS_CLIN_CONTACT_LOCK DESC cannot be found.I've tried putting brackets in various combinations with no success.Would I be better just re-writing the whole thing either by re-writing the select statements (in which case, how?) or would I be better just not bothering writing a generic function in the first place - it was intended to save time but has now wasted large chunks of my day!!Any suggestions/advice would be appreciatedthankssteveSteve no function beer well without |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-09-17 : 15:25:14
|
i'd have to say i don't see the point of a generic sproc. i vote for rewriting the whole thing.but if you must do it in dynamic sql see sp_executesql in BOL.Go with the flow & have fun! Else fight the flow  |
 |
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2004-09-20 : 04:00:45
|
| The (only) reason for a generic sproc is that otherwise I would have four sprocs that did essentially the same thing (just on four different fields in the same table) - in fact on the same record!!The design of this system (not by me I hasten to add) is that all of the parameters, (including counters for primary keys) Are stored in seperate fields in a table, i.e. there is one record which contains all the system variables. Four of these variable are counters - all I want to do is get the next number from the counter (hence the select statement)steveSteve no function beer well without |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-09-20 : 04:03:21
|
| then i vote for if - else logic.if something = 0begin...endelse....Go with the flow & have fun! Else fight the flow |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-09-20 : 04:03:21
|
| then i vote for if - else logic.if something = 0begin...endelse....Go with the flow & have fun! Else fight the flow |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2004-09-20 : 07:47:00
|
| Having rewritten one and got it working I think now that youre probably right - should be fairly straightforward to modify what I havecheerssteveSteve no function beer well without |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-09-20 : 09:41:59
|
| Note that havingif something = 0begin...endelse....logic in your SProc may cause the query plan to be calculated based on a specific path through the code (the first time the SProc is run) that may not be reresentative of normal execution. You are then stuck with that in your query-plan-cache ...Kristen |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-09-20 : 09:53:38
|
| so you mean that the sproc will return what is not suppose to, or what????or it will just be slower? - but as he just gets an id out, i guess that's not a problem.Go with the flow & have fun! Else fight the flow |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-09-20 : 14:07:30
|
| Slower ... potentially ... but ...Kristen |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2004-09-21 : 17:50:11
|
| Didn't realise that, thanks Kristen. In this particular case speed is unlikely to be an issue, but that is useful to knowcheerssteveSteve no function beer well without |
|
|
|
|
Generics