Is something like this possible?:
DECLARE @test nvarchar(30) = 'Appt'
SELECT * FROM @test.dbo.tablename with (NOLOCK)
or possibly
DECLARE @test nvarchar(30) = 'Appt'
SELECT * FROM @test + '.dbo.tablename' with (NOLOCK)
No, right?
I'm not looking to put the whole string into a variable and then use "EXEC." I have long scripts that I want to just try to replace that with when it occurs.
Thank you.
This requires Dynamic SQL. Basically you create a string that builds the SQL statement dynamically based on your query. That string is then executed with an EXEC Statement. A basic example is like this:
DECLARE @test nvarchar(30) = 'Appt'
DECLARE @sql as varchar(max)
SET @SQL = 'SELECT * FROM' + @test + '.dbo.tablename' + 'with (NOLOCK)'
EXEC @SQL
Assuming your tablename was Appt.dbo.tablename
I think you meant:
SET @SQL = 'SELECT * FROM' + 'dbo.' + @test + ' with (NOLOCK)'
SELECT statements and concatenation to create the whole statement and exec at the end. Don't forget scope. Its more challenging to create a variable within the dynamic SQL. Also keep in mind when you execute a statement: SELECT * FROM ABCD INSERT INTO dbo.Something... That will execute together even if there are no spaces.
SET @SQL = 'SELECT * FROM' + 'dbo.' + QUOTENAME(@test) + ' with (NOLOCK)'. Also: EXEC (@SQL); (cf msdn.microsoft.com/en-us/library/ms188332.aspx)Nice explanation logixologist.
Because Tom wants to run it for multiple select statements, i was thinking he could store all the table names in a column and use a cursor to fetch the table values. Considering you would create a table first such as:
CREATE TABLE tableName ( name varchar(20));
then run the following code once you inserted the right values in the table above.
Declare @table VARCHAR(255)
DECLARE @sql as varchar(max)
SET @SQL = 'SELECT * FROM' + 'dbo.' + @table + ' with (NOLOCK)'
DECLARE table_cursor CURSOR FOR
select distinct([name]) FROM DBname.dbo.tableName
Where [name] is not null
OPEN table_cursor
FETCH Next from table_cursor
INTO @table
WHILE @@FETCH_STATUS =0
BEGIN
EXEC @SQL
FETCH NEXT FROM table_cursor
INTO @table
END
CLOSE table_cursor
DEALLOCATE table_cursor
The cursor would return the table name for the select statement. I think you could use the same logic for insert statements.
DoQuery(@test)and encapsulate theEXECcallreplaceon those strings to fill in any values than to dynamically assemble the whole thing. It sounds like your concern is primarily with ease of development/ maintenance. If you have a stringselect * from ##dbname##.dbo.tablename with (nolock), you can doreplace(your_string, '##dbname##, 'Appt')and then execute that. If you are just replacing database names, maybe you just needsp_MSforeachDB.