Re: Use of "USE databasename" Within a Stored Procedure

On Tue, 6 Dec 2005 08:22:03 -0800, RitaG wrote:

>Hi.
>
>I have a common stored procedure (SP) that has a variable containing a
>database name passed to it. Within the SP I have the statement "Use
>@databasename" but get an error when I run the SP. I then defined a variable
>that holds all the SQL statements (including the ?USE @databasename?) and
>then in the SP used ?Exec sp_executesql !@SQL?.
>
>This works fine but I now have to create another much more complicated SP
>and I find it tricky to work this way especially when I'm passing several
>variables to the SP.
>
>Is there another way I can use the "Use" statment within a SP without having
>to bundle all the SP code into a variable?
>
>Any suggestions will be greatly appreciated :-).
>Rita

Hi Rita,

First of all: why do you need to use the same procedure in different
databases? The only situation I can imagine if it's a procedure that
performs general DB-maintenance tasks. If you use this in a procedure
that performs business logic, than you might have a flawed design. If
you post more information, we can help you improve your design.

That being said, the answer to your question is that the only better way
than using dynamic SQL to do this is to not use it at all. Related data
should be in one database.

Are you aware of the risks of dynamic SQL? I heartily recommend you to
read Erland's site: http://www.sommarskog.se/dynamic_sql.html

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
.

Relevant Pages

  • Dynamic SQL in Stored Proc
    ... I have a database setup so that NO users have READ, ... Dynamic SQL and they have stopped working. ... error message when executing one of the stored procs. ...
    (microsoft.public.sqlserver.security)
  • Re: SQL Injection Prevention
    ... I was pointing that you're not considering it in isolation but with the ... I think it's much more likely that an application developer would build the ... sql string from input that the database developer would do it in dynamic sql. ...
    (microsoft.public.sqlserver.server)
  • Re: SQL Injection Prevention
    ... I was pointing that you're not considering it in isolation but with the ... I think it's much more likely that an application developer would build the ... sql string from input that the database developer would do it in dynamic sql. ...
    (microsoft.public.dotnet.security)
  • Re: ASP coding help required please
    ... Your database will not know anything about the Request object. ... sType = Request.Form ... sAcct = Request.Form ... Avoid the dynamic sql approach, but if you are determined to use it, see: ...
    (microsoft.public.inetserver.asp.db)
  • Re: SP accessing other db
    ... why do you pass the database name as a parameter? ... > The 1st SP calls the 2nd SP, the user has only EXEC permission, no other ... >> so dynamic SQL always breaks the ownership chain. ... The user has EXEC permission on this SP, ...
    (microsoft.public.sqlserver.security)