question

Simonn avatar image
Simonn asked

Database USE issues with dynamic SQL

Hi All

I want to create a stored procedure that creates a new database with tables, triggers and its own set of stored procedures each time the stored procedure is executed. Creating the tables is easy but now trying to create a trigger I get a error saying that I'm not using the correct database and if I try to include the USE statement in the Create trigger it returns a error saying the CREATE TRIGGER statement needs to be first in the Query. Please help me as this is critical. Please see code below. Thank you in advance

DECLARE @Retailer VARCHAR(30)

SET @Retailer = 'RetailerName'
SET @Retailer = '[OTA_' + @Retailer + ']'

DECLARE @CreateVoucherMoveTrigger NVARCHAR(4000)

SET @CreateVoucherMoveTrigger = 
'USE ' + @Retailer + '
GO

CREATE TRIGGER [dbo].[UpdateVoucherStock]
   ON '+ @Retailer +'.[dbo].[TempVoucherUpload]
   AFTER INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for trigger here
INSERT INTO ' + @Retailer + '.[dbo].[VoucherStock]
           ([Network]
           ,[RatePerProvider]
           ,[SumOfVoucher]
           ,[Status]
           ,[Chain]
           ,[DateTimeStamp])
SELECT (LEFT(TVP.[ProductCode],(CHARINDEX(''_'',TVP.[ProductCode])-1))) AS [Network]
      ,RIGHT(TVP.[ProductCode],(LEN(TVP.[ProductCode])-CHARINDEX(''_'',TVP.[ProductCode]))) AS [RatePerProvider]
      ,TVP.[Vouchers]
      ,TVP.[Status]
      ,TVP.[Chain]
      ,TVP.[ReportDate]
  FROM ' + @Retailer + '.[dbo].[TempVoucherUpload] AS TVP
    LEFT JOIN ' + @Retailer + '.[dbo].[VoucherStock] AS VS
    	ON (LEFT(TVP.[ProductCode],(CHARINDEX(''_'',TVP.[ProductCode])-1))) = VS.[Network]
    	AND RIGHT(TVP.[ProductCode],(LEN(TVP.[ProductCode])-CHARINDEX(''_'',TVP.[ProductCode]))) = VS.[RatePerProvider]
    	AND TVP.[Chain] = VS.[Chain]
    	AND TVP.[ReportDate] = VS.[DateTimeStamp]
WHERE VS.[Network] IS NULL

DELETE FROM ' + @Retailer + '.[dbo].[TempVoucherUpload]

END
GO'
PRINT @CreateVoucherMoveTrigger
execute sp_executesql @CreateVoucherMoveTrigger

Weird thing is the Message returned when placed in a new query window it works perfectly?

sqldatabasedatabase-designdynamic
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tom Staab avatar image
Tom Staab answered

