Recently, I’ve noticed a few tables @ work that has static information and is being reference from mutliple sprocs. We have a table called Parameters. In this table, it would have values, like I said, its static and only 1 row can exist. Let’s take replication, for example:
[column] [value]
distributor serverA
publisher serverB
subscriber serverC
So in our script, to set a value to a variable:
Declare@distro varchar(50)
, @publ varchar(50)
, @subs varchar(50)select @distro = distributor
, @publ = publisher
, @subs = subscriber
from database1.parameters
So that’s one way right there. Next one is a bit different:
select @rows = @@rowcounts, @err =@@error, @obj='some text here', @dt = getdate()
Last but not least:
set @query = 'osql /s + @distro + ' /d database1 + ' /q "select * from ' + @dbname + '.' + @tblname + ' where .... '