nvarchar as query argument

I am trying to create a stored procedure that has a table and as an argument and executes some queries on that table.

So...

CREATE PROCEDURE blabla
@TableName nvarchar(50)
AS
DROP TABLE @TableName -- just an example, real quer开发者_StackOverflow社区ies are much longer
GO

This query gives me incorrect syntax error.

I know I can always use sp_executesql procedure, but I want a neater way where I don't need to worry about building an endless sql string.

Thanks

Here is a good article on why not to use Dynamic SQL in most cases as well as how to use it properly when it is the best solution:

http://www.sommarskog.se/dynamic_sql.html

Basically, doing what you are looking to do has a number of issues, including not allowing the system to properly check for permission issues before executing, not being able to optimize the stored procedure, and (most importantly) opening yourself up to SQL injection. You can mitigate this last issue somewhat but it involves a much more complex statement. Here is a quote from the above article:

Passing table and column names as parameters to a procedure with dynamic SQL is rarely a good idea for application code. (It can make perfectly sense for admin tasks). As I've said, you cannot pass a table or a column name as a parameter to sp_executesql, but you must interpolate it into the SQL string. Still you should protect it against SQL injection, as a matter of routine. It could be that bad it comes from user input.

To this end, you should use the built-in function quotename() (added in SQL 7). quotename() takes two parameters: the first is a string, and the second is a pair of delimiters to wrap the string in. The default for the second parameter is []. Thus, quotename('Orders') returns [Orders]. quotename() takes care of nested delimiters, so if you have a really crazy table name like Left]Bracket, quotename() will return [Left]]Bracket].

Note that when you work with names with several components, each component should be quoted separately. quotename('dbo.Orders') returns [dbo.Orders], but that is a table in an unknown schema of which the first four characters are d, b, o and a dot. As long as you only work with the dbo schema, best practice is to add dbo in the dynamic SQL and only pass the table name. If you work with different schemas, pass the schema as a separate parameter. (Although you could use the built-in function parsename() to split up a @tblname parameter in parts.)

I know you want a "neater" way of creating a dynamic statement but the reality is that no only is that not possible for how you want to do this, really you need to make the statement even more complex in order to ensure that the stored procedure is safe. I would try very hard to look at a different way to solve this issue (the article had a few suggestions). If you can avoid making this statement into dynamic SQL, you really should.

There are very few places that parameters can be used in T-SQL. Usually, it's exactly the places where you would find a quoted string - not just any arbitrary place within the query (where the query is necessarily in a string form anyway)

E.g., you could use a parameter or variable to replace 'hello' below:

SELECT * from Table2 where ColA = 'hello'

But you couldn't use it where Table2 appears. I don't know why people seem to expect such things to be possible in T-SQL, when it's generally not possible in most other programming languages either, outside of exec/eval style functions.

If you have multiple tables that share the same structure (names and types of columns), it generally suggests that what you should actually have is a single table, with possibly additional column(s) that distinguish between rows that would originally be in different tables. E.g. if you currently have:

CREATE TABLE MaleEmployees (
    EmployeeNo int not null,
    Name varchar(50) not null,
)

and

CREATE TABLE FemaleEmployees (
    EmployeeNo int not null,
    Name varchar(50) not null
)

You should instead have:

CREATE TABLE Employees (
    EmployeeNo int not null,
    Name varchar(50) not null,
    Gender char(1) not null,
    constraint CK_Gender_Valid CHECK (Gender in ('M','F'))
)

You can then query this Employees table, regardless of gender, rather than trying to parametrize the table name within your query. Of course, the above is an exaggerated example.

set @l = 'DROP TABLE ' + @TableName
exec @l

But if that's what you mean by 'endless string', not sure what you want

The correct syntax(notice the begin):

CREATE PROCEDURE blabla
@TableName nvarchar(50)
AS
begin
DROP TABLE @TableName -- just an example, real queries are much longer
END
GO