Access数据库中的带参数嵌套查询怎么迁移到SQLSERVER中?

[suine]

下面的这些查询怎么迁移到SQLServer中啊?
我的access表中建立了一个查询,叫"全部借据",代码如下:

SELECT min(数据日期) AS 起始日期, 借据号
FROM 贷款原始信息
WHERE 数据日期>=[开始日期] And 数据日期<=[结束日期]
GROUP BY 借据号
ORDER BY min(数据日期);

另一个查询"全部借据明细"使用了"全部借据"这个查询,代码如下

SELECT 贷款原始信息.*
FROM 全部借据 INNER JOIN 贷款原始信息 ON (全部借据.借据号=贷款原始信息.借据号) AND (全部借据.起始日期=贷款原始信息.数据日期);

另一个查询"全部借据汇总"又使用了"全部借据明细"这个查询,代码如下:

SELECT 全部借据明细.业务所在地区, 支行代码.地区名称, 全部借据明细.科目, 科目.DICT_NAME AS 科目名称, Sum(全部借据明细.贷款余额) AS 全部金额
FROM (全部借据明细 LEFT JOIN 支行代码 ON 全部借据明细.业务所在地区=支行代码.地区代码) LEFT JOIN 科目 ON 全部借据明细.科目=科目.DICT_CODE
GROUP BY 全部借据明细.业务所在地区, 支行代码.地区名称, 全部借据明细.科目, 科目.DICT_NAME
HAVING 科目 not in (select 科目 from 排除科目)
ORDER BY 全部借据明细.业务所在地区, 全部借据明细.科目;

[suine]

我用自定义函数实现了，不知道存储过程怎么实现？

[csw200201]

create proc YourProcName(@start_date datetime, @enddate datetime) as
SELECT min(数据日期) AS 起始日期, 借据号
FROM 贷款原始信息
WHERE 数据日期>=@start_date And 数据日期<=@enddate
GROUP BY 借据号
ORDER BY 1
go

[pt1314917]

--可以考虑在sql中将全部借据的创建成视图，如：
create view 全部借据
as
    SELECT min(数据日期) AS 起始日期, 借据号
FROM 贷款原始信息
WHERE 数据日期>=[开始日期] And 数据日期<=[结束日期]
GROUP BY 借据号
ORDER BY min(数据日期)
go

--下面再直接去使用。

[suine]

SQLSERVER视图支持参数吗?[开始日期]和[结束日期]是参数

[suine]

这么用?好象不行吧...
select 贷款原始信息.*
from (exec 全部借据 '20100101','20100531') as tablea left join 贷款原始信息
where tablea.开始日期 = 贷款原始信息.数据日期 and tablea.借据号 = 贷款原始信息.借据号

[csw200201]

[Quote=引用 7 楼 suine 的回复:]
这么用?好象不行吧...
select 贷款原始信息.*
from (exec 全部借据 '20100101','20100531') as tablea left join 贷款原始信息
where tablea.开始日期 = 贷款原始信息.数据日期 and tablea.借据号 = 贷款原始信息.借据号
[/Quote]

No, you can't use it like that. This is a stored proc not a table valued function.

You should do your join/select statement inside the stored procedure rather than outside it (there are ways to do it - but for you as an access user, the topic is way too advanced).

The output of stored proc should go straight to your business logic layer. If you need additional processing on the data, do it in the proc.

[suine]

那我第二个和第三个存储过程应该怎么写？

[csw200201]

[Quote=引用 9 楼 suine 的回复:]
那我第二个和第三个存储过程应该怎么写？
[/Quote]

Do you mean you want to use the output from the first stored proc in another stored proc? If that is what you want, your additional stored proc should

(1) create a temp table (say #tmp) that matches the output definition of the first stored proc
(2) use insert into #tmp exec YourProcName '2010-01-01', '2010-05-31'
(3) use the data in #tmp anyway you like

However, having said that, you shouldn't have to do this in first place. Assuming your additional stored procs are as simple as the first one, you can easily include the logic from the first proc in your other procs.

[suine]

但是我的程序还要调用第一个存储进程生成的数据啊。。。
写到临时表里，我的程序怎么调？

[csw200201]

[Quote=引用 11 楼 suine 的回复:]
但是我的程序还要调用第一个存储进程生成的数据啊。。。
写到临时表里，我的程序怎么调？
[/Quote]
How do you want to use the data from the stored proc?

[suine]

我是想，一个存储过程返回的结果集，既可以被客户端使用，又可以被另一个存储过程使用。

[suine]

那我下一个存储过程怎么引用这个过程啊?

[csw200201]

[Quote=引用 5 楼 suine 的回复:]
那我下一个存储过程怎么引用这个过程啊?
[/Quote]

For example

exec YourProcName '2010-01-01', '2010-05-31'

[suine]

噢，看来想像ACCESS那样重用查询是不太容易了，谢过了。。

[csw200201]

[Quote=引用 15 楼 suine 的回复:]
噢，看来想像ACCESS那样重用查询是不太容易了，谢过了。。
[/Quote]

Well, not quite. There are ways you can get around it

For example, you can set up the SQL Server as a linked server on itself. You can then use openquery to return result to another stored proc.

This is not necessarily an efficient method as running query from linked server results in an RPC process being created and there is performance implications to it. The preferred method is to use exec insert method.

[csw200201]

[Quote=引用 13 楼 suine 的回复:]
我是想，一个存储过程返回的结果集，既可以被客户端使用，又可以被另一个存储过程使用。
[/Quote]

The resultset from a stored proc can be used using the suggestion I provided in 10 楼. The temporary table is created in your new stored proc, not the old one. At the end of your new stored proc, you simply call select * from #tmp to return the resultset to your calling program.