Is there any way to turn of parsing?

Question

Is there any way to turn of parsing?

Dennis Post

SSCarpal Tunnel

Points: 4473
More actions
September 4, 2013 at 6:55 am

#297411

Hi,
I'm writing a script that will gather server information from different versions of SQL, starting from 2005.
I cannot run the following query due to one or the other column not existing in the current version of SQL.
SELECTCASE
WHEN RIGHT(LEFT(@@VERSION, 25), 4) >= 2012
THEN ROUND(1.0 * Physical_Memory_kb / 1024 / 1024, 2)
ELSE ROUND(1.0 * Physical_Memory_in_bytes / 1024 / 1024 / 1024, 2)
END MemGB
FROMsys.dm_os_sys_info
Is it possible to turn off parsing in a HINT or SET?
Couldn't find anything here, but maybe someone somewhere knows a sneaky trick
http://technet.microsoft.com/en-us/library/ms190356.aspx
http://technet.microsoft.com/en-us/library/ms187713.aspx
I can of course create the queries dynamically, but am hoping to avoid this if possible.
Thanks!
BTW, how can I alias the URLs?

For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.[/url]
"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort

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

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

PiMané SSChampion Points: 13965 More actions · Answer 1

Hi,

It's only possible to create SPs that reference objects, columns, that don't exist... but it's the only exception on SQL... Executing the SP will throw an error.

The only way is to use EXEC with your statement:

IF @@VERSION LIKE '%2005%'

BEGIN

EXEC('SELECTCASE

WHEN RIGHT(LEFT(@@VERSION, 25), 4) >= 2012

THEN ROUND(1.0 * Physical_Memory_kb / 1024 / 1024, 2)

ELSE ROUND(1.0 * Physical_Memory_in_bytes / 1024 / 1024 / 1024, 2)

END MemGB

FROMsys.dm_os_sys_info')

END

Pedro

If you need to work better, try working less...

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 2

Pedro's method would be the only thing. The script itself needs to be parsed in order to generate a plan, and it can't do it if the columns don't exist. The same reason why select * from @myparameter doesn't work.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

Dennis Post (9/4/2013)
BTW, how can I alias the URLs?

If you do it manually, you can use the following syntax (without the space).

[ url=http://technet.microsoft.com/en-us/library/ms190356.aspx]SET Statements (Transact-SQL)[/url]

Otherwise, you can write the alias, select it and click on the hyperlink icon on top of the text editor and past the url in there (some browsers might give you some trouble with it).

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Dennis Post SSCarpal Tunnel Points: 4473 More actions · Answer 4

Thanks for your comments guys.

Unfortunately PiMané's suggestion doesn't work as it is still getting parse and failing due to the unknown column name.

IF @@VERSION LIKE '%2005%'

BEGIN

EXEC('SELECTCASE

WHEN RIGHT(LEFT(@@VERSION, 25), 4) >= 2012

THEN ROUND(1.0 * Physical_Memory_kb / 1024 / 1024, 2)

ELSE ROUND(1.0 * Physical_Memory_in_bytes / 1024 / 1024 / 1024, 2)

END MemGB

FROMsys.dm_os_sys_info')

END

ELSE

BEGIN

PRINT 'Didn''t parse'

END

But if you guys say that it can only be done dynamically then i'll do something like this:

DECLARE @SQL Varchar(1000)

SET @SQL = '

SELECT ' +

CASE

WHEN RIGHT(LEFT(@@VERSION, 25), 4) >= 2012

THEN + 'ROUND(1.0 * Physical_Memory_kb / 1024 / 1024, 2) MemGB '

ELSE + 'ROUND(1.0 * Physical_Memory_in_bytes / 1024 / 1024 / 1024, 2) MemGB '

END +

'FROM sys.dm_os_sys_info'

EXEC(@SQL)

Will probably have to use a TempTable to catch the value.

For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.[/url]

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort

Dennis Post SSCarpal Tunnel Points: 4473 More actions · Answer 5

If you do it manually, you can use the following syntax (without the space).
[ url=http://technet.microsoft.com/en-us/library/ms190356.aspx]SET Statements (Transact-SQL)[/url]

Thanks Luis!!

For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.[/url]

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort

PiMané SSChampion Points: 13965 More actions · Answer 6

Sorry, didn't notice the CASE in the SELECT.

You have to change it to something like this:

IF RIGHT(LEFT(@@VERSION, 25), 4) >= 2012

BEGIN

EXEC('SELECTROUND(1.0 * Physical_Memory_kb / 1024 / 1024, 2) FROMsys.dm_os_sys_info')

END

ELSE

BEGIN

EXEC ('SELECTROUND(1.0 * Physical_Memory_in_bytes / 1024 / 1024 / 1024, 2)FROMsys.dm_os_sys_info')

END

If you need to work better, try working less...