On twitter Adam Machanic posted a question
if anyone has a document that details what a valid variable name is, what is allowed and what is not allowed.
Did you know that you can have a variable named @@ or @@@@?
DECLARE @ int=5,@@ int
SET @@=@
DECLARE @@@@ int=@*@@
DECLARE @$$ int=@@@@
SELECT @$$,@@@@
Running that will print 25 25
There is a Books On Line page that gives some details but it is not very explicit, you can find that page here
Identifiers
But what is valid? Let's write a quick SQL script, we are going to use the spt_values in the master database to quickly generate a SQL statement which we can execute
SELECT number,'declare @' + CHAR(CONVERT(VARCHAR(5),number)) +' int' AS stmt
INTO #test
FROM master..spt_values WHERE type='p'
SELECT * from #test
First step is to dump what we want into a temporary table
Running that will give you something like this if you have the output in grid not text
Don't be fooled by that the first 33 rows, those will be generated as @ and will appear valid, those are non printable characters
So let's continue with our SQL script after you created the temp table, run the following, if there is an error, the script will catch it and print the character number
DELETE #test WHERE stmt IS NULL
DELETE #test WHERE number < 33
DELETE #test WHERE stmt like '%''%' OR stmt LIKE '"'
DECLARE @stmt VARCHAR(100)
DECLARE @start int = 0,@end int =(SELECT max(number) from #test)
WHILE @start <= @end
BEGIN
SELECT @stmt = stmt from #test where number = @start
BEGIN TRY
EXEC(@stmt)
END TRY
BEGIN CATCH
PRINT 'error ' + right('000' + CONVERT(VARCHAR(3),@start),3) + ' ' + @stmt
END CATCH
SET @start+=1
END
Running that code will give you the following output
error 033 declare @! int
error 034 declare @" int
error 037 declare @% int
error 038 declare @& int
error 039 declare @' int
error 040 declare @( int
error 041 declare @) int
error 042 declare @* int
error 043 declare @+ int
error 044 declare @, int
error 045 declare @- int
error 046 declare @. int
error 047 declare @/ int
error 058 declare @: int
error 059 declare @; int
error 060 declare @< int
error 061 declare @= int
error 062 declare @> int
error 063 declare @? int
error 091 declare @[ int
error 092 declare @\ int
error 093 declare @] int
error 094 declare @^ int
error 096 declare @` int
error 123 declare @{ int
error 124 declare @| int
error 125 declare @} int
error 126 declare @~ int
error 127 declare @ int
error 128 declare @€ int
error 129 declare @ int
error 130 declare @‚ int
error 132 declare @„ int
error 133 declare @… int
error 134 declare @† int
error 135 declare @‡ int
error 136 declare @ˆ int
error 137 declare @‰ int
error 139 declare @‹ int
error 141 declare @ int
error 143 declare @ int
error 144 declare @ int
error 145 declare @‘ int
error 146 declare @’ int
error 147 declare @“ int
error 148 declare @” int
error 149 declare @• int
error 150 declare @– int
error 151 declare @— int
error 152 declare @˜ int
error 153 declare @™ int
error 155 declare @› int
error 157 declare @ int
error 161 declare @¡ int
error 162 declare @¢ int
error 163 declare @£ int
error 164 declare @¤ int
error 165 declare @¥ int
error 166 declare @¦ int
error 167 declare @§ int
error 168 declare @¨ int
error 169 declare @© int
error 171 declare @« int
error 172 declare @¬ int
error 173 declare @ int
error 174 declare @® int
error 175 declare @¯ int
error 176 declare @° int
error 177 declare @± int
error 178 declare @² int
error 179 declare @³ int
error 180 declare @´ int
error 182 declare @¶ int
error 183 declare @· int
error 184 declare @¸ int
error 185 declare @¹ int
error 187 declare @» int
error 188 declare @¼ int
error 189 declare @½ int
error 190 declare @¾ int
error 191 declare @¿ int
error 215 declare @× int
error 247 declare @÷ int
So those are all invalid, as well as the single and double quote and everything between characters 0 and 32 since those are not really printable but how would you even use those?
So there you have it a quick and dirty script to test what is valid....
Exercise for you...expand this to use unicode......
