传递多个参数
https://stackoverflow.com/questions/28481189/exec-sp-executesql-with-multiple-parameters
Here is a simple example:
EXEC sp_executesql @sql, N'@p1 INT, @p2 INT, @p3 INT', @p1, @p2, @p3;Your call will be something like this
EXEC sp_executesql @statement, N'@LabID int, @BeginDate date, @EndDate date, @RequestTypeID varchar', @LabID, @BeginDate, @EndDate, @RequestTypeIDDECLARE @IntVariable int; DECLARE @SQLString nvarchar(500); DECLARE @ParmDefinition nvarchar(500); /* Build the SQL string one time.*/ SET @SQLString = N'SELECT BusinessEntityID, NationalIDNumber, JobTitle, LoginID FROM AdventureWorks2012.HumanResources.Employee WHERE BusinessEntityID = @BusinessEntityID'; SET @ParmDefinition = N'@BusinessEntityID tinyint'; /* Execute the string with the first parameter value. */ SET @IntVariable = 197; EXECUTE sp_executesql @SQLString, @ParmDefinition, @BusinessEntityID = @IntVariable; /* Execute the same string with the second parameter value. */ SET @IntVariable = 109; EXECUTE sp_executesql @SQLString, @ParmDefinition, @BusinessEntityID = @IntVariable;
传递表变量作为参数(比较麻烦，需要自定义表类型)
错误提示为
Must declare the table variable "@TempTable".
因为需要自定义表类型，显得比较麻烦。所以，如果只是单纯的需要表操作，可以考虑dynamic sql拼接临时表。参考下一个标题1
https://stackoverflow.com/questions/7329996/using-table-variable-with-sp-executesql
Here's an example of how to pass a table-valued parameter to sp_executesql. The variable has to be passed readonly:
if exists (select * from sys.types where name = 'TestTableType')
drop type TestTableType
create type TestTableType as table (id int)
go
declare @t TestTableType
insert @t select 6*7
exec sp_executesql N'select * from @var', N'@var TestTableType readonly', @tThis prints the Answer to the Ultimate Question of Life, the Universe, and Everything.
传递临时表作为参数(无法实现，只能通过dynamic sql进行拼接)
https://stackoverflow.com/questions/4258798/pass-a-table-variable-to-sp-executesql
While this may not directly answer your question, it should solve your issue overall.
You can indeed capture the results of a Stored Procedure execution into a temporary table:
INSERT INTO #workingData
EXEC myProc So change your code to look like the following:
CREATE TABLE #workingData ( col1 VARCHAR(20),
col2 VARCHAR(20) )
INSERT INTO #workingData
EXEC myProc
DECLARE @sql NVARCHAR(MAX)
SET @sql = 'SELECT * FROM #workingData'
EXEC sp_executesql @sql Regards, Tim
将表名作为参数进行传递
https://stackoverflow.com/questions/20156157/how-to-pass-a-table-variable-using-sp-executesql
这个很可能会导致sql 注入。
如果可以确保不是用户输入的话，就可以避免sql注入
Figured out the problem.
Apparently sp_executesql expects the parameter definition for a table to be of a table type (see this answer for an example: https://stackoverflow.com/a/4264553/21539).
An easier way to solve this problem was to insert the variables names directly into the SQLStatement string as follows:
DECLARE @SQLString NVARCHAR(MAX),
@TableName NVARCHAR(MAX);
SET @TableName = N'[dbo].[MyTable]';
SET @SQLString = N'SELECT * FROM ' + @TableName + ';';
SET @ParamDefinition = N'@_TableName NVARCHAR(max);
EXEC sp_executesql @SQLString;
First of all, you should NEVER do SQL command compositions on a client app like this, that's what SQL Injection is. (Its OK for an admin tool that has no privs of its own, but not for a shared use application).
Secondly, yes, a parametrized call to a Stored procedure is both cleaner and safer.
However, as you will need to use Dynamic SQL to do this, you still do not want to include the passed string in the text of the executed query. Instead, you want to used the passed string to look up the names of the actual tables that the user should be allowed to query in the way.
Here's a simple naive example:
CREATE PROC spCountAnyTableRows( @PassedTableName as NVarchar(255) ) AS
-- Counts the number of rows from any non-system Table, *SAFELY*
BEGIN
DECLARE @ActualTableName AS NVarchar(255)
SELECT @ActualTableName = QUOTENAME( TABLE_NAME )
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @PassedTableName
DECLARE @sql AS NVARCHAR(MAX)
SELECT @sql = 'SELECT COUNT(*) FROM ' + @ActualTableName + ';'
EXEC(@SQL)
ENDSome have fairly asked why this is safer. Hopefully, little Bobby Tables can make this clearer:
Answers to more questions:
-
QUOTENAME alone is not guaranteed to be safe. MS encourages us to use it, but they have not given a guarantee that it cannot be out-foxed by hackers. FYI, real Security is all about the guarantees. The table lookup with QUOTENAME, is another story, it's unbreakable.
-
QUOTENAME is not strictly necessary for this example, the Lookup translation on INFORMATION_SCHEMA alone is normally sufficient. QUOTENAME is in here because it is good form in security to include a complete and correct solution. QUOTENAME in here is actually protecting against a distinct, but similar potential problem know as latent injection.
sp_executesql 无法识别临时表或者表变量
问题：
Was trying to select...into a temp Table #TempTable in sp_Executedsql. Not its successfully inserted or not but there Messages there written (359 row(s) affected) that mean successful inserted? Script below
DECLARE @Sql NVARCHAR(MAX);
SET @Sql = 'select distinct Coloum1,Coloum2 into #TempTable
from SPCTable with(nolock)
where Convert(varchar(10), Date_Tm, 120) Between @Date_From And @Date_To';
SET @Sql = 'DECLARE @Date_From VARCHAR(10);
DECLARE @Date_To VARCHAR(10);
SET @Date_From = '''+CONVERT(VARCHAR(10),DATEADD(d,DATEDIFF(d,0,GETDATE()),0)-1,120)+''';
SET @Date_To = '''+CONVERT(VARCHAR(10),DATEADD(d,DATEDIFF(d,0,GETDATE()),0)-1,120)+''';
'+ @Sql;
EXECUTE sp_executesql @Sql;
After executed,its return me on messages (359 row(s) affected). Next when trying to select out the data from #TempTable.
Select * From #TempTable;
Its return me:
Msg 208, Level 16, State 0, Line 2
Invalid object name '#TempTable'.
Suspected its working only the 'select' section only. The insert is not working. how fix it?
解答：
Local temporary table #table_name is visible in current session only, global temporary ##table_name tables are visible in all sessions.
Both lives until their session is closed.
sp_executesql - creates its own session (maybe word "scope" would be better) so that's why it happens.
可以识别的临时表
如果是在存储过程内部
select
into #TempTable
那么这个是可以在拼接的sql中识别的。
上面的例子无法识别，是因为那里的#TempTable是单独执行的，并不属于存储过程