Seems to me like a bug somewhere. Either you are allowed to alter a schema-bound function to recurse (in which case Try2 should work the same as Try3) or you are not, because ALTER is actually working like DROP...CREATE (in which case Try3 should not work and Try4 should return a different error)
Upshot is: you can get around the restriction on schema-binding a recursion by using CREATE OR ALTER twice.
You cannot use a calculated string directly in a BACKUP or RESTORE statement.
But contrary to popular understanding, you can use a variable, you do not need dynamic SQL.
DECLARE @file nvarchar(255) =
'G:\SP_Backup_Daily\iMIS_Prod_' +
CONVERT(nvarchar(50), GETDATE(), 112) +
'.bak';
...
@ErikDarling Argh I thought I made it clear. Variables != dynamic SQL. You can use variables on all parts of a BACKUP clause without using sp_executesql
In other words, doing BACKUP DATABASE @db TO DISK = @file is not dynamic SQL. SET @SQL = 'BACKUP DATABASE ' + @db + ' TO ...' EXEC(@SQL) is dynamic.
@HannahVernon I find it hard to believe that one can be a good moderator of 8 groups - some of them heavy-duty ( Chess, English Language Learners, Spanish Language).
@PaulWhite I saw that but didn't feel it was clear enough or stressing enough the fact you don't need dynamic SQL. The amount of times I see badly constructed dynamics is unreal.
@ErikDarling No, dynamic SQL is dynamic code ie the code itself is dynamically produced. By your same argument every parameterized query is dynamic, which is obviously nonsense.
Yes the file name is constructed dynamically, but there is little meaning to it apart from the \ symbols, there is no mixing of code and data there
It does 'work' in as much as it selects the same formatting as the tsql tag, which like most tags, has lang-sql as its syntax highlighting style. That same style is the default for the whole site.
@Charlieface Yes, that's how it is in the question. That's not the point I'm making.
@J.D. Agreed, but only in the very technical sense that it's dynamically parse and executed at runtime. It's not really dynamic in the overall "this is going to wipe your database" sense.
Yea I know whatcha mean. I just wanted to give a counter example to "the code itself is dynamically produced". But I also agree that just because a variable is used doesn't necessarily make it dynamic SQL either.
But in the interest of moving on... Hempy Easter? 🥬🚬
TL;DR; Is it a bug that SQL Server allows a scalar UDF to recursively call itself when schema-bound, but only when altered to do so using the CREATE OR ALTER syntax? Or is it a bug that other syntaxes are disallowed?
A trivial recursive scalar UDF can be constructed as follows
CREATE FUNCTION db...
