Fred Hooper
fahooper at trapo.com
Mon Mar 24 17:48:48 CDT 2008
You create the stored procedure with an input parameter. Then, when you call the sp you provide the parameter. E.g. Exec sp_process_a_database DatabaseName The "DatabaseName" is assigned to the parameter and then processed. Unfortunately, that parameter can't be used for a field, table or database, as this information is needed to optimize the execution plan. So if you want to use it for one of these you'll need to use dynamic sql, which is just a different container than a DoCmd.RunSql, because it can't do anything with an execution plan. If you're just using the sp for different databases, don't fully specify tables and you can create the same sp in different databases. Fred -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Monday, March 24, 2008 5:27 PM To: 'Discussion concerning MS SQL Server' Subject: [dba-SQLServer] Stringing USPs together I am building a bunch of user stored procedures, which will eventually be strung together in another usp. However in order to do this I need to have the USP ask me a question or two, like the name of the database. Or is there a variable that gives me the name of the database that the usp is in? If I need to get variables into a usp how do I do so? By that I mean how do I get the USP to ask me a question and accept my answer and store it to a variable? John W. Colby Colby Consulting www.ColbyConsulting.com _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com