大大们好，请教个问题，对于字符串拼凑的sql语句，如何获取多个output参数赋值
create procedure UP_Test
@Where as varhcar(100),
@PageCount as int=0 output,
@AllPrice as decimal(18,2)=0 output
as
begin
delcare @sqlcount='select @PageCount=count(1),@AllPrice=sum(price) from T_Test where 1=1 [email protected]
end
[email protected]，@AllPrice赋值呢？一个参数我知道这样写没有问题：exec sp_executesql @sqlcount,[email protected] int output',@count output！两个就出现未定义问题
------解决方案--------------------
- SQL code
create table tb (num int)insert into tb select 1 union select 2 union select 3declare @sql1 nvarchar(2000) declare @cou int declare @cou1 int declare @id nvarchar(20) set @id='1' set @sql1='select @count=count(*) from tb where [email protected] select @count1=count(*) from tb where [email protected]' exec sp_executesql @sql1, [email protected] int out,@count1 int out,@id varchar(20)', @cou out ,@cou1 out ,@id select @cou ,@cou1/*1 1*/
------解决方案--------------------
- SQL code
create table tb(id int,ic int,ik decimal(18,2))insert into tbselect 1,2,3 union allselect 1,3,3godeclare @id intdeclare @count intdeclare @all decimal(18,2)declare @sqlcount nvarchar(1000)set @id = 1set @sqlcount = isnull(@sqlcount,'') + 'select @count=count(1),@all=sum(ik) from tb where 1=1 and id = '+ltrim(@id)exec sp_executesql @sqlcount,[email protected] int output,@all decimal(18,2) output',@count output,@all outputselect @count,@alldrop table tb/************** ----------- ---------------------------------------2 6.00(1 行受影响)
------解决方案--------------------
- SQL code
USE AdventureWorks;GOIF OBJECT_ID ( 'Production.usp_GetList', 'P' ) IS NOT NULL DROP PROCEDURE Production.usp_GetList;GOCREATE PROCEDURE Production.usp_GetList @product varchar(40) , @maxprice money , @compareprice money OUTPUT , @listprice money OUTAS 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
------解决方案--------------------
- SQL code
输出参数 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 [email protected]_result set @QueryString='select @totalcount=count(*) from tablename' [email protected] set @[email protected] int output' --设置动态语句中参数的定义的字符串,多个参数用","隔开 exec sp_executesql @querystring,@paramstring,@[email protected]_result output select @output_result