SQL Server : Table Name as Parameter in Stored Procedure

Question

I need to be able to add columns to a table that will be specified by the "user". Thus, I was wondering what it might look like to pass a table name dynamically to the stored procedure. Below is as far as I've gotten but not sure where to go next. Ultimately, the user will specify the table name and various columns that will need to be "cleansed". To do this i want to add new columns to the table to then preform operations on.

USE *******
GO

CREATE PROCEDURE [dbo].[MS_FormatSectionData](
    @SectionCol varchar(50),
    @TwpCol varchar(50),
    @TWPDirCol varchar(50),
    @RangeCol varchar(50),
    @RangeDirCol varchar(50),
    @ST_CodeCol varchar(50),
    @Cnty_CodeCol varchar(50),
    @Mer_CodeCol varchar(50),
    @Stg_DB varchar(50),
    @Tbl_Name varchar(50)
)
AS
BEGIN TRY
    DECLARE @sql_AddStgCol VARCHAR(500)

    SET @sql_AddStgCol = 'ALTER TABLE' + @Tbl_Name + 'ADD stgSection  VARCHAR(3), stgTownship VARCHAR(3), 
    stgTownshipDir VARCHAR(1), stgRange VARCHAR(3), stgRangeDir VARCHAR(1), stgSt_Code VARCHAR(2), stgCnty_Code VARCHAR(3),
    stgMer_Code VARCHAR(3)' 

    EXEC(@sql_AddStgCol)

END TRY

BEGIN CATCH
      SELECT ERROR_NUMBER() AS ErrorNumber
     ,ERROR_SEVERITY() AS ErrorSeverity
     ,ERROR_STATE() AS ErrorState
     ,ERROR_PROCEDURE() AS ErrorProcedure
     ,ERROR_LINE() AS ErrorLine
     ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH


GO

space after table in alter table to alter table and before 'add' ' ADD' otherwise you get alter tablesomenameadd DB engine parser needs those spaces.!! — xQbert
– xQbert, Commented Apr 20, 2017 at 19:00

Evaldas Buinauskas · Accepted Answer · 2017-04-20 19:13:57Z

2

Like xQbert said - add spaces. Additionally I'd advice to wrap table name in QUOTENAME() in order to prevent SQL Injection and use sp_executesql instead of EXEC(). In this case it's not going to make a difference, but generally it's a good practice - you then can parameterize your queries.

DECLARE @sql_AddStgCol NVARCHAR(1000);

SET @sql_AddStgCol = N'
  ALTER TABLE ' + QUOTENAME(@Tbl_Name) + '
  ADD stgSection VARCHAR(3),
      stgTownship VARCHAR(3),
      stgTownshipDir VARCHAR(1),
      stgRange VARCHAR(3),
      stgRangeDir VARCHAR(1),
      stgSt_Code VARCHAR(2),
      stgCnty_Code VARCHAR(3),
      stgMer_Code VARCHAR(3);';

EXECUTE sp_executesql @sql_AddStgCol;

edited Apr 20, 2017 at 19:13

answered Apr 20, 2017 at 19:06

Evaldas Buinauskas

14.2k11 gold badges63 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server : Table Name as Parameter in Stored Procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related