动态语句学习·！！！~！

 -----用exec----- select * from tablename exec('select * from tablename') --------用sp_executesql---- exec sp_executesql N'select * from tablename' ---一定要加上N ------------------------------------------------------------- -------当属性名，表名，或数据库名都作为变量时，必须用动态sql ------------------------------------------------------------- declare @fname varchar(20) set @fname='fieldname' select @fname from tablename ---不能执行，会报错 exec('select '+@fname+'from tablename')---可以执行 --------- declare @s varchar(1000) set @s='select '+@fname+' from tablename' exec(@s)---可以执行 exec sp_executesql @s ---不可执行，报错 ------------ declare @s nvarchar(1000) set @s='select '+@fname+' from tablename' exec(@s)---此句正确 sp_executesql @s---此句正确 --------------- -----输出参数--------- declare @num int, @sqls nvarchar(4000) set @sqls='select count(*) from tablename' exec(@sqls)---right ------------- -------------- --------如何把exec的值传入另外定义的变量中 declare @num int, @sqls nvarchar(4000) set @sqls='select @c=count(*) from tablename' exec sp_executesql @sqls,N'@c int output',@num output ---@num=@c select @num 动态语句的一般调试方法说说动态语句的一般调试方法 declare @sqlstr nvarchar(500), @SaleNum int, @Style varchar(10), @StoreId varchar(10), @TName varchar(10) Set @TName='200608' Set @StoreId='24' Set @Style='W16004' Set @sqlstr=N'Select @SaleNum=SaleNum from ['+@TName+'] where ['+@TName+'].StoreID='+@StoreID+' and ['+@TName+'].Style='+rtrim(@Style) print @sqlstr --print出sql语句可以很快定位错误 Set @sqlstr=N'Select @SaleNum=SaleNum from ['+@TName+'] where ['+@TName+'].StoreID='+@StoreID+' and ['+@TName+'].Style='''+rtrim(@Style)+'''' print @sqlstr Execute sp_executesql @sqlstr,N'@SaleNum int out',@SaleNum out