For example, if you want to run one script against multiple servers, instead of modifying the script for each server, you can use a scripting variable for the server name.

You can join to a UDF. Using table variables SQL Server 2000 has a new Table data type that lets you declare local variables as type table. The table type is available only via a UDF.

In this, Part 4 of a series on programming T-SQL, Gregory Larsen details how to set variables in calling T-SQL code while using sp_executesql.