åå¨è¿ç¨ä¸æ§è¡å¨æSqlè¯å¥
ããMSSQL为æ们æä¾äºä¸¤ç§å¨ææ§è¡SQLè¯å¥çå½ä»¤ï¼åå«æ¯EXECåsp_executesql;é常,sp_executesqlåæ´å
·æä¼å¿ï¼å®æä¾äºè¾å
¥è¾åºæ¥å£ï¼èEXEC没æãè¿æä¸ä¸ªæ大ç好å¤å°±æ¯å©ç¨sp_executesqlï¼è½å¤éç¨æ§è¡è®¡åï¼è¿å°±å¤§å¤§æä¾äºæ§è¡æ§è½ï¼è¿å¯ä»¥ç¼åæ´å®å
¨ç代ç ãEXECå¨æäºæ
åµä¸ä¼æ´çµæ´»ãé¤éæ¨æ令人信æççç±ä½¿ç¨EXECï¼å¦ä¾§å°½é使ç¨sp_executesql.
1.EXECç使ç¨
EXECå½ä»¤æ两ç§ç¨æ³ï¼ä¸ç§æ¯æ§è¡ä¸ä¸ªåå¨è¿ç¨ï¼å¦ä¸ç§æ¯æ§è¡ä¸ä¸ªå¨æçæ¹å¤çã以ä¸æ讲çé½æ¯ç¬¬äºç§ç¨æ³ã
ããä¸é¢å
使ç¨EXECæ¼ç¤ºä¸ä¸ªä¾å,代ç 1
代ç
DECLARE @TableName VARCHAR(50),@Sql NVARCHAR (MAX),@OrderID INT;
SET @TableName = 'Orders';
SET @OrderID = 10251;
SET @sql =
ãããã'SELECT * FROM '+QUOTENAME(@TableName) +'WHERE OrderID = '+
ããããCAST(@OrderID AS VARCHAR(10))+' ORDER BY ORDERID DESC'
EXEC(@sql);
注ï¼è¿éçEXECæ¬å·ä¸åªå
许å
å«ä¸ä¸ªå符串åéï¼ä½æ¯å¯ä»¥ä¸²èå¤ä¸ªåéï¼å¦ææ们è¿æ ·åEXECï¼
EXEC('SELECT TOP('+ CAST(@TopCount AS VARCHAR(10)) +')* FROM '+
ããããããããããQUOTENAME(@TableName) +' ORDER BY ORDERID DESC');
SQLç¼è¯å¨å°±ä¼æ¥éï¼ç¼è¯ä¸éè¿ï¼èå¦ææ们è¿æ ·ï¼
EXEC(@sql+@sql2+@sql3);
ç¼è¯å¨å°±ä¼éè¿ï¼
æ以æä½³çåæ³æ¯æ代ç æé å°ä¸ä¸ªåéä¸ï¼ç¶ååæ该åéä½ä¸ºEXECå½ä»¤çè¾å
¥åæ°ï¼è¿æ ·å°±ä¸ä¼åéå¶äºã
EXECç缺ç¹æ¯ä¸æä¾æ¥å£ï¼è¿éçæ¥å£æ¯æï¼å®ä¸è½æ§è¡ä¸ä¸ªå
å«ä¸ä¸ªå¸¦åé符çæ¹å¤çï¼å¦ä¸
代ç
DECLARE @TableName VARCHAR(50),@Sql NVARCHAR(MAX),@OrderID INT;
SET @TableName = 'Orders';
SET @OrderID = 10251;
SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) +
ãããã'WHERE OrderID = @OrderID ORDER BY ORDERID DESC'
EXEC(@sql);
å
³é®å°±å¨SET @sqlè¿ä¸å¥è¯ä¸ï¼å¦ææ们è¿è¡è¿ä¸ªæ¹å¤çï¼ç¼è¯å¨å°±ä¼äº§çä¸ä¸é误
Msg 137, Level 15, State 2, Line 1
å¿
须声ææ éåé "@OrderID"ã
使ç¨EXECæ¶ï¼å¦ææ¨æ³è®¿é®åéï¼å¿
é¡»æåéå
容串èå°å¨ææ建ç代ç å符串ä¸ï¼å¦ï¼
SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) + ãã'WHERE OrderID = '+CAST(@OrderID AS VARCHAR(10))+' ORDER BY ORDERID DESC'
串èåéçå
容ä¹åå¨æ§è½æ¹é¢çå¼ç«¯ãSQL Server为æ¯ä¸ä¸ªçæ¥è¯¢å符串å建æ°çæ§è¡è®¡åï¼å³ä½¿æ¥è¯¢æ¨¡å¼ç¸åä¹æ¯è¿æ ·ã为æ¼ç¤ºè¿ä¸ç¹ï¼å
æ¸
空ç¼åä¸çæ§è¡è®¡å
DBCC FREEPROCCACHE (è¿ä¸ªä¸æ¯æ¬æææ¶åçå
容ï¼æ¨å¯ä»¥æ¥çMSçMSDN)
å°ä»£ç 1è¿è¡3次ï¼åå«å¯¹@OrderID èµäºä¸é¢3个å¼ï¼10251ï¼10252ï¼10253ãç¶å使ç¨ä¸é¢ç代ç æ¥è¯¢
SELECT cacheobjtype,objtype,usecounts,sql FROM sys.syscacheobjects WHERE sql NOT LIKE '%cach%' AND sql NOT LIKE '%sys.%'
ç¹å»F5è¿è¡ï¼æ们å¯ä»¥çå°ï¼æ¯æ§è¡ä¸æ¬¡é½è¦äº§çä¸æ¬¡çç¼è¯ï¼æ§è¡è®¡å没æå¾å°å
åéç¨ã
EXECé¤äºä¸æ¯æå¨ææ¹å¤çä¸çè¾å
¥åæ°å¤ï¼ä»ä¹ä¸æ¯æè¾åºåæ°ãé»è®¤æ
åµä¸ï¼EXECææ¥è¯¢çè¾åºè¿åç»è°ç¨è
ãä¾å¦ä¸é¢ä»£ç è¿åOrders表ä¸ææçè®°å½æ°
DECLARE @sql NVARCHAR(MAX)
SET @sql = 'SELECT COUNT(ORDERID) FROM Orders';
EXEC(@sql);
ç¶èï¼å¦æä½ è¦æè¾åºè¿åç»è°ç¨æ¹å¤çä¸çåéï¼äºæ
就没æé£ä¹ç®åäºã为æ¤ï¼ä½ å¿
须使ç¨INSERT EXECè¯æ³æè¾åºæå
¥å°ä¸ä¸ªç®æ 表ä¸ï¼ç¶åä»è¿è¡¨ä¸è·åå¼åèµç»è¯¥åéï¼å°±åè¿æ ·ï¼
代ç
DECLARE @sql NVARCHAR(MAX),@RecordCount INT
SET @sql = 'SELECT COUNT(ORDERID) FROM Orders';
CREATE TABLE #T(TID INT);
INSERT INTO #T EXEC(@sql);
SET @RecordCount = (SELECT TID FROM #T)
SELECT @RecordCount
DROP TABLE #T2
2.sp_executesqlç使ç¨
sp_executesqlå½ä»¤å¨SQL Serverä¸å¼å
¥çæ¯EXECå½ä»¤æä¸äºï¼å®ä¸»è¦ä¸ºéç¨æ§è¡è®¡åæä¾æ´å¥½çæ¯æã
为äºåEXECä½ä¸ä¸ªé²æç对æ¯,æ们ççå¦æç¨ä»£ç 1ç代ç ï¼æEXECæ¢æsp_executesql,ççæ¯å¦å¾å°æ们æææçç»æ
代ç
DECLARE @TableName VARCHAR(50),@sql NVARCHAR(MAX),@OrderID INT ,@sql2 NVARCHAR(MAX);
SET @TableName = 'Orders ';
SET @OrderID = 10251;
SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) + ' WHERE OrderID = '+CAST(@OrderID AS VARCHAR(50)) + ' ORDER BY ORDERID DESC'
EXEC sp_executesql @sql
注ææåä¸è¡ï¼äºå®è¯æå¯ä»¥è¿è¡ï¼
sp_executesqlæä¾æ¥å£
sp_executesqlå½ä»¤æ¯EXECå½ä»¤æ´çµæ´»ï¼å 为å®æä¾ä¸ä¸ªæ¥å£ï¼è¯¥æ¥å£åæ¯æè¾å
¥åæ°ä¹æ¯æè¾åºåæ°ãè¿åè½ä½¿ä½ å¯ä»¥å建带åæ°çæ¥è¯¢å符串ï¼è¿æ ·å°±å¯ä»¥æ¯EXECæ´å¥½çéç¨æ§è¡è®¡å,sp_executesqlçææä¸åå¨è¿ç¨é常ç¸ä¼¼ï¼ä¸åä¹å¤å¨äºä½ æ¯å¨ææ建代ç ãå®çææå
æ¬ï¼ä»£ç å¿«ï¼åæ°å£°æé¨åï¼åæ°èµå¼é¨åã说äºè¿ä¹å¤ï¼è¿æ¯ççå®çè¯æ³ï¼
EXEC sp_executesql
@stmt= <statement>,--类似åå¨è¿ç¨ä¸»ä½
@params = <params>, --类似åå¨è¿ç¨åæ°é¨å,声æåæ°ç±»å
<params assignment> --类似åå¨è¿ç¨è°ç¨ï¼ä¸ºåæ°èµå¼,åæ°å¼è¦ååæ°é¡ºåºè¦ä¸ä¸å¯¹åºï¼ä¹å¯ä»¥éè¿ä¸ºåæ°ææåæ°å¼çæ¹å¼ä¸ºå
¶èµå¼
@stmtåæ°æ¯è¾å
¥çå¨ææ¹å¤çï¼å®å¯ä»¥å¼å
¥è¾å
¥åæ°æè¾åºåæ°ï¼ååå¨è¿ç¨ç主ä½è¯å¥ä¸æ ·ï¼åªä¸è¿å®æ¯å¨æçï¼èåå¨è¿ç¨æ¯éæçï¼ä¸è¿ä½ ä¹å¯ä»¥å¨åå¨è¿ç¨ä¸ä½¿ç¨sp_executesql;
@paramsåæ°ä¸å®ä¹è¾å
¥/è¾åºåæ°çåå¨è¿ç¨å¤´ç±»ä¼¼ï¼å®é
ä¸ååå¨è¿ç¨å¤´çè¯æ³å®å
¨ä¸æ ·ï¼
@<params assignment> ä¸è°ç¨åå¨è¿ç¨çEXECé¨å类似ã
å
¶å®@stmt,@paramså¯ä»¥çç¥ï¼é£ä¹exec sp_executesqlçè¯æ³å°±å¯ä»¥ç®åæå¦ä¸æ ¼å¼ï¼
EXEC sp_executesql
<statement>,
<params>,
<params assignment>
为äºè¯´æsp_executesql对æ§è¡è®¡åç管çä¼äºEXEC,æå°ä½¿ç¨åé¢è®¨è®ºEXECæ¶ç¨å°ç代ç ã
代ç
DECLARE @TableName VARCHAR(50),@sql NVARCHAR(MAX),@OrderID INT;
SET @TableName = 'Orders ';
SET @OrderID = 10251;
SET @sql = 'SELECT * FROM '+@TableName + ' WHERE OrderID = @OID ORDER BY ORDERID DESC'
--注æå½è¦å¯¹å¨æsqlè¯å¥ç表åå®è¡åæ°åæ¶ï¼ä¸å¯ä»¥å¦ä¸è¡¨ç¤ºï¼
--set @sql='select * from @TableName where OrderID=@OID ORDER BY Orderid desc',
--å¦æè¿æ ·ä¼æ示å¿
须声ææ éåé@TableName,åªå¯ä»¥å¦ä¸é¢æåçä¸æ ·ï¼å°è¡¨å@TableNameä½ä¸ºåéåè¿è¡æ¼æ¥
EXEC sp_executesql
@sql,
N'@OID int ',
@OID = @OrderID
ä¸é¢æ们ççexec sp_executesqlçæ§è¡æçï¼å¨è°ç¨è¯¥ä»£ç åæ£æ¥å®çæçæ§è¡è®¡ååï¼å
æ¸
空ç¼åä¸çæ§è¡è®¡åï¼
DBCC FREEPROCCACHE
å°ä¸é¢çå¨æ代ç æ§è¡3次ï¼æ¯æ¬¡æ§è¡é½èµäº@OrderID ä¸åçå¼ï¼ç¶åæ¥è¯¢sys.syscacheobjects表ï¼å¹¶æ³¨æå®çè¾åºï¼ä¼åå¨åªå建äºä¸ä¸ªå¤ç¨è®¡åï¼èä¸è¯¥è®¡å被éç¨ç3次
SELECT cacheobjtype,objtype,usecounts,sql FROM sys.syscacheobjects ããWHERE sql NOT LIKE '%cache%' AND sql NOT LIKE '%sys.%' AND sql NOT LIKE '%sp_executesql%'
ç¹å»F5è¿è¡ã
sq_executesqlçå¦ä¸ä¸ªä¸å
¶æ¥å£æå
³ç强大åè½æ¯ï¼ä½ å¯ä»¥ä½¿ç¨è¾åºåæ°ä¸ºè°ç¨æ¹å¤çä¸çåéè¿åå¼ãå©ç¨è¯¥åè½å¯ä»¥é¿å
ç¨ä¸´æ¶è¡¨è¿åæ°æ®ï¼ä»èå¾å°æ´é«æç代ç åæ´å°çéæ°ç¼è¯ãå®ä¹å使ç¨è¾åºåæ°çè¯æ³ä¸åå¨è¿ç¨ç±»ä¼¼ãä¹å°±æ¯è¯´ï¼ä½ éè¦å¨å£°æåæ°æ¶æå®OUTPUTåå¥ãä¾å¦ï¼ä¸é¢çéæ代ç ç®åçæ¼ç¤ºäºå¦ä½ä»å¨ææ¹å¤çä¸å©ç¨è¾åºåæ°@pæå¼è¿åå°å¤é¨æ¹å¤çä¸çåé@i.
DECLARE @sql AS NVARCHAR(12),@i AS INT;
SET @sql = N' SET @p = 10';
EXEC sp_executesql
@sql,
N'@p AS INT OUTPUT',
@p = @i OUTPUT
SELECT @i --该代ç è¿åè¾åº10
以åæ¯ N 为åç¼æ è¯ Unicode å符串常é
æ»ç»ä»¥ä¸å ç¹ï¼
ä¸.使ç¨exce sp_executesqlæçæ¯execè¦é«ï¼åä¸ç±»åçè¯å¥ï¼åªéç¼è¯ä¸æ¬¡å³å¯ï¼èexecæ§è¡å 次就éè¦ç¼è¯å 次ã
äº.æé å¨æsqlçwhereåå¥ï¼ä¹å°±æ¯æ¡ä»¶åå¥æ¶ï¼execæ æ³ä½¿ç¨åéæ¥è¿è¡ç«ä½ï¼éè¦å°åé转æ¢æå符串ï¼ç¶ååå¨æsqlè¿è¡æ¼æ¥ï¼è¿å°±å¯è½å¼èµ·Sql注å
¥é®é¢ï¼å¦ä¸ï¼
SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) +
ãã' WHERE OrderID = '+CAST(@OrderID AS VARCHAR(50)) + ' ORDER BY ORDERID DESC'
èè¥ä½¿ç¨exec sp_executesqlåå¯ä»¥ä½¿ç¨åéæ¥è¿è¡ç«ä½ï¼ä»¥ååç»è¿ä¸ªåæ°ä¼ å¼çæ¾å¼æé å¨æsqlï¼å°±é¿å
çSql注å
¥çé®é¢ï¼å¦ä¸ï¼
SET @sql = 'SELECT * FROM '+@TableName + ' WHERE OrderID = @OID ORDER BY ORDERID DESC'
ä¸.æ 论æ¯Execè¿æ¯Exec sp_executesqlï¼å¦ææ³è¦å°è¡¨ååååè¿è¡å¨æåæ°åï¼ä¸å¯ä»¥ä½¿ç¨è¡¨ååæ°ååååæ°æ¥è¿è¡ç«ä½ï¼èä¸è¡¨ååæ°ååååæ°éè¦ä½¿ç¨åå¨è¿ç¨çåæ°.对 äºexec sp_executesqlæ¥è¯´ï¼ä¸å¯ä»¥å°è¡¨ååæ°ååååæ°å¨æå®ä¸ºå¨exec sp_executesqlåæ°å£°æé¨å声æçåæ°ï¼å¦ï¼
代ç
create PROCEDURE GetData
@tbName nvarchar(10),
@colName nvarchar(10),
@Name nvarchar(10)
AS
BEGIN
declare @sql nvarchar(50);
set @sql='select '+ @colName+' from ' +@tbName+ ' where name=@whereName';
--注ææ¤å¥ä¸å¯ä»¥åæå¦ä¸:
-- set @sql='select @colName from @tbName where name=@whereName';
exec sp_executesql
@sql,
N'@whereName nvarchar(10)',
@Name
END
温馨提示:答案为网友推荐,仅供参考