大大们好,请教个问题,对于字符串拼凑的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 '+@Where
end如何给@Where,@AllPrice赋值呢?一个参数我知道这样写没有问题:exec sp_executesql @sqlcount,N'@count int output',@count output!两个就出现未定义问题
@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 '+@Where
end如何给@Where,@AllPrice赋值呢?一个参数我知道这样写没有问题:exec sp_executesql @sqlcount,N'@count int output',@count output!两个就出现未定义问题
解决方案 »
- 字段设计的问题
- 【提问】通过主键索引从键的记录
- 如何写这个sql语句
- 高分求教如何得到一个分组统计的数据,内容见帖,在线等待
- 网站服务器不稳定,隔三差五的打开网页时提示:ECSHOP info: Can't Connect MySQL Server(localhost:3306)!
- 问一个SQL2005插入的问题
- 请教大家一个关于多字段查询的SQL语句写法,对我来说有难度
- 急啊,怎样求平均时间差(秒)?
- XP中的猫与98中的猫不同
- pb7.0的打包问题,如何实现动态修改数据结构
- 给定2个日期,怎么按周统计这2个日期之间的周
- SQL里有一列默认值是0或者1 ,1是有效,0是无效,怎么在输出的时候,显示为有效或者无效
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
godeclare @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,@alldrop 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 @compareprice money, @cost money
EXECUTE Production.usp_GetList '%Bikes%', 700,
@compareprice OUT,
@cost OUTPUT
IF @cost <= @compareprice
BEGIN
PRINT 'These products can be purchased for less than
$'+RTRIM(CAST(@compareprice AS varchar(20)))+'.'
END
ELSE
PRINT 'The prices for all products in this category exceed
$'+ RTRIM(CAST(@compareprice AS varchar(20)))+'.'
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