I checked the DOCs to remind myself
-- Stored Procedure
EXEC @retstat = dbo.MySProc @Param1, @Param2
SET @MySProc = 'dbo.MySProc'
EXEC @retstat = @MySProc @Param1, @Param2
-- Dynamic SQL
EXEC ('SELECT * FROM MyTable') -- Parenthesis are required
SET @MySQL = 'SELECT * FROM MyTable WHERE Col1 = 123'
SET @MySQL = 'SELECT * FROM MyTable WHERE Col1 = '
EXEC (@MySQL + '456') -- String concatenation only; expressions not permitted
SET @MyNVarcharSQL = 'SELECT * FROM MyTable WHERE Col1 = @Param1'
DECLARE @MyValue int = 789
EXEC sp_ExecuteSQL @MyNVarcharSQL, N'@Param1 int', @Param1 = @MyValue
Dynamic SQL can be Varchar or NVarchar, including (MAX), and the T-SQL string can be 'string' or N'string' ... which is a lot better than trying to concatenate numerous NVarchar(4000) variables on the EXEC line in the old days!!
There's quite a bit of stuff in DOCs that was new to me, or subtlety different. Perhaps it was in the Changes notes ... but it would be nice if there was DOCs with highlight / redlining showing the differences between versions - such that one could flip through reviewing only the changes. We write our product manuals like that ... takes ages! ... hopefully Clients appreciate it!
When did EXECUTE ... AT linked_server come in? DOCs say it was there in SQL 2005 even ... I've been faffing around with alternative ways of doing that - although maybe I'll still need the String & Gum methods!
'CREATE TABLE MyRemoteDB.dbo.MyRemoteTable
(Col1 int, Col2 varchar(10)) ;'
) AT MyRemoteServer
What about using EXECUTE on a (scalar) UDF? Why would I do that instead of using SELECT?
EXEC @returnstatus = dbo.MyScalarFunction @MyParam1 = 1;
is presumable the same as
SELECT @returnstatus = dbo.MyScalarFunction @MyParam1 = 1;
As of SQL2012 you can use EXECUTE to redefine the result set(s) - why would I do that? To change the name of a column that was returned perhaps?
EXEC MySProc @MyParam1 = 1
WITH RESULT SETS
-- Resultset 1
([New Label 1] int NOT NULL,
[New Label 2] nvarchar(50) NOT NULL,
... etc ...
-- Resultset 2
([New Label 1] nvarchar(50) NOT NULL,
[New Label 2] int NOT NULL,
... etc ...