CODEWITHSUNDEEP
sql-server-2008stored-procedures
sandeep bang
sandeep bang

Reputation: 1

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

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?

Upvotes: 0

Views: 499

Answers (2)

Bharath
Bharath

Reputation: 665

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)

Upvotes: 1

Vaibhav Survase
Vaibhav Survase

Reputation: 19

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

Upvotes: 0

Related Questions