using System;

public class Program

{

    public static void Main()

    {

        /*

https://dotblogs.com.tw/hunterpo/2010/02/05/13488

​

--==========================================My

​

-------使用exec

declare @sql nvarchar(max)

SET @sql = N'select top 100 * from %s p where p.ProductID <= %s  and p.ProductStatus = %s'

select @sql = FORMATMESSAGE(@sql, 'Productinfo', '11', '5')

exec (@sql)

​

-------使用sp_executesql

--sp_executesql中敘述句中TableName不能使用參數

--所以用FORMATMESSAGE的方式置換

-------

declare @sql nvarchar(max)

declare @paramsDef nvarchar(max)

​

SET @sql = N'select top 100 * from %s p where p.ProductID <= @pid  and p.ProductStatus = @sta'; 

​

select @sql = FORMATMESSAGE(@sql, 'Productinfo')

​

SET @paramsDef = N' @pid int, @sta int';

​

EXEC dbo.sp_executesql

        @Stmt = @sql,

        @Params = @paramsDef,

        @pid = 11,

        @sta = 5

​

--==========================================Web

--exec

GO

​

DECLARE @ProductID int

SET @ProductID = 1;

​

DECLARE @cmd VarChar(60);

SET @cmd = 'SELECT * FROM dbo.Products WHERE (ProductID = @ProductID);';

EXEC(@cmd); --使用方式

GO

​

---------------------

​

GO

​

CREATE PROC [dbo].[usp_executesql]

    @cmd NVarChar(100)

AS

BEGIN

    IF LEN(@cmd) > 0

    BEGIN

        EXEC (@cmd)

        EXEC sp_executesql @cmd

    END

    ELSE

        RAISERROR ('您必須指定查詢指令。', -- 錯誤訊息

               16, -- 錯誤層級碼

               1 -- 錯誤狀態碼

               );

END

GO

​

​

--sp_executesql

@paramsDef NVarChar(20),

@cnt int

​

SET @sql = N'SELECT @Cnt = COUNT(*) FROM [dbo].[Products];'; 

SET @paramsDef = N'@Cnt int OUTPUT'; -- 內嵌參數宣告為輸出參數

​

EXEC dbo.sp_executesql --使用方式

        @Stmt = @sql,

        @Params = @paramsDef,    

        @Cnt = @cnt OUTPUT;

SELECT @cnt AS [total_products];

*/

        Console.WriteLine("Hello World");

    }

}