Pass table name to stored procedure?
in [Server Configuration]
|
Prev: Can we access SQL Server 2008 R2 Database Engine with other Management Studio ?
Next: sp_releaseapplock timeout expired?
From: jam772 on 12 May 2010 12:30 Hi, I'm working on a stored procedure that will allow us to monitor some high level numbers we need to keep track of. The challenge here is that this stored procedure needs to be able to run it's code on one of many tables in one of many databases in one of many database environments. The code to obtain the numbers is working nicely now but I wanted to parameterize the procedure so that no hard coding of table names, databases, and database environments needs to occur. I want to be able to simply pass these values to the stored procedure but I'm just now learing that you can't do this with the database name. Is there a way I can make this happen? -- Thanks! - Jim
From: Erland Sommarskog on 12 May 2010 17:58 jam772 (jam772(a)discussions.microsoft.com) writes: > Hi, I'm working on a stored procedure that will allow us to monitor > some high level numbers we need to keep track of. The challenge here is > that this stored procedure needs to be able to run it's code on one of > many tables in one of many databases in one of many database > environments. > > The code to obtain the numbers is working nicely now but I wanted to > parameterize the procedure so that no hard coding of table names, > databases, and database environments needs to occur. I want to be able > to simply pass these values to the stored procedure but I'm just now > learing that you can't do this with the database name. > > Is there a way I can make this happen? Dynamic SQL is the way to go to do it. But whether you actually should, I don't know. In many cases where you feel compelled to this, the root is an incorrect database design. But since I don't know anything about your database, or your tables, I don't know what's right or wrong here. Anyway, there's an article on my web site where you can learn a lot about using dynamic SQL: http://www.sommarskog.se/dynamic_sql.html. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Eric Isaacs on 12 May 2010 18:00
You might be able to do it by taking advantage of a temporary synonym, where you pass in the server.database.schema.tablename and create a temporary synonym name using dynamic SQL, then use standard SQL to reference the Synonym name... IF OBJECT_ID('dbo.sprocname') IS NOT NULL DROP PROCEDURE dbo.SprocName GO CREATE PROCEDURE dbo.SprocName ( @TableName AS VARCHAR(200) ) AS BEGIN --Procedure SET NOCOUNT ON DECLARE @SQL AS VARCHAR(1000) IF OBJECT_ID('dbo.synTEMP') IS NOT NULL DROP SYNONYM dbo.synTEMP SET @SQL = 'CREATE SYNONYM dbo.synTEMP FOR ' + @TableName EXEC(@SQL) IF OBJECT_ID('dbo.synTEMP') IS NOT NULL BEGIN SELECT COUNT(*) FROM dbo.synTEMP END ELSE BEGIN PRINT @TableName + ' Not Found.' END END --Procedure GO EXEC sprocname 'servername.databasename.dbo.sometablename' -Eric Isaacs |