Passing a database name as a stored procedure parameter

Tags:
Stored Procedures
T-SQL
I would like to be able to pass the name of a database as a parameter to a stored procedure. This db would be used for certain queries and may be different from the db where the stored procedure runs. Is it possible to specify a database name from within the T-SQL of the stored procedure? If so, how?

Answer Wiki

Thanks. We'll let you know when a new response is added.

Yes you can do this, however it is not recommended. You have to use a technique called dynamic SQL. There are security risks on using dynamic SQL as the user who runs the procedure much have access to the table or tables listed in the dynamic SQL, and dynamic SQL is subject to a SQL injection attack.

That said, the syntax would be something like this.

<pre>CREATE PROCEDURE MyProcedure
@DBName sysname
AS
DECLARE @cmd nvarchar(2000)
SET @cmd = N’SELECT * FROM ‘ + @DBName + ‘.dbo.SomeTable’
exec (@cmd)
GO</pre>

You can add more input parameters as needed.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    Check out my SQL Server blog "SQL Server with Mr Denny" for more SQL Server information.
    66,075 pointsBadges:
    report
  • jaimie
    How can we restrict the SQL injection in the above said description.

    Jaimie El Rohi Nelaturi.
    10 pointsBadges:
    report
  • TomLiotta
    How can we restrict the SQL injection...   You do it using the same techniques that restrict "SQL injection" in any cirucumstances. You validate the parameter values with your code. You write the code to ensure that only parameter values that you define as acceptable are used.   Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following