cannot we pass the table as a parameter in stored procedure..?.

Question

SET QUOTED_IDENTIFIER OFF
go
CREATE PROCEDURE ps_StudentList_Import
@PathFileName varchar(100),
@tablename varchar(100)

AS

DECLARE @SQL varchar(2000)

 BEGIN

  SET @SQL = "BULK INSERT '"+@tablename+"' FROM '"+@PathFileName+"' WITH (FIELDTERMINATOR = '"",""',ROWTERMINATOR = '""\n""') "
 END
 exec(@SQL);
 go


 EXEC ps_StudentList_Import 'c:\data\DimActivity.txt' ,'dbo.DimActivity';

here the 'dbo.DimActivity' is a table as parameter cannot we pass the table as a parameter? what is wrong with my above code..please anyone help me out...

Answer 1

Placing begin and end at the right place might help:

AS
    BEGIN
    DECLARE @SQL varchar(2000)
    SET @SQL = "BULK INSERT '"+@tablename+"' FROM '"+@PathFileName+
        "' WITH (FIELDTERMINATOR = '"",""',ROWTERMINATOR = '""\n""') "
    exec(@SQL);
    END
go

Answer 2

I'm not sure about the quotes usage. Does this work?

CREATE PROCEDURE ps_StudentList_Import
@PathFileName varchar(100),
@tablename sysname

AS
BEGIN
DECLARE @SQL varchar(2000)

 SET @SQL = 'BULK INSERT '+@tablename+' FROM '''+@PathFileName+''' WITH (FIELDTERMINATOR = '','',ROWTERMINATOR = ''\n'') '

print @SQL
exec(@SQL);
 END

Answer 3

You could try dropping the "dbo." from table name. The dbo is just a namespace, the actual name is "DimActivity". Add a 'use' statement at the top to specify which database the table is in.