You cannot use "GO" inside your dynamic SQL (because, as Håkan said earlier, sp_executesql doesn't support multiple batches). However, if you change the database before running the stored procedure, it will execute with that as the current database. The problem in your case is that even the database name is dynamic. So, I came up with a solution that uses nested dynamic SQL.

DECLARE @sql nvarchar(4000);
DECLARE @BigSQL nvarchar(4000);
DECLARE @dbName varchar(100);

SET @dbName = 'master';
SET @sql = 'SELECT DB_NAME()';

SET @BigSQL = 'USE ' + @dbName + '; EXEC sp_executesql N''' + @sql + '''';
EXEC (@BigSQL)

In your case, your CREATE TRIGGER code would go in the @sql variable, and obviously the database name would go in @dbName.

Good luck, and thanks for the challenge. :)

In response to Håkan's comment, here's a longer example that creates a stored procedure, executes it, drops it and then attempts to execute it again (which intentionally fails).

DECLARE @EndLine nchar(2); SET @EndLine = NCHAR(13) + NCHAR(10);
DECLARE @sql nvarchar(4000);
DECLARE @BigSQL nvarchar(4000);
DECLARE @dbName varchar(100);

SET @dbName = 'master';
--SET @sql = 'SELECT DB_NAME()';

SET @sql =
    N'IF OBJECT_ID(''''dbo.GetDatabaseName'''') is not null' + @EndLine +
    N'	DROP PROCEDURE dbo.GetDatabaseName'
SET @BigSQL = 'USE ' + @dbName + '; EXEC sp_executesql N''' + @sql + '''';
PRINT @BigSQL
EXEC (@BigSQL)

SET @sql =
    N'CREATE PROCEDURE dbo.GetDatabaseName' + @EndLine +
    N'AS' + @EndLine +
    N'BEGIN' + @EndLine +
    N'	SELECT DB_NAME();' + @EndLine +
    N'END'
SET @BigSQL = 'USE ' + @dbName + '; EXEC sp_executesql N''' + @sql + '''';
PRINT @BigSQL
EXEC (@BigSQL)

SET @BigSQL = 'USE ' + @dbName + '; EXEC dbo.GetDatabaseName';
PRINT @BigSQL
EXEC (@BigSQL)

SET @sql =
    N'IF OBJECT_ID(''''dbo.GetDatabaseName'''') is not null' + @EndLine +
    N'	DROP PROCEDURE dbo.GetDatabaseName'
SET @BigSQL = 'USE ' + @dbName + '; EXEC sp_executesql N''' + @sql + '''';
PRINT @BigSQL
EXEC (@BigSQL)

SET @BigSQL = 'USE ' + @dbName + '; EXEC dbo.GetDatabaseName';
PRINT @BigSQL
EXEC (@BigSQL)

Here's the "Messages" output:

USE master; EXEC sp_executesql N'IF OBJECT_ID(''dbo.GetDatabaseName'') is not null
    DROP PROCEDURE dbo.GetDatabaseName'
USE master; EXEC sp_executesql N'CREATE PROCEDURE dbo.GetDatabaseName
AS
BEGIN
    SELECT DB_NAME();
END'
USE master; EXEC dbo.GetDatabaseName

(1 row(s) affected)
USE master; EXEC sp_executesql N'IF OBJECT_ID(''dbo.GetDatabaseName'') is not null
    DROP PROCEDURE dbo.GetDatabaseName'
USE master; EXEC dbo.GetDatabaseName
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'dbo.GetDatabaseName'.

The "Results" output is just "master".

2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 Excellent solution, I was wondering if nesting the statements could be a solution, but I didn't dare to suggest it. :) Did it work with CREATE TRIGGER or CREATE PROCEDURE?
2 Likes 2 ·
In response to Håkan's question, I updated my answer to show a longer example that creates a stored procedure. To directly answer your question, yes it worked. :) I gave your comment a +1 both because I thought it was a good comment and because that will let you know that I responded (if you check your activity).
2 Likes 2 ·
Fatherjack avatar image
Fatherjack answered

Simply add a GO statement after the USE command eg:

Use Adventureworks
GO
Create trigger <yourtriggername> on <tablename>
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Returns the following.... (Might be because I'm using dynamic code?) Msg 102, Level 15, State 1, Line 2 Incorrect syntax near 'GO'. Msg 111, Level 15, State 1, Line 4 'CREATE TRIGGER' must be the first statement in a query batch. Msg 102, Level 15, State 1, Line 38 Incorrect syntax near 'GO'.
0 Likes 0 ·
Ah, just noticed your syntax, you'll need to try using sp_executesql as: DECLARE @sql NVARCHAR(20) DECLARE @db NVARCHAR(10) SET @db = 'Adventureworks' SET @sql = 'Use ' + @db EXEC sp_executesql @sql
0 Likes 0 ·
Still gives me the same error Cannot create trigger on 'OTA_RetailerName.dbo.TempVoucherUpload' as the target is not in the current database.
0 Likes 0 ·
Håkan Winther avatar image
Håkan Winther answered

I have tried implementing "USE" in dynamic SQL with sp_executeSQL but didn't find any solution for it. The only solution was to use "fully qualified" name, that is, databasename.schemaname.objectname. In your case, try to use :

'CREATE TRIGGER ' + @retailer + '[dbo].[UpdateVoucherStock]   ON ' 
+ @Retailer +'.[dbo].[TempVoucherUpload]

I am not sure it will work for triggers, but it works for other objects. Personally I would avoid triggers unless I have to, because of the performance impact, but I know there are some benefits with triggers if you can't limit direct access to the tables.

2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Seems like I will have to specify the USE database statement as I still get and error with the code supplied above. Msg 166, Level 15, State 1, Line 1 'CREATE/ALTER TRIGGER' does not allow specifying the database name as a prefix to the object name.
0 Likes 0 ·
I was afraid of that! Triggers are a little special becuase it is not a "standalone" object, but it was worth a try I guess.
0 Likes 0 ·
Håkan Winther avatar image
Håkan Winther answered
DECLARE @sql AS NVARCHAR(4000)
SET @SQL ='USE SQLTools;
select * from sys.procedures;
create table dbo.x (id int NOT NULL);'
execute sp_executesql @sql

This works for me, but it doesn't include a trigger, and if I add a trigger it doesn't work.

You can read more about rules for batches at this page:

http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1161826_mem1,00.html

3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Keeps complaining that the CREATE TRIGGER statement should be the first in the batch Msg 111, Level 15, State 1, Line 3 'CREATE TRIGGER' must be the first statement in a query batch.
0 Likes 0 ·
Yes it does, because according to the rules I posted it is not possible to specify any statement before create trigger, and you can't have multiple batches in sp_executeSQL
0 Likes 0 ·
Need to find a way to get this working. Is there no way of letting the EXEC statement take affect on your current session?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.