How to pass a schema as a parameter to a stored procedure in sql server?

Question

I have a stored procedure for selecting a list of data based on two tables. The first table is fixed: CO.Country. But the second table may be one of several tables. Name of the table itself is the same: Location. But the table layout is different:ABD.Location, CGA.Location, GBN.Location.

The user selects a diagram from the application, then the selected diagram will be passed to the stored procedure as a parameter.

But an error occurs when analyzing a stored procedure when it is created.

Anyway, to pass the schema name as a parameter?

Answer 1

DynamicSql

CREATE PROCEDURE proc_name
   @schema VARCHAR(25)
AS

DECLARE @Query VARCHAR(1000)
SET @query='SELECT * FROM' +@schema +'.Location'
EXECUTE(@query)