August 9, 2011 at 10:52 am
Hi All:
I'm trying to build a function that has to use some dynamic SQL. There are two input values, we'll call them @table, and @ID for clarity.
Without getting into too much painful detail, the @table variable is passed to the SQL Statement to tell the query which table to run from. The problem is, the query will return a single value but I can't figure out to make this happen.
The user calles the function in a query, passing the table name and ID to the function. The Table identifies which table to query from, the ID will Identify which record in that table, and the return result is a specific value from the record of the table.
I've tried building the SQL as a string thus passing the table name and ID to the query dynamicly, then executing the SQL variable. I've also tried passing the result to a table variable, but I can't run an Execute from within the function.
I'm stumped. Any suggestions will be greatly appreciated.
Thanks
Crusty.
August 9, 2011 at 11:01 am
You cannot execute dynamic SQL within a function.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 9, 2011 at 11:04 am
About the only thing you could do is this
CREATE FUNCTION blah blah blah
IF @table = 'tbl1'
select something from tbl1 where ID = @ID
IF @table = 'tbl2'
select something from tbl2 where ID = @ID
....
But that will likely be a performance nightmare (more so than functions usually are)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 9, 2011 at 11:07 am
Can you use a stored procedure instead of a function?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 9, 2011 at 11:09 am
This is not the right way to code in SQL, period.
I won't go through all the problems with this here but go straight to the solution.
What business need(s) are you trying to solve at the moment?
August 9, 2011 at 12:23 pm
All,
Thanks for the replies. Yes I can use a proc instead. As far as the business function, why am I doing this? Well... cuz I was told to.. 😀 BUT, I've since talked to the "senior" individual that told me to do this and told them that it can't be done, at least not that way.
I've since written a stored proc to do it, using an OUTPUT variable, but I know I'm going to catch grief about having to pass a variable to the procedure while in the SQL... Alas... I have a job, so why complain.
LOL
Crusty.
August 9, 2011 at 12:29 pm
Thank you and your "senior".
Code like this allows guys like me to come back in after perf tanks and charge unhuman amount of $ for what I'm telling you free here.
I'm glad you're happy ;-).
August 9, 2011 at 12:35 pm
This "individual" is supposedly very skilled in SS2K8, but then asks me stuff like this...
Ok... I have a proc now:
sproc_blah (@table varchar(5), @ID int, @returned_Stuff INT OUTPUT)
HOw the heck do I call it in a query?
August 9, 2011 at 12:38 pm
CptCrusty1 (8/9/2011)
This "individual" is supposedly very skilled in SS2K8, but then asks me stuff like this...Ok... I have a proc now:
sproc_blah (@table varchar(5), @ID int, @returned_Stuff INT OUTPUT)
HOw the heck do I call it in a query?
You need me to send you my card? 😀
August 9, 2011 at 12:42 pm
:rolleyes:
August 9, 2011 at 12:45 pm
CptCrusty1 (8/9/2011)
:rolleyes:
You know where to find me when you need me :-P.
Just remember that ungodly amount of $ is whatever figure you have in mind now and then multiply by 3, or 5. :hehe:
August 9, 2011 at 1:05 pm
CptCrusty1 (8/9/2011)
This "individual" is supposedly very skilled in SS2K8, but then asks me stuff like this...Ok... I have a proc now:
sproc_blah (@table varchar(5), @ID int, @returned_Stuff INT OUTPUT)
HOw the heck do I call it in a query?
If you mean in the sense of
SELECT col1, col2, EXEC Proc (params) FROM table
or
SELECT col1, col2 FROM EXEC Proc
Same answer as before. Cannot be done.
I think this needs to go back to your 'senior' for a complete redesign.
You do realise you have a SQL injection vulnerability here. Hard to pull off in a varchar(5), but that doesn't make it safe.
p.s. I also make a good living fixing this kind of mess and sometimes even designing systems so that they actually work first time. The latter is rarer.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 9, 2011 at 1:25 pm
Are you the poisonous kind of Gila Monster?
I'm putting this aside as it's apparent I'm spinning my wheels, making little rocks out of big rocks, trying to drain the lake one cup at a time... etc.
Thanks for your assistance... :crazy:
August 9, 2011 at 1:31 pm
CptCrusty1 (8/9/2011)
Are you the poisonous kind of Gila Monster?
Depends what mood I'm in and how rude the questions are. Usually not though. 😀
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 9, 2011 at 1:33 pm
CptCrusty1 (8/9/2011)
Are you the poisonous kind of Gila Monster?I'm putting this aside as it's apparent I'm spinning my wheels, making little rocks out of big rocks, trying to drain the lake one cup at a time... etc.
Thanks for your assistance... :crazy:
She's the nice kind, with a black belt. So I'd stay out of arm's reach if you see her face turning :-D.
... never seen it happen here tho. So you should be safe!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply