SQL Server Procedure Parameter Using [duplicate]

Question

I created a procedure successfully but I have an issue on using parameter while executing the procedure

CREATE PROCEDURE TestProc 
     @table varchar(200)    
AS   
     UPDATE [test].[dbo].[@table]           
     SET [Column 9] = [Column 10]
     WHERE [Column 9] = 'Example1' 
        OR [Column 9] = 'Example2'     
GO

When the procedure is executed like this

EXEC TestProc @table= '2015-07'

I want to update the table [test].[dbo].[2015-07].

Does anyone have an idea how to solve this?

similar problem Dynamic update statement with variable column names — Roman Marusyk
– Roman Marusyk, Commented Sep 8, 2015 at 15:19
You can't pass the table as a parameter. Using dynamic SQL in a stored procedure doesn't offer any benefits over writing the statement on the client either. In fact, it's safer to create a parameterized query on the client and execute it on the server — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 8, 2015 at 15:22

Roman Marusyk · Accepted Answer · 2015-09-08 15:17:38Z

0

If I proper urderstood you want to pass name of table to procedure and in procedure update this table. Try to use code below but it will work only for table that have Column 9 and Column 10

CREATE PROCEDURE TestProc @table varchar(200)    
AS   
DECLARE @SQL varchar(500)
SET @SQL = 'UPDATE [test].[dbo].[' + @table + ']           
            SET [Column 9]=[Column 10]
            WHERE [Column 9]= ''Example1'' OR [Column 9]=''Example2''' 
EXEC (@SQL) 
GO

edited Sep 8, 2015 at 15:17

answered Sep 8, 2015 at 15:09

Roman Marusyk

24.8k27 gold badges83 silver badges127 bronze badges

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

Collectives™ on Stack Overflow

SQL Server Procedure Parameter Using [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related