什么是继续运行方法的最佳方法(what is the best way to keep running a method)

 您可以使用以下过程来执行您想要的操作。 它只需要你： 
 
 
 
 在调用proc之前创建Temp Table（您将Temp Table名称传递给proc）。 这允许临时表在当前作用域中使用，因为一旦proc结束/返回，就会删除在存储过程中创建的临时表。 
 
 
 在Temp Table中只放一个字段; 数据类型是无关紧要的，因为该字段将被删除（您将字段名称传递给proc） 
 
 
 [ 确保将proc名称更改为您喜欢的任何名称，但在后面的示例中使用了temp proc名称 ] 
 
CREATE PROCEDURE #Abracadabra
(
    @TempTableName SYSNAME,
    @DummyFieldName SYSNAME,
    @TestMode BIT = 0
)
AS
SET NOCOUNT ON

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = COALESCE(@SQL + N', [',
                       N'ALTER TABLE ' + @TempTableName + N' ADD [')
               + [Title]
               + N'] VARCHAR(100)'
FROM #FieldList
ORDER BY [ID]

SET @SQL = @SQL
            + N' ; ALTER TABLE '
            + @TempTableName
            + N' DROP COLUMN ['
            + @DummyFieldName
            + N'] ; '

IF (@TestMode = 0)
BEGIN
    EXEC(@SQL)
END
ELSE
BEGIN
    PRINT @SQL
END
GO
 
 以下示例显示了正在使用的proc。 第一次执行是在测试模式下，它只打印将要执行的SQL。 第二次执行运行SQL和SELECT，然后EXEC显示字段是FieldList表中的字段。 
 
/*
-- HIGHLIGHT FROM "SET" THROUGH FINAL "INSERT" AND RUN ONCE
-- to setup the example

SET NOCOUNT ON;

--DROP TABLE #FieldList
CREATE TABLE #FieldList (ID INT, Title VARCHAR(50))

INSERT INTO #FieldList (ID, Title) VALUES (1, 'City')
INSERT INTO #FieldList (ID, Title) VALUES (2, 'UserSuppliedFieldName')
INSERT INTO #FieldList (ID, Title) VALUES (3, 'SomeField')
*/


IF (OBJECT_ID('tempdb.dbo.#Tmp') IS NOT NULL)
BEGIN
    DROP TABLE #Tmp
END
CREATE TABLE #Tmp (Dummy INT)

EXEC #Abracadabra
           @TempTableName = N'#Tmp',
           @DummyFieldName = N'Dummy',
           @TestMode = 1
-- look in "Messages" tab

EXEC #Abracadabra
           @TempTableName = N'#Tmp',
           @DummyFieldName = N'Dummy',
           @TestMode = 0

SELECT * FROM #Tmp
 
 @TestMode = 1输出： 
 
 
 
 ALTER TABLE #Tmp ADD [City] VARCHAR（100），[UserSuppliedFieldName] VARCHAR（100），[SomeField] VARCHAR（100）; ALTER TABLE #Tmp DROP COLUMN [Dummy]; 
 

You can use the following proc to do what you are wanting. It just requires that you: 
 
 
 
Create the Temp Table before calling the proc (you will pass in the Temp Table name to the proc). This allows the temp table to be used in the current scope as Temp Tables created in Stored Procedures are dropped once that proc ends / returns. 
 
 
Put just one field in the Temp Table; the datatype is irrelevant as the field will be dropped (you will pass in the field name to the proc) 
 
 
[Be sure to change the proc name to whatever you like, but the temp proc name is used in the example that follows] 
 
CREATE PROCEDURE #Abracadabra
(
    @TempTableName SYSNAME,
    @DummyFieldName SYSNAME,
    @TestMode BIT = 0
)
AS
SET NOCOUNT ON

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = COALESCE(@SQL + N', [',
                       N'ALTER TABLE ' + @TempTableName + N' ADD [')
               + [Title]
               + N'] VARCHAR(100)'
FROM #FieldList
ORDER BY [ID]

SET @SQL = @SQL
            + N' ; ALTER TABLE '
            + @TempTableName
            + N' DROP COLUMN ['
            + @DummyFieldName
            + N'] ; '

IF (@TestMode = 0)
BEGIN
    EXEC(@SQL)
END
ELSE
BEGIN
    PRINT @SQL
END
GO
 
The following example shows the proc in use. The first execution is in Test Mode that simply prints the SQL that will be executed. The second execution runs the SQL and the SELECT following that EXEC shows that the fields are what was in the FieldList table. 
 
/*
-- HIGHLIGHT FROM "SET" THROUGH FINAL "INSERT" AND RUN ONCE
-- to setup the example

SET NOCOUNT ON;

--DROP TABLE #FieldList
CREATE TABLE #FieldList (ID INT, Title VARCHAR(50))

INSERT INTO #FieldList (ID, Title) VALUES (1, 'City')
INSERT INTO #FieldList (ID, Title) VALUES (2, 'UserSuppliedFieldName')
INSERT INTO #FieldList (ID, Title) VALUES (3, 'SomeField')
*/


IF (OBJECT_ID('tempdb.dbo.#Tmp') IS NOT NULL)
BEGIN
    DROP TABLE #Tmp
END
CREATE TABLE #Tmp (Dummy INT)

EXEC #Abracadabra
           @TempTableName = N'#Tmp',
           @DummyFieldName = N'Dummy',
           @TestMode = 1
-- look in "Messages" tab

EXEC #Abracadabra
           @TempTableName = N'#Tmp',
           @DummyFieldName = N'Dummy',
           @TestMode = 0

SELECT * FROM #Tmp
 
Output from @TestMode = 1:
 
 
 
ALTER TABLE #Tmp ADD [City] VARCHAR(100), [UserSuppliedFieldName] VARCHAR(100), [SomeField] VARCHAR(100) ; ALTER TABLE #Tmp DROP COLUMN [Dummy] ;