February 20, 2009 at 11:26 am
I'm trying to do something like string substitution inside a UDF, and I have a varchar variable @E that contains an expression like
@ShortName
CONVERT(varchar(20),@TheDate,102)
or somesuch, and I want to get the results of executing this into a variable so the variable gets what's in @ShortName or maybe the formatted datetime...
Maybe like
SELECT @Result = EXEC(@E)
Or maybe
EXEC( SELECT @Result = @E)
I just haven't found the magic incantation.
[font="Arial"]Halfbubble ------------
You just can't do this stuff unless you're at least half a bubble off center.[/font]
February 20, 2009 at 11:40 am
You can't exec inside a UDF. No dynamic SQL in them.
What's the end result you're trying to achieve?
- 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
February 20, 2009 at 12:10 pm
I was just figuring that out 😀 MS kindly thinks I shouldn't be using EXEC inside a function...
I thought I'd simplify the task of writing a big stored proc by separating out this big string munching operation into a function that returns the munched string.
Doing sort of a mailmerge to email, where I have a template string kindof like:
Hello {CustomerName},
I'm spamming you to let you know you have an outstanding balance of {AccountBalance}, you lazy deadbeat!
Of course, the users don't want to mess with SQLish. Those that do want to mess with SQLish likely know more about it than I do;)
So I have a table with rows like:
CustomerName RTRIM( @LastName + ', ' + @FirstName + ' ' + @MiddleName)
AccountBalance CONVERT(varchar(20),@AccountBalance)
So, I have a loop that uses CHARINDEX and SUBSTRING to find the tags in the template. I look each up in the table to get the SQL expression. So then I need to evaluate the sql expression to get the resulting string to stick into the result text.
Then it's off to database mail with the results.
I'm moving an operation we used to do in Clarioneese to the server. Likely in a stored procedure.
[font="Arial"]Halfbubble ------------
You just can't do this stuff unless you're at least half a bubble off center.[/font]
February 20, 2009 at 12:22 pm
I've built a couple of systems like that. One was really simple. I gave the users a list of the parameters they could choose, and hard-coded those into the proc that would build the e-mail. They could write a subject and body, and could use place-holders in them for the "merge fields", with square-brackets to indicate merge-fields.
They only had a dozen or so options on the merge fields, but they could write them into whatever text they wanted for the e-mail and subject.
Then I would take the list of messages in the queue, and run a proc once for each one to build the outbound e-mail and send it with sp_send_dbmail. One of the few cursors I've ever used in a production proc.
The other system, the merge fields could be defined in XML format, including which table they were pulled from, what column name, what parameter(s) to use from the main proc to determine which rows to use in the source table, and any optional extras to the Where clause. Took more training to get people to build their merge definitions, but was very, very flexible and extensible.
Then I used a "quirky update" to build a single dynamic string that would do the individual calls to sp_send_dbmail, and send the e-mails.
The second one was very complex to build, and involved a lot of dynamic SQL, but it was fast and powerful and allowed for much more flexible merge data. Could even have been used to pull data out of web services and such with a little bit more work.
I'd go with something resembling the first option, if I were you.
Build a list of allowed merge fields. Account number, name, amount owed, due date, number of missed payments. That might be an adequate list.
Then build a proc that takes a message queue ID and pulls the "ToAccount" from it, and uses that to build the message based on the message text and the merge fields desired, and then sends it.
- 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
February 20, 2009 at 1:10 pm
Half Bubble (2/20/2009)
MS kindly thinks I shouldn't be using EXEC inside a function...
Functions are not allowed to cause side effects. That restriction is checked when the function is created. Since there's no way to tell, at that point, what dynamic SQL will do, it is not allowed.
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
February 22, 2009 at 1:47 pm
[font="Verdana"]I've done this sort of thing quite often.
Here's my suggestion on how to approach it.
Define a table that lists your "variables", and calculate the current value of the variables. Then use replace() to replace the references in strings with the actual values.
And yes, you can do this set-based! I even got it to where I could have variables based on other variables.
[/font]
February 22, 2009 at 2:12 pm
The point is: you can do this, you just cannot do it inside a UDF. You can do everything else that you mention in your UDF, but you cannot EXEC(..) the dynamic SQL in there.
However, you can do the dynamic SQL in a stored procedure just fine. A couple of notes though:
1) If you want to return a value, then you want to use SP_EXECUTESQL() instead which can return values in OUTPUT parameters.
2) You should be concerned about SQL Injection and how to protect your dynamic SQL from it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 23, 2009 at 6:23 am
Given that there are only a couple dozen options here, I'm using a big CASE.
[font="Arial"]Halfbubble ------------
You just can't do this stuff unless you're at least half a bubble off center.[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply