如何在sp中编写动态sql[英] how to write dynamic sql in sp

问题描述

select @amount=tax_amnt from @tblnm where @trn_col_nm=@trn_No and tax_nm=@taxnm

这是我的sql,可以用dynmaicsql写吗?
解决方案
hi,

尝试使用 EXEC 命令:

http://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/[^]

http://www.techrepublic.com/blog/datacenter/generate-dynamic-sql-statements-in-sql-server/306[^]

http://msdn.microsoft.com/en-us/library/ms188332.aspx[^]

问候
罗伯特

use "exec" function this way
dec @myamt varchar(20);
exec(''
declare @amount decimal(12,3);
set  @amount=''+@myamt+''
select @amount=tax_amnt from @tblnm where @trn_col_nm=@trn_No and tax_nm=@taxnm'')


注意:这只是一个例子.

Execute a stored procedure or function
[ { EXEC | EXECUTE } ]
    { 
      [ @return_status = ]
      { module_name [ ;number ] | @module_name_var } 
        [ [ @parameter = ] { value 
                           | @variable [ OUTPUT ] 
                           | [ DEFAULT ] 
                           }
        ]
      [ ,...n ]
      [ WITH RECOMPILE ]
    }
[;]

Execute a character string
{ EXEC | EXECUTE } 
    ( { @string_variable | [ N ]''tsql_string'' } [ + ...n ] )
    [ AS { LOGIN | USER } = '' name '' ]
[;]

Execute a pass-through command against a linked server
{ EXEC | EXECUTE }
    ( { @string_variable | [ N ] ''command_string [ ? ]'' } [ + ...n ]
        [ { , { value | @variable [ OUTPUT ] } } [ ...n ] ]
    ) 
    [ AS { LOGIN | USER } = '' name '' ]
    [ AT linked_server_name ]
[;]

本文地址:https://www.itbaoku.cn/post/1395166.html