I have a stored procedure to select a list of data based on two tables. The first table is a fixed one: CO.Country. But the second table can be one of a number of tables. The name of the table itself is the same: Location. But, the schema of the tables are different: ABD.Location, CGA.Location, GBN.Location.
The user will select the schema from the application, then the schema chosen will be passed to the stored procedure as a parameter.
But there's an error when I parse the stored procedure while creating it.
Is there anyway to pass the schema name as a parameter?
Use DynamicSql
Try like this
CREATE PROCEDURE proc_name
@schema VARCHAR(25)
AS
DECLARE @Query VARCHAR(1000)
SET @query='SELECT * FROM' +@schema +'.Location'
EXECUTE(@query)
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments