Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Wednesday, 11 December 2013

Use database inside stored procedure

 Use database inside stored procedure

I am thinking about an important funcitonality or property in sql server.
Is it possible to give the database name dynamically in sql server?
OOps, i think we can achieve this using dynamic sql.
I have made a try on this. Lets see,
use master
declare @sql nvarchar(max)
select @sql = 'use'
select @sql = @sql + ' ' +
select * from dbo.Employees -- Northwind table
exec sp_executesql @sql
Its not working. I have tried to analyse this problem, its because once the query gets executed its coming out of that particular scope and enter into the public scope.

The work around for this problem is, we need to

use tempdb
declare @sql nvarchar(1000)
declare @dbname varchar(40)
select @dbname = 'Northwind'
select db_name() --tempdb
select @sql = 'use'
select @sql = @sql + ' '+@dbname +' select * from dbo.Employees
select db_name()'
exec sp_executesql @sql --northwind
select db_name() --tempdb