April 28, 2011 at 9:24 am
Hello,
I have a 3 parameter User Stored Procedure that reads:
EXEC dbo.usp_Generate_Metric_Table @DataBase = N'DHL_KEMP_LOAD',
@Table_Name = N'RAW_DHL_KEMP_CLAIM',
@Evaluation_Date = N'2011-05-01
My next statement to use the Database passed in to the UP as a paramter is:
Use @BataBase
When this Use @DataBase statement runs in the USP i get the following error:
Incorrect sysntax near @DataBase
If I substitute the the real name of the DataBase (Use Raw_DLK_Production) the USP does not error out.
Any thoughts? Thank you.
April 28, 2011 at 9:32 am
You either need to use full dynamic sql or solve the problem differently.
What are you trying to accomplish exactly?
April 28, 2011 at 9:43 am
I have written a utility that our users need to be able to run form a Central database that is accessible by all users. In this Database, resides my utility that runs agains a database of their choosing based on the N'DataBase' parameter they pass to the EXEC function. If I keep thwe storedprocedure in on location, maintenance is simpler since it will reside in ionly one location.
The USE @Database throws the error I mentioned.
In a query editor... try this.
USE <database> any name of your choosing. This will work fine
now try this...
Declare @database as varchar(30)
Set @database = '<database noame of your choosing>'
USE @DataBase
Thisa will error out.... Why?
April 28, 2011 at 9:49 am
It just does error out (by design). You need to use dynamic sql.
I've had to do something similar for a website. I simply concatenated the right DB name in the query and used parameters for the rest.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply