How do I write the stored procedure with parameters?

rg001
rg001 used Ask the Experts™
on
I am having problems writing the following stored procedure:

What I need to do:

Run the SP via VBA and pass the following parameters to the SP

pTable     – name of the table to insert to

pColumn – the name of the column I want to insert the value into

pValue    – the value to insert into the pColumn column.


Thus execute -           sp_InsertRecord(‘tableA’, ‘Id’, 012345)


CREATE PROCEDURE sp_InsertRecord (@pTable as character, @pColumn as character, @value INT)
AS
BEGIN
   -- Add a record

      INSERT @pTable   (@pColumn)
      VALUES (@value)
   
END
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
you should use the dynamic sql.
CREATE PROCEDURE sp_InsertRecord (
	@pTable varchar(100), 
	@pColumn varchar(100), 
	@value int)
AS
BEGIN
   -- Add a record
	DECLARE @sql	VARCHAR(1000);
	
	SET @sql = 'INSERT INTO ' + @pTable +' ('+ @pColumn + ') ' +
      'VALUES (' + CAST(@value AS VARCHAR) + ')';
      
    EXEC (@sql);   
END

Open in new window

Author

Commented:
This is exactly what I need.  

Thank you, I appreciate it!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial