日期:2014-05-18 浏览次数:20553 次
create table tb (num int) insert into tb select 1 union select 2 union select 3 declare @sql1 nvarchar(2000) declare @cou int declare @cou1 int declare @id nvarchar(20) set @id='1' set @sql1='select @count=count(*) from tb where num=@id select @count1=count(*) from tb where num=@id' exec sp_executesql @sql1, N'@count int out,@count1 int out,@id varchar(20)', @cou out ,@cou1 out ,@id select @cou ,@cou1 /* 1 1 */
------解决方案--------------------
create table tb(id int,ic int,ik decimal(18,2)) insert into tb select 1,2,3 union all select 1,3,3 go declare @id int declare @count int declare @all decimal(18,2) declare @sqlcount nvarchar(1000) set @id = 1 set @sqlcount = isnull(@sqlcount,'') + 'select @count=count(1),@all=sum(ik) from tb where 1=1 and id = '+ltrim(@id) exec sp_executesql @sqlcount,N'@count int output,@all decimal(18,2) output',@count output,@all output select @count,@all drop table tb /************** ----------- --------------------------------------- 2 6.00 (1 行受影响)
------解决方案--------------------
USE AdventureWorks; GO IF OBJECT_ID ( 'Production.usp_GetList', 'P' ) IS NOT NULL DROP PROCEDURE Production.usp_GetList; GO CREATE PROCEDURE Production.usp_GetList @product varchar(40) , @maxprice money , @compareprice money OUTPUT , @listprice money OUT AS SELECT p.name AS Product, p.ListPrice AS 'List Price' FROM Production.Product p JOIN Production.ProductSubcategory s ON p.ProductSubcategoryID = s.ProductSubcategoryID WHERE s.name LIKE @product AND p.ListPrice < @maxprice; -- Populate the output variable @listprice. SET @listprice = (SELECT MAX(p.ListPrice) FROM Production.Product p JOIN Production.ProductSubcategory s ON p.ProductSubcategoryID = s.ProductSubcategoryID WHERE s.name LIKE @product AND p.ListPrice < @maxprice); -- Populate the output variable @compareprice. SET @compareprice = @maxprice; GO
------解决方案--------------------
输出参数 declare @num int, @sqls nvarchar(4000) set @sqls='select count(*) from tableName' exec(@sqls) --如何将exec执行结果放入变量中? declare @QueryString nvarchar(1000) --动态查询语名变量(注:必须为ntext或nchar哐nvarchar类型,不能是varchar类型) declare @paramstring nvarchar(200) --设置动态语句中的参数的字符串(注:必须为ntext或nchar哐nvarchar类型,不能是varchar类型) declare @output_result int--查询结果赋给@output_result set @QueryString='select @totalcount=count(*) from tablename' --@totalcount 为输出结果参数 set @paramstring='@totalcount int output' --设置动态语句中参数的定义的字符串,多个参数用","隔开 exec sp_executesql @querystring,@paramstring,@totalcount=@output_result output select @output_result