Stored Procedures 1

[gillesnebel]

Hi,

Can somebody help me with the following stored procedure. I want to declare dynamically the name of table that has to be filled. But there must be a syntax error.....


CREATE PROCEDURE [sp_TransferTax]
(
@DataTableName VarChar (150)
)

AS
INSERT INTO @DataTableName (x,y,z) VALUES (1,2,3)
GO

Can somebody help me?

Thanks

 

[jbenson001]

You need to use Dynamic SQL to do this.

 

[gmmastros]

You can use dynamic SQL to accomplish this. If there is a relatively small number of tables, I would advise creating a seperate stored procedure for each table.

The dynamic SQL code...

CREATE PROCEDURE [sp_TransferTax]
(
@DataTableName VarChar (150)
)

AS 
Declare @SQL VarChar(8000)
Set @SQL = 'INSERT INTO ' + @DataTableName + ' (x,y,z) VALUES (1,2,3)'
Exec (@SQL)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

CREATE PROCEDURE [sp_TransferTax]
(
@DataTableName VarChar (150)
)

AS
declare @sql varchar(500)
select @sql = 'INSERT INTO ' + @DataTableName + '(x,y,z) VALUES (1,2,3) '

exec (@sql)
GO

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[sodakotahusker]

You must use dynamic sql in this situation

Declare @sql nvarchar(500) -- declare variable to hold code

set @sql = "INSERT INTO ' + @DataTableName + '(x,y,z) VALUES (1,2,3)' -- this creates a string which contains your code

sp_executeSql @sql -- this executes your code

 

[SQLDenis]

Close again gmmastros, Are you still going to the Philly launch?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

Yes. I'm going.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gillesnebel]

Thanks to all replies,... I will try to add some dynamic sql instead.