sp_executesql


ตัวอย่างการใช้งาน
โดยสรุปแล้วมันก็เหมาะกับพวกที่เอาไปเขียนต่อ sql string มาแล้วก็ส่ง parameter เข้าไป execuete
จากประสบการณ์ทำงานเคยเจอที่เค้าจะทำการ create table ตามสาขา ถ้าสาขาเพิ่ม table เพิ่ม
ก็ต้องมานั่งเขียนเพื่อ dynanic sql แล้วเราก็เอา statement ที่ได้มา execute ด้วยคำสั่งนี้

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 AdventureWorks2008R2.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;
Advertisements
1 comment
  1. Declare @sql nvarchar(4000),
    @paramlist nvarchar(4000)

    SELECT @sql = 'SELECT DISTINCT TOP 10000000 E.RECORD_NO,
    E.ERROR_TYPE,
    E.ERROR_DESCRIPTION,
    CONVERT(VARCHAR(10), H.TRADE_DATE, 103) as TRADE_DATE,
    H.FUND_CODE,
    H.FUND_NAME,
    H.SEDOL,
    H.SECURITY_DESCRIPTION,
    H.COUNTRY_CODE,
    H.INVESTMENT_CLASS_NAME,
    H.QTY_ORIGINAL,
    H.QTY_OUTSTANDING,
    replace(H.INDUSTRY_GROUP_NAME,''NULL'','''') as INDUSTRY_GROUP_NAME, –H.INDUSTRY_GROUP_NAME,
    H.STOCK_EXCHANGE_NAME,
    H.TICKER,
    H.ASSET_CURRENCY,
    H.IMPORT_FILE_ID,
    H.IMPORT_GROUP,
    –H.ISUPDATE,
    case when ISUPDATE = 1 then ''Yes''
    else ''No''
    end as ISUPDATE,
    H.ISNEW,
    H.SYS_CRT_USERNAME,
    H.SYS_CRT_DATETIME,
    H.SYS_UPD_USERNAME,
    H.SYS_UPD_DATETIME,
    isnull(H.ISDELETE,''False'') AS ISDELETE,
    I.REGION_ID,
    E.MISMATCH_TYPE_ID

    FROM IMPORT_FILES_ERROR_LOG AS E with (index=IX_IMPORT_FILE_ID) INNER JOIN HOLDING_TMP AS H with (index=IX_IMPORT_FILE_ID) ON E.IMPORT_FILES_ID=H.IMPORT_FILE_ID AND E.RECORD_NO=H.RECORD_NO
    INNER JOIN IMPORT_FILES AS I with (nolock) ON I.IMPORT_FILES_ID = E.IMPORT_FILES_ID
    WHERE E.ERROR_TYPE NOT IN (''Reconciliation'')
    AND (H.ISDELETE <> ''True'' or H.ISDELETE = '''' or H.ISDELETE is null or H.ISDELETE = null) — Not delete
    AND 1 = 1'

    IF @IF_id <> 0
    SELECT @sql = @sql + ' AND H.IMPORT_FILE_ID = @xIF_id' +
    ' AND E.IMPORT_FILES_ID = @xIF_id'

    IF @MISMATCH_TYPE_ID <> 0
    SELECT @sql = @sql + ' AND E.MISMATCH_TYPE_ID = @xMISMATCH_TYPE_ID'

    IF @REGION_ID IS NOT NULL
    SELECT @sql = @sql + ' AND I.REGION_ID = @xREGION_ID'

    IF @ERROR_TYPE <> 'ALL'
    SELECT @sql = @sql + ' AND E.ERROR_TYPE = @xERROR_TYPE'

    IF @ERROR_CATEGORY_CODE <> 'ALL'
    SELECT @sql = @sql + ' AND E.ERROR_CATEGORY_CODE = @xERROR_CATEGORY_CODE'

    IF @debug = 1
    PRINT @sql

    SELECT @paramlist = '@xIF_id int,
    @xMISMATCH_TYPE_ID int,
    @xREGION_ID int,
    @xERROR_TYPE varchar(20),
    @xERROR_CATEGORY_CODE varchar(4)'

    exec sp_executesql @sql, @paramlist,
    @IF_id,@MISMATCH_TYPE_ID,@REGION_ID,@ERROR_TYPE, @ERROR_CATEGORY_CODE

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: