SQL Server 2008: Inserting Variables in DML Statements Using a Stored Procedure
I have the following procedure:
CREATE PROCEDURE [dbo].[Test1]
AS
BEGIN
INSERT INTO [My_Database].[My_Schema].[My_Table]
(...lists columns...)
SELECT ... lots of columns from joined query...
END
Instead of hardcoding "[My_Database]. [My_Schema]", now I want to select it as a variable from a predefined table like this:
CREATE PROCEDURE [dbo].[Test1]
AS
BEGIN
SELECT @myDB = [My_DB] FROM [my_custom_table]
--INSERT INTO [My_Database].[My_Schema].[My_Table]
INSERT INTO @myDB.[My_Table]
(...lists columns...)
SELECT ... lots of columns from joined query...
END
It doesn't work if I use it like above. I need to use: EXEC sp_executesql (whole_sql_statement_in_quotes)
My problem is that I have a lot of these routines to switch to using a variable instead of hardcoding. It will take forever to convert each expression to a long string.
Is there any other way to do this? What am I missing?
Hello
source to share
One idea: you could dump and recreate the synonym using dynamic SQL at the beginning of each procedure, then you can leave each Insert statement as Insert Into MySynonym
DROP SYNONYM MySynonym -- Must create it first before running this bit!
DECLARE @sql nvarchar(max)
SET @SQL = 'CREATE SYNONYM MySynonym
FOR ' + @myDB + '.test1'
EXEC sp_Executesql @sql
INSERT INTO MySynonym
SELECT ...
This will give you a code of code that you can copy to each SP. If the table you are inserting into is different for each SP, you can also declare that and create it in your CREATE SYNONYM statement
SET @SQL = 'CREATE SYNONYM MySynonym
FOR ' + @myDB + '.' + @MyTable
To truncate each table first, you will also need to use DynamicSQL, as you cannot drop a synonym
SET @SQL = 'Truncate Table ' + @MyTable
EXEC sp_Executesql @sql
source to share