How to Pass Schema as Parameter to a Stored Procedure in SQL Server

How to pass schema name as parameter in SQL Server stored procedure?

You can use QUOTENAME to avoid any SQL injection and build your dynamic query like the following:

CREATE PROCEDURE [EFM].[usp_readApexTable] 
    @SCHEMANAME VARCHAR(20) = NULL,
    @TABLENAME VARCHAR(100) = NULL
AS
BEGIN 
    SET NOCOUNT ON;

    DECLARE @SQL VARCHAR(MAX)=N'SELECT *  FROM '
          + QUOTENAME(@SCHEMANAME) + '.' + QUOTENAME(@TABLENAME)
    EXEC (@SQL)
END
GO

Note: If you have any plan to add parameters also for your WHERE clause, in that case QUOTENAME will not help much, I suggest to to use sp_executesql by passing appropriate parameters used in WHERE clause.

Still you need to use QUOTENAME for schema and table name as SQL excepts it only as literal, you can't use variable names for table and schema.

For example.

  declare @sql nvarchar(max)
  set @sql = N'select * from ' + quotename(@SCHEMANAME ) + '.' +  quotename(@TABLENAME ) 
         + '  where (City = @City)' 
  exec sp_executesql 
    @sql, 
    N'@City nvarchar(50)',       
    @City 

You can find more details here

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

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)

How to pass schema parameter to a procedure without dynamic sql

Many thanks to @Sepupic although the final answer wasn't quite his, but it helped me down the road I needed. A couple of +1s on his comments for the credit.

Solution I finally used:

Stored Procedure to copy a stored proc from schema "template" to schema @SessionID

CREATE PROC [dbo].[usp_CopySproc]
    @SessionID NVARCHAR(255)
    ,   @ProcName NVARCHAR(255)
AS
BEGIN
    DECLARE @strSQL AS NVARCHAR(MAX)
    DECLARE @FullProcName AS NVARCHAR(MAX)

    SET @FullProcName = @SessionID + '.' + @ProcName

    IF OBJECT_ID(@FullProcName) IS NULL
        BEGIN
            PRINT 'Creating ' + @FullProcName

            SET @strSQL = OBJECT_DEFINITION(OBJECT_ID('template.' + @ProcName))
            SET @strSQL = STUFF(@strSQL, PATINDEX('%template%', @strSQL), 8, @SessionID)

            EXEC (@strSQL)
        END
    ELSE
        PRINT @FullProcName + ' already exists'

END

I setup a schema on session start up ready to contain procedures and session data. Then copied the procs in using a command like:

DECLARE @strSQL NVARCHAR(MAX)
,       @SessionID NVARCHAR(255) = 'mySession'

EXEC dbo.usp_CopySproc @SessionID , 'usp_RefreshPriceSummary'
SET @strSQL = @SessionID + '.usp_RefreshPriceSummary'
EXEC @strSQL @SessionID

The procedures I copied could then select from the tables within the session schema without qualifying their schema.

Finally on session close/cleanup I navigated through the object table drop anything with that schema id.

Pass PL/SQL parameter as SCHEMA NAME

There is a way; you'll need some kind of dynamic SQL because you can't use schema (or object) names like that. For example, you could use refcursor instead.

Sample table:

SQL> create table table_name as
  2  select 'EMP' table_name, 'PERMANENT' type from dual union all
  3  select 'DEPT'          , 'TEMPORARY'      from dual union all
  4  select 'BONUS'         , 'PERMANENT'      from dual;

Table created.

Procedure; note the way I composed SELECT statement first (so that I could display it and check whether it is correct), and then used it in OPEN. Loop is here to ... well, loop through the cursor. I'm just displaying table names I found - you'd probably do something smarter.

SQL> create or replace procedure housekeeping (par_schemaname in varchar2)
  2  is
  3    l_str        varchar2(500);
  4    l_rc         sys_refcursor;
  5    l_table_name varchar2(30);
  6  begin
  7    l_str := 'select table_name from ' ||
  8              dbms_assert.schema_name(upper(par_schemaname)) ||
  9             '.table_name where type = ''PERMANENT'' and rownum < 200';
 10    open l_rc for l_str;
 11
 12    loop
 13      fetch l_rc into l_table_name;
 14      exit when l_rc%notfound;
 15
 16      dbms_output.put_line(l_table_name);
 17    end loop;
 18    close l_rc;
 19  end;
 20  /

Procedure created.

Testing:

SQL> set serveroutput on
SQL> exec housekeeping('SCOTT');
EMP
BONUS

PL/SQL procedure successfully completed.

SQL>

how to pass schema_name_variable in stored procedure syntax call schema_name_variable.storedproceduretask()

You need dynamic SQL:

EXECUTE format('CALL %I.sp_master_procedure()', schema_name_var);

passing schema name in mysql procedure

SET @sql_text = concat('SELECT lookup_value INTO l_client_id FROM...

Error Code: 1327. Undeclared variable: l_client_id

You can't reference procedure-local variables in a dynamic SQL statement. This is because the dynamic SQL execution doesn't have any access to local variables in your stored procedure. It's like it's executing the dynamic SQL statement in a different scope.

As a workaround, you can store the results of the dynamic SQL statement in session variables instead of local variables. Session variables have the @ prefix.

SET @sql_text = concat('SELECT lookup_value INTO @l_client_id FROM...

FROM param_db.lookup_code_detail 

Error Code: 1146 Table 'param_db.lookup_code_detail' doesn't exist

You cannot use the param_db variable in the way you're doing. Variables can take the place of scalar values only, not identifiers.

---

Related Topics