动态 SQL(将表名作为参数传递) [英] Dynamic SQL (passing table name as parameter)

问题描述

我想编写一个存储过程，它将使用一个参数，即表名.

I want to write a stored proc which will use a parameter, which will be the table name.

例如:

@tablename << Parameter

SELECT * FROM @tablename

这怎么可能?

我是这样写的:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetAllInterviewQuestions]
@Alias varchar = null
AS
BEGIN
Exec('Select * FROM Table as ' @Alias) 
END

但它说@Alias 附近的语法不正确.

But it says incorrect syntax near @Alias.

推荐答案

嗯，首先你已经从你的字符串中省略了+".这种做事方式远非理想，但你可以做到

Well, firstly you've omitted the '+' from your string. This way of doing things is far from ideal, but you can do

DECLARE @SQL varchar(250)
SELECT @SQL = 'SELECT * FROM ' + QuoteName(@Alias)
Exec(@SQL)

不过，我强烈建议您重新考虑如何执行此操作.生成动态 SQL 通常会导致 SQL 注入漏洞，并使 SQL Server(和其他数据库)更难找出处理查询的最佳方式.如果你有一个可以返回任何表的存储过程，你实际上从它作为一个存储过程中获得几乎没有任何好处，因为它不能在优化方面做很多事情，而且你在很大程度上也削弱了安全优势.

I'd strongly suggest rethinking how you do this, however. Generating Dynamic SQL often leads to SQL Injection vulnerabilities as well as making it harder for SQL Server (and other DBs) to work out the best way to process your query. If you have a stored procedure that can return any table, you're really getting virtually no benefit from it being a stored procedure in the first place as it won't be able to do much in the way of optimizations, and you're largely emasculating the security benefits too.

这篇关于动态 SQL(将表名作为参数传递)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！