Passing TableName as parameter in SP 1

[5679]

I want to pass a a table name as parameter in a stored procedure to allow it to chioose its table, but it doesnt work. i`ve tried the following:-

CREATE PROCEDURE testTables
@tableName varchar (50),
@name_New varchar(50)
AS
INSERT INTO [@tableName]
VALUES
(@name_New)

testTables 'TESTING','myName'

'TESTING' is the table name and 'myName' is a value to be inserted in the table TESTING

 

[SemperFiDownUnda]

You have to use the
EXEC
command and pass it a string like
EXEC 'INSERT INTO ' + @tablename + ' VALUES (' + @name_New +')'



Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[5679]

my stored procedure is:-

CREATE PROCEDURE testTables
@tableName sysname,
@name_New varchar(50)
AS
EXEC ('INSERT INTO ' + @tablename + '(name) VALUES (' + @name_New +')')

But, when i execute it as follows:-
testTables 'TESTING','myName'

I get the following error:-
server: Msg 128, Level 15, State 1, Line 1
The name 'SDFDFS' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

any hints?

 

[TysonLPrice]

I copied this from another post a few weeks ago. It works ASIS on my users table. Maybe you can tweak it.

DECLARE @tablename SYSNAME
declare @SQLCommand VarChar(200)
SET @tablename = 'users'
SET @SQLCommand = 'SELECT * FROM ' + @tablename
print @sqlcommand
EXEC (@SQLCommand)

 

[donutman]

@tableName sysname
what's that?

 

[TysonLPrice]

I just copied the code but in looking it up it looks like:

sysname is a system-supplied user-defined
data type that is functionally equivalent to nvarchar(128)

 

[SemperFiDownUnda]

try running this
executing this SP in QA and see the output

CREATE PROCEDURE testTables
    @tableName sysname,
    @name_New varchar(50)
AS
PRINT 'INSERT INTO ' + @tablename + '(name) VALUES (' + @name_New +')'

What I'll expect you'll find is that your statement looks like

INSERT INTO blah (name) VALUES (yadda)

You are not surrounding the @name_New variable in the concatinated string in single quotes.

Should look like this

'INSERT INTO ' + @tablename + '(name) VALUES (''' + @name_New +''')'

Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first