SQL

DECLARE @sql AS NVARCHAR(12),@i AS INT;

SET @sql = N' SET @p = 10';

EXEC sp_executesql 

    @stmt= @sql,

    @params = N'@p AS INT OUTPUT',

    @p= @i OUTPUT

SELECT @i

该代码返回输出10(EXEC  回传值)

CREATE PROCEDURE sp_Clear_Archive_Data(@path VARCHAR(200))--'C:\folder\'
AS
SELECT ROW_NUMBER() OVER(ORDER BY create_date) AS Num,SCHEMA_NAME(schema_id)+'.'+name AS TableName  INTO #tmpTableName FROM sys.tables
WHERE name like 'MKTG_%'
AND SCHEMA_NAME(schema_id) = 'ARCHIVE'
--AND create_date <= dateadd(month, -3, getdate())
ORDER BY create_date


DECLARE @parameter NVARCHAR(MAX), @tableName NVARCHAR(200);
DECLARE @row INT = 1,@state INT = 1;
WHILE(EXISTS(SELECT 1 FROM #tmpTableName WHERE Num = @row))
BEGIN
SELECT @parameter = 'EXEC @p = master..xp_cmdshell ''bcp "'+ TableName +'" out "' + @path + TableName + '.csv" -c -t"," -r"\n" -T''',
@tableName = TableName FROM #tmpTableName WHERE Num = @row;
SET @row += 1;
EXEC sp_executesql 
    @stmt = @parameter,
    @params = N'@p AS INT OUTPUT',
    @p = @state OUTPUT;
IF (@state = 0) EXEC ('DROP TABLE ' + @tableName);
ELSE PRINT 'Backup data failure';
END


DROP TABLE #tmpTableName;