April 15, 2016 at 6:48 am
Hello Team,
I need one help here .
I am preparing one Stored proc for different object access. I am taking database name as variable @db
now I want to use the database provided in SP input as
USE @db in stored procedure. I cannot pass database name as it will be different everytime as per user requirement.
Any help will be appreciated.
Sagar Sonawane
** Every DBA has his day!!:cool:
April 15, 2016 at 7:01 am
is it accessing metadata, like sys.tables and stuff,so you KNOW the object sit access exist in ALL databases, or is it assuming that specific tables exist int he database you want to pass?
the reason I ask, is if it's for getting metadata, there's a neat technique for that; you put the proc in master, mark it as a system object, and the implicit or explicit database context will filter rows to be related to the current database.
If it's the other way, where you assume objects like dbo.CustomerList exists, i'd probably just hard code it. that way i eliminate errors where i apss in an invalid database.
it's undoubtedly going to have permissions issues for normal users, so i'd use execute as owner or execute as login='superman' to resolve permissions;
You could use dynamic SQL to just execute against the right table or proc as well...
DECLARE @cmd varchar(max) = 'SELECT * FROM ' + @dbname + '.dbo.CustomerList;'
EXEC(@cmd)
IF OBJECT_ID('[dbo].[sp_show]') IS NOT NULL
DROP PROCEDURE [dbo].[sp_show]
GO
--#################################################################################################
--developer utility function added by Lowell, used in SQL Server Management Studio
--Purpose: Preview top 100 rows of a given table
--additional modification: fast count of rows if a TABLE or #Temp (no results on views)
--#################################################################################################
CREATE PROCEDURE sp_show
--USAGE: sp_show gmact
@TblName VARCHAR(128),
@Top INT = 100
--WITH ENCRYPTION
AS
BEGIN
DECLARE @cmd VARCHAR(MAX)
IF LEFT(@TblName,1) = '#'
BEGIN
SELECT
ps.row_count AS TotalRows
FROM tempdb.sys.indexes AS i
INNER JOIN tempdb.sys.objects AS o
ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN tempdb.sys.dm_db_partition_stats AS ps
ON i.OBJECT_ID = ps.OBJECT_ID
AND i.index_id = ps.index_id
WHERE i.index_id < 2
AND o.is_ms_shipped = 0
AND o.object_id = OBJECT_ID('tempdb.dbo.' + RTRIM(@TblName)) ;
END
ELSE
BEGIN
SELECT
ps.row_count AS TotalRows
FROM sys.indexes AS i
INNER JOIN sys.objects AS o
ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats AS ps
ON i.OBJECT_ID = ps.OBJECT_ID
AND i.index_id = ps.index_id
WHERE i.index_id < 2
AND o.is_ms_shipped = 0
AND o.object_id = OBJECT_ID(RTRIM(@TblName)) ;
END
SET @cmd = 'SELECT TOP ' + CONVERT(VARCHAR,@Top) + ' * FROM ' + QUOTENAME(@TblName) + ' ORDER BY 1 DESC '
EXEC(@cmd)
END
GO
--#################################################################################################
--Mark as a system object
EXECUTE sp_ms_marksystemobject '[dbo].[sp_show]'
--#################################################################################################
CREATE PROC example(@dbname sysname)
WITH EXECUTE AS OWNER
AS
IF @dbname='Google'
SELECT * FROM Google.dbo.CustomerList
IF @dbname='Yahoo'
SELECT * FROM Yahoo.dbo.CustomerList
Lowell
April 15, 2016 at 8:22 am
Look into dynamic SQL. That's how you pass database/table/field values into SQL statements.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply