传递多个参数

https://stackoverflow.com/questions/28481189/exec-sp-executesql-with-multiple-parameters

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-2017

SRE实战 互联网时代守护先锋,助力企业售后服务体系运筹帷幄!一键直达领取阿里云限量特价优惠。
DECLARE @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', @t

This 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;

https://stackoverflow.com/questions/1246760/how-should-i-pass-a-table-name-into-a-stored-proc/1246848#1246848

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) END

Some have fairly asked why this is safer. Hopefully, little Bobby Tables can make this clearer:

 EXEC sp_executesql with multiple parameters 随笔

Answers to more questions:

  1. 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.

  2. 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 无法识别临时表或者表变量

https://stackoverflow.com/questions/8040105/execute-sp-executesql-for-select-into-table-but-cant-select-out-temp-table-d

问题:

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.

 

 

 

扫码关注我们
微信号:SRE实战
拒绝背锅 运筹帷幄