Incorrect tsql syntax

The below returns incorrect syntax near 'db_owner'. I'm not sure how to fix. Please help:

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

SET @dbName = 'TSQL2012';
SET @sql = 'EXEC sp_addrolemember ''db_owner'', ''testuser''';

SET @BigSQL = 'USE ' + @dbName + '; EXEC sp_executesql N''' + @sql + '''';
EXEC (@BigSQL)
barnescoAsked:
Who is Participating?
 
Brendt HessConnect With a Mentor Senior DBACommented:
Your problem is not enough single quotes.  As written, the value in @BigSQL to be executed is:

USE TSQL2012; EXEC sp_executesql N'EXEC sp_addrolemember 'db_owner', 'testuser''

Where the bolded items are not quoted.  To fix this is simple, but tiresome when counting single quotes.  Just replace:

SET @sql = 'EXEC sp_addrolemember ''db_owner'', ''testuser''';

With:

SET @sql = 'EXEC sp_addrolemember ''''db_owner'''', ''''testuser''''';

And @BigSQL now equals:

USE TSQL2012; EXEC sp_executesql N'EXEC sp_addrolemember ''db_owner'', ''testuser'''
0
 
PortletPaulfreelancerCommented:
remove the semi-colons (at the end of each line)
0
 
chaauCommented:
You cannot use 'USE' inside EXEC command. Instead, you need to use a dotted command, like this:
DECLARE @sql nvarchar(4000);
DECLARE @BigSQL nvarchar(4000);
DECLARE @dbName varchar(100);

SET @dbName = 'TSQL2012';
SET @sql = @dbName' + ..sp_addrolemember ''db_owner'', ''testuser''';

EXEC (@sql) 

Open in new window

0
 
barnescoAuthor Commented:
That's the for the assist. Works great.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.