如何在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

推荐答案

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 [ ^ ]

问候
罗伯特

try to use EXEC command:

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[^]

Regards
Robert

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'')

注意:这只是一个例子.

Note: it is just a example.

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 ]
[;]

这篇关于如何在sp中编写动态sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！