Passing table name as a input parameter to a SQL server stored procedure

Question

I'm using SQLserver 2008 and I have created a stored procedure to insert multiple records into a table. But I'm unable to pass the table name as a input parameter.

USE $(DbName)
GO
DECLARE @command varchar(1000),@i int, @tabname as varchar(100)
set @tabname = $(tabn)
set @i = $(startn) 
while @i < $(endn)
BEGIN
SET @command = 'Insert into '+($tabn)+'(Name,Address) Values("Act'+ CAST(@i AS varchar) +'","Place '+ CAST(@i AS varchar) +'")'
EXEC (@command)
SET @i = @i + 1
END
Go

Getting error as

C:\Program Files\Microsoft SQL Server\100\Tools\Binn>SQLCMD.EXE -v DbName="Gannu"
startn="90" endn="180" tabn="Sandeep" -i U:\SSHScript\recvariable.sql
Changed database context to 'Gannu'.
Msg 126, Level 15, State 1, Server BRANCH1_WIN, Line 6
Invalid pseudocolumn "$tabn".

Can someone help me on this?

Bharath · Accepted Answer · 2016-06-21 19:30:02Z

1

This should resolve your issue

USE $(DbName)
GO
DECLARE @command varchar(1000),@i int, @tabname as varchar(100)
set @tabname = '$(tabn)'
set @i = $(startn) 
while @i < $(endn)
BEGIN
SET @command = 'Insert into ' + @tabname + '(Name,city) Values("Act'+   CAST(@i AS varchar) +'","Place '+ CAST(@i AS varchar) +'")'
EXEC (@command)

SET @i = @i + 1
END
Go

C:\Program Files\Microsoft SQL Server\100\Tools\Binn>SQLCMD.EXE -v DbName="Gannu" tabn="Bharath" startn="20" endn="25" -i T:\SSHScript\validate.sql

Changed database context to 'Gannu'.

(1 rows affected)

answered Jun 21, 2016 at 19:30

Bharath

6656 gold badges13 silver badges21 bronze badges

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

Comments

Vaibhav Survase · Accepted Answer · 2016-06-21 15:35:53Z

0

I could run the above query with some data in places of variables.

The only thing, I found problematic was to use double quotes (") for values Act, Place. Double quotes won't work. You need single quotes there.

USE VAIBHAVDB
if OBJECT_ID('MyTable', 'U') is not null
drop table MyTable
Go
create table MyTable (name varchar(500), address varchar(500))
GO
DECLARE @command nvarchar(1000),@i int, @tabname as varchar(100)
set @tabname = 'MyTable'
set @i = 1
while @i < 5
BEGIN
SET @command = 'Insert into '+@tabname +'(Name,Address) Values('+ '''' + 'Act' + CAST(@i AS varchar) + '''' +' ,'+''''+'Place '+ CAST(@i AS varchar) +''''+')'
PRINT @command
EXEC (@command)
SET @i = @i + 1
END
Go

answered Jun 21, 2016 at 15:35

Vaibhav Survase

195 bronze badges

Collectives™ on Stack Overflow

Passing table name as a input parameter to a SQL server stored procedure

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related