CODEWITHSUNDEEP
sql-serverstored-procedures
xzk
xzk

Reputation: 877

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

As I have seen so far, people suggested using dynamic SQL.

For example:

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

How to pass schema name as parameter in stored procedure

However, dynamic SQL has the risk of SQL injection. Hence, I want to know if there are any other safe alternatives?

Basically, this stored procedure that I am creating will be called at runtime. There will be 2 possible schemas to be passed in. And the table name will be passed in as well.

Something like below: (It does not work)

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

    SELECT *  
    FROM [@SCHEMANAME].[@TABLENAME];
END
GO

This is just an example of READ action. My plan is to create for CRUD, which requires 4 different stored procedures.

Upvotes: 4

Views: 11995

Answers (2)

PSK
PSK

Reputation: 17943

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

Upvotes: 4

sebu
sebu

Reputation: 2954

You need to use dynamic sql to do this operation 

CREATE PROCEDURE [EFM].[usp_readApexTable] 
@SCHEMANAME VARCHAR(20) = NULL,
@TABLENAME VARCHAR(100) = NULL
AS
BEGIN 
    SET NOCOUNT ON;
    DECLARE @sqlCommand nvarchar(MAX)
    SET @sqlCommand='SELECT *  FROM ['+@SCHEMANAME+'].['+@TABLENAME+'];'
    --Create Your Temp Table where you can set the records after executing the dynamic query
    CREATE TABLE #tmpTbl(
        Column1 [datatype]
        Column2 [datatype]
        .
        .
        ColumnN
    )
    INSERT INTO #tmpTbl EXEC sp_executesql  @sqlCommand --Copy data to #tmpTbl table
    SELECT * FROM #tmpTbl
    DROP TABLE #tmpTbl
END
GO

Upvotes: 0

Related Questions