Using sp_executesql in a stored proc 1

[GacUtil]

I'm trying to build a stored proc that allows a user to specify a table name, and then the stored proc deletes all records from that tabel. Basically I'm trying to use sp_executesql within the proc to execute a DELETE statement as shown below. I keep receiving the error "Must declare the variable '@DeleteTable'." I'm not sure what I'm doing wrong. Any ideas? Thanks!

CREATE PROCEDURE dbo.spDeleteTEMPTables
(
	@TableName varchar(25)
)

AS
exec sp_executesql N'DELETE FROM @DeleteTable', N'@DeleteTable varchar(50)', @DeleteTable = @TableName
GO

 

[Durango122]

hi
there no referencr to @DeleteTable
CREATE PROCEDURE dbo.spDeleteTEMPTables
(
@TableName varchar(25)
@DeleteTable varchar(50)
)


Durango122

 

[SQLDenis]

maybe it doesn't like table names like that

--WORKS
DECLARE @IntVariable INT
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
SET @SQLString =     N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = @level'

SET @ParmDefinition = N'@level tinyint'
SET @IntVariable = 35

EXECUTE sp_executesql @SQLString, @ParmDefinition,@level = @IntVariable



--Does not work


DECLARE @IntVariable varchar(50)

DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
 
SET @SQLString = N'SELECT * FROM @level'

SET @ParmDefinition = N'@level varchar(50)'
SET @IntVariable = 'employee'

EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable

you can always do something like this

create PROCEDURE dbo.spDeleteTEMPTables
(
    @TableName varchar(25)
)

AS
DECLARE @SQLString VARCHAR(500)
SET @SQLString = 'SELECT * FROM ' + @TableName
exec (@SQLString)

then change select to delete and run like this

spDeleteTEMPTables 'pubs..authors'

[/code]


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[GacUtil]

Wow, thanks! The modified proc as recommended by SQLDenis works perfectly!

 

[Lekar]

Sometimes is better to use Exec instead of sp_executesql?

 

[Jasen]

sp_executesql is good if you want to return a couple specific results back into variables. But doing a simple exec is the probably easiest way to run arbitrary or changing code. I don't think there's really much difference though.

 

[SQLDenis]

The main advantage of sp_executesql is query plan reuse so it should perform faster if you hit the same sp_executesql with different parameters

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/