SQL Spelling mistakes

Question

Post reply

SQL Spelling mistakes

JKSQL

SSCrazy Eights

Points: 8481
More actions
September 17, 2008 at 3:35 pm

#193627

I have customized about 200 stored procedures. There was a spelling mistake in some of them. I spelled a table incorrectly. Is there a way to have sql validate when I try and create the procedure. So that it throws an error instead of the programmer finding it and telling me.

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

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

Quatrei.X SSCrazy Points: 2697 More actions · Answer 1

Hi there,

Try this one... I hope it helps...

(Although this example uses dynamic SQL)

DECLARE @tableName VARCHAR(MAX)

SET @tableName='NotExistsingTable'

IF NOT EXISTS(SELECT [name] FROM sys.tables WHERE [name]=@tableName)

BEGIN

SELECT 'The table does not exists'

RETURN

-- Or just return an error value

END

EXEC ('SELECT * FROM ' + @tableName)

Anyway, if your doing it in stored sprocs where the tables are already defined, you may not use the EXEC dynamic SQL.

Please tell me if this was helpful or if it still needs some modifications... Thank you! ^__^

_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!

"Press any key...
Where the heck is the any key?
hmmm... Let's see... there's ESC, CTRL, Page Up...
but no any key"
- Homer Simpson

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

Yeaup... test the first sproc you changed before you promulate the change to 199 others. Simple testing is the best verification tool there is. And, in this case, if you create the proc and then write an exec to call it, and just do a Display Estimated Execution plan, it's almost as good as running it.

If the sproc is calling a new table and the original mistake was naming the table incorrectly, nothing can be done to help prevent that unless you copy your code into word and have it spell check for you. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

Or, write all your code in a syntax checking tool like Visual Studio Team System Database Edition. I can't even save a script with bad syntax without getting an alert to the problem. It prevents a lot of silly mistakes (the kind I'm prone to).

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

JKSQL SSCrazy Eights Points: 8481 More actions · Answer 4

Thanks for the help. I am not sure how the cursor would work since I am dealing with stored procedures. There can be many calls to different tables within my sp's. I have played with database edition and it validates the same way SQL does. If your table is misnamed then it basically does not care about checking anything within the statement. If the table is named correctly then it will look through the schema. It will catch fields and syntax problems. I guess I am going to just have to learn how to spell. I HATE SPEALING 🙂

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

SQL Server does deferred name resolution because you can create a proc before a table or you can create a table within your proc.

There is actually an issue on Connect, https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=260762&wa=wsignin1.0, that requests the ability to have Set OPTIONS for checking.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

JKSQL SSCrazy Eights Points: 8481 More actions · Answer 6

That would be a great feature if I could turn something like that on. Strict_Check. I hope that SPXX will have something like that.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Jeff Moden (9/17/2008)
And, in this case, if you create the proc and then write an exec to call it, and just do a Display Estimated Execution plan, it's almost as good as running it.

This is what I usually use, ad-hoc. Just write/edit the Sproc, execute it to CREATE/ALTER the sproc, then select the sProc name (after the CREATE PROC ...) and press ctrl-L. Catches almost all static SQL name resolution issues.

Dynamic SQL is another story of course...

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