CODEWITHSUNDEEP
sql-serverstored-procedures
LCaraway
LCaraway

Reputation: 1357

SQL Server : Table Name as Parameter in Stored Procedure

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

Upvotes: 0

Views: 103

Answers (1)

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14097

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;

Upvotes: 2

Related Questions