CODEWITHSUNDEEP
t-sql
Sam
Sam

Reputation: 61

Use database with execute is right way?

Which will be the right way of using a database with execute?

Method 1:

declare @query nvarchar(max),  
        @db varchar(100) = 'figmdhqimanagementaad'  

set @query = 'select top 100 * from  [' + @db + '].dbo.tblencounter '  

execute Sp_ExecuteSQL @query

Method 2

Execute('use ' + @db + 'select top 100 * from tblencounter')

Upvotes: 2

Views: 40

Answers (1)

S3S
S3S

Reputation: 25132

I wouldn't say there is a right and wrong way in this case. It's Dynamic SQL and you have mitigated some SQL injection buy using brackets. I would use QUOTENAME on the second version.

declare @query nvarchar(max)
declare @db varchar(100) = 'figmdhqimanagementaad'  

set @query = 'USE ' + QUOTENAME(@db) + ' select top 100 * from tblencounter'

print(@query)

But beware, even this is subject to SQL injection if someone knows the DB name. You have mitigated this by making it only a varchar(100) though. You could always check to make sure the database exists too.

declare @query nvarchar(max)
declare @db varchar(100) = 'figmdhqimanagementaad'  

if exists(select * from sys.databases where name = @db)
begin
    set @query = 'USE ' + QUOTENAME(@db) + ' select top 100 * from tblencounter'

    print(@query)
end

Upvotes: 1

Related Questions