Database Name as Variable -Dynamic SQL inside stored procedure

Question

I'm having a hard time to solve this issue. My project requirement need to be able to use database name as a parameter. I thought it will be easy since in a stored procedure, we can use it like [dbname].[dbo].tblname. But when I replace the dbname with variable, it's not working.

So after sometime googling, I decided to go with dynamic SQL in the stored procedure. But, currently I'm having a hard time to debug this part. On my program it raise a syntax error. I'm hoping if someone could give me a hint, or a better idea for my project. Thanks in advance!

  SELECT @DBName=DBName FROM Client WHERE ClientCode = @ClientCode

  DECLARE @SQL NVARCHAR(MAX) = N'SELECT * FROM ' + @DBName + 
       N'.[dbo].Users AS A INNER JOIN'+ @DBName  + 
       N'.[dbo].UserRoles AS B On B.RoleCode = A.UserRole INNER JOIN ' + @DBName + 
       N'.[dbo].Branch AS C On C.BranchCode = A.BranchCode WHERE Username= ' + @UserName + 
       N' AND Password = ' + @Password 

  Declare @ParamDefinition AS NVarchar(2000) 
  Set @ParamDefinition =      N'   @ClientCode VARCHAR(20),' +
                              N' @UserName Varchar(15),' +
                              N' @Password NVARCHAR(200)' 

   exec sp_executesql @SQL,@ParamDefinition

rather than execing, print the sql, take the printed sql, parse it, if no errors, run it. It will give you a better idea of where to start. — Kritner
– Kritner, Commented Aug 21, 2015 at 16:44
Start with something simpler such as '@sql = select count(*) records from ' + @DBName + 'dbo.users where 1=2'. — Dan Bracuk
– Dan Bracuk, Commented Aug 21, 2015 at 16:48

Community · Accepted Answer · 2017-03-17 10:45:55Z

1

use QUOTENAME in your dynamic SQL, this is very important to prevent SQL Injection
username and password can and should stay parameters
pass in the declared parameters

So like this:

DECLARE @SQL NVARCHAR(MAX) = N'SELECT * FROM ' + QUOTENAME(@DBName) + 
       N'.[dbo].Users AS A INNER JOIN '+ QUOTENAME(@DBName)  +  
       N'.[dbo].UserRoles AS B On B.RoleCode = A.UserRole INNER JOIN ' + QUOTENAME(@DBName) + 
       N'.[dbo].Branch AS C On C.BranchCode = A.BranchCode WHERE Username= @UserName AND Password = @Password'

   exec sp_executesql @SQL, N'@UserName VARCHAR(15), @Password NVARCHAR(200)', @UserName, @Password

Finally: never store passwords in the database. Use instead a salted hash.

edited Mar 17, 2017 at 10:45

CommunityBot

11 silver badge

answered Aug 21, 2015 at 17:15

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Muhammad Nazri Over a year ago

it works..this is the most complete answers.and thanks for the tips =)

Joe R · Accepted Answer · 2015-08-21 16:50:33Z

1

You are missing a space on the second line of your dynamic SQL generation.

DECLARE @SQL NVARCHAR(MAX) = N'SELECT * FROM ' + @DBName + 
   N'.[dbo].Users AS A INNER JOIN ' + @DBName  + -- space added
   N'.[dbo].UserRoles AS B On B.RoleCode = A.UserRole INNER JOIN ' + @DBName + 
   N'.[dbo].Branch AS C On C.BranchCode = A.BranchCode WHERE Username= ' + @UserName + 
   N' AND Password = ' + @Password

answered Aug 21, 2015 at 16:50

Joe R

111 bronze badge

Comments

Juan Carlos Oropeza · Accepted Answer · 2015-08-21 16:58:44Z

0

I rewrite like this so is easy to read

 DECLARE @SQL NVARCHAR(MAX) = N'SELECT * FROM ' + 
   @DBName + N'.[dbo].Users AS A INNER JOIN ' + 
                                          ^^^ space add here 
   @DBName + N'.[dbo].UserRoles AS B On B.RoleCode = A.UserRole INNER JOIN ' +
   @DBName + N'.[dbo].Branch AS C On C.BranchCode = A.BranchCode ' + 
   N' WHERE Username= ' + @UserName + 
   N' AND Password = ' + @Password

and like Joe R say, was a missing space.

edited Aug 21, 2015 at 16:58

answered Aug 21, 2015 at 16:49

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Collectives™ on Stack Overflow

Database Name as Variable -Dynamic SQL inside stored procedure

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related