Modifying UDF - any way to open with just plain text?

Question

Modifying UDF - any way to open with just plain text?

kathy o

Mr or Mrs. 500

Points: 588
More actions
December 29, 2008 at 4:14 pm

#197975

I'm trying to modify many user defined functions in SSMS (2005). When I right click on the function and select 'Modify' or 'Script function as...', the text displayed in the editing screen looks as if it is dynamic SQL (2 single quotes instead of 1 for text, execute sp_executesql at the beginning, etc.). Is there a way to open a UDF for editing without the dynamic sql stuff?
Thanks in advance-

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

yeah that's deeply annoying...I'd like to switch it back as well;

my alternative is to sp_helptext functionname/proc/view to avoid the dynamic sql style...but that's a bandaid, not a solution

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Etni Valued Member Points: 68 More actions · Answer 2

which version are you using? I've seen that before but not on the version i currently use, i'm on my laptop right now and this is what i get from the help... about screen.

Microsoft SQL Server Management Studio Express9.00.2047.00

Microsoft Data Access Components (MDAC)6.0.6001.18000 (longhorn_rtm.080118-1840)

Microsoft MSXML3.0 4.0 5.0 6.0

Microsoft Internet Explorer8.0.6001.18241

Microsoft .NET Framework2.0.50727.1434

Operating System6.0.6001

Etni

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

This happens when you have the "If EXISTS" option on.

[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]

kathy o Mr or Mrs. 500 Points: 588 More actions · Answer 4

Where can the 'if exists' option be turned off or on?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Its a scripting Option. Look in SSMS under Tools..Options.., select the "Scripting" tab, then scroll down to the "Include IF NOT EXISTS clause" option and set it to FALSE.

[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]

kathy o Mr or Mrs. 500 Points: 588 More actions · Answer 6

kathy o

Mr or Mrs. 500

Points: 588

December 30, 2008 at 9:10 am

#919099

Thank you, Thank you, Thank you!!!!!

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Glad I could help. 🙂

[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]