动态SQL语句

<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 728x15, 创建于 08-4-23MSDN */ google_ad_slot = "3624277373"; google_ad_width = 728; google_ad_height = 15; //--> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 160x600, 创建于 08-4-23MSDN */ google_ad_slot = "4367022601"; google_ad_width = 160; google_ad_height = 600; //--> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

1:普通SQL语句可以用Exec执行
eg:  Select*fromtableName
     Exec('select*fromtableName')
     sp_executeSQLN'select*fromtableName'   --请注意字符串前一定要加N

2:字段名,表名,名之类作为变量时,必须用动态SQL
eg:  
declare@fnamevarchar(20)
set@fname='[name]'
Select@fnamefromsysobjects                    --错误
Exec('select'+@fname+'fromsysobjects')    --请注意加号前后的单引号的边上要加空格
execsp_executeSQLN'select'+@fname+'fromsysobjects'
当然将字符串改成变量的形式也可
   declare@svarchar(1000)
   set@s='select'+@fname+'fromsysobjects'
   Exec(@s)               --成功
   execsp_executeSQL@s  --此句会报错

   declare@sNvarchar(1000) --注意此处改为nvarchar(1000)
   set@s='select'+@fname+'fromsysobjects'
   Exec(@s)               --成功   
   execsp_executeSQL@s  --此句正确,

3:输出参数
eg:
declare@num,
       @SQLs
set@SQLs='selectcount(*)from '+@servername+'.a.dbo.b'
exec(@SQLs)
我如何能将exec执行的结果存入变量@num中

declare@numint,
       @SQLsnvarchar(4000)
set@SQLs='select@a=count(*)from'+@servername+'.a.dbo.b'
execsp_executeSQL@SQLs,N'@aintoutput',@numoutput
select@num


<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 728x15, 创建于 08-4-23MSDN */ google_ad_slot = "3624277373"; google_ad_width = 728; google_ad_height = 15; //--> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 160x600, 创建于 08-4-23MSDN */ google_ad_slot = "4367022601"; google_ad_width = 160; google_ad_height = 600; //--> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
阅读更多
文章标签: sql n2
个人分类: 数据库
上一篇如何修复SQLSERVER置疑之
下一篇从SQL中的一个表中导出文件表格
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