SQL Server，表达式可以调用模块中的代码吗？ [英] SQL Server, expressions can call code in modules??
问题描述
你好，
我们有Access数据库（后端），最终将整合到SQL Server数据库中，以便于数据报告， />
分析等
一个Access数据库前端的一些查询包含表达式，这些表达式在Access前端的Access模块中有参考代码结束，主要是来自mvps.org/access的
，它们将连接除主查询表之外的*其他*附加表中各种
字段的数据。
其他查询使用Mid函数在同一个表中连接
中的字段值。
此类查询是否可以结转到SQL Server 2005，或者我必须以某种方式重写那些查询吗？
我有Chapman / Baron参考书并将阅读很多
最终。
谢谢你，汤姆
< blockquote> tlyczko写道：
您好，
我们有Access数据库（后端），最终将整合到SQL Server中的
数据库，便于数据报告，
分析等。
一个Access数据库前端的一些查询包含表达式
Access前端的Access模块中的参考代码，主要是来自mvps.org/access的参考代码，它们将连接*其他*其他*表中的各种
字段中的数据主查询表。
其他查询使用Mid函数在同一个表中连接
的字段值。
这样的查询可以转移到SQL Server 2005，还是我必须以某种方式重写那些查询？b
$ b
你要么必须重新编写它们才能在SQL Server（SP或UDF）上获得相同的功能
）或者您可以使用SQL查询或SP将基本数据返回到Access，然后仍然使用您返回的
结果中的Access函数。
（将数据移动到SQL Server并不意味着所有查询都被移动到
SQL Server）。
- -
Rick Brandt，Microsoft Access MVP
电子邮件（视情况而定）至...
在Hunter dot com的RBrandt
tlyczko写道：
这样的查询可以转移到SQL Server 2005，还是我必须
以某种方式改写那些查询？
编号是。
您可以在线进行计算。也许更好，你可以在SQL-Servers T-SQL中创建
函数，并在你的Stored
程序中使用它们。
例如：
ALTER FUNCTION [dbo]。[ProperCase]
（
@VarString varchar（8000）
）
RETURNS varchar（8000）
AS
BEGIN
DECLARE @NewString varchar （8000）
DECLARE @Length int
DECLARE @Position int
DECLARE @CharAtPosition varchar（1）
DECLARE @ASCIIOfChar tinyint
DECLARE @WordStart位
SET @NewString =''''
SET @Length = LEN（ @VarString）
SET @Position = 1
SET @WordStart = 1
WHILE（@Position< = @Length ）
BEGIN
SET @CharAtPosition = LOWER（SUBSTRING（@VarString，@ Position，1））
IF（@WordStart = 1）
BEGIN
SET @CharAtPosition = UPPER（@CharAtPosition）
END
S ET @ASCIIOfChar = ASCII（@CharAtPosition）
IF（（@ ASCIIOfChar> 64 AND @ASCIIOfChar< 92）或（@ ASCIIOfChar> 96 AND
@ ASCIIOfChar< 123） ）
SET @WordStart = 0
ELSE
SET @WordStart = 1
SET @ NewString = @NewString + @CharAtPosition
SET @Position = @Position + 1
END
RETURN @ NewString
END
可以使用：
创建程序MakeFFDBAAccountsProperCase
AS
UPDATE FFDBAAccounts SET CommonName = dbo.ProperCase（CommonName）
RETURN
< Rick Brandt写道：
你要么必须重新编写它们才能获得相同的功能
$ SQL Server上的b $ b（SP或UDF）或者您可以使用SQL查询或SP将基本数据返回到Access，然后仍然使用您的Access函数$>
结果返回。
（将数据移动到SQL Server并不意味着所有查询都被移动到
SQL Server） 。
Rick，从您的第二条评论来看，听起来像是为了查询使用Access模块代码的几个查询
，我应该使用对链接的SQL表访问查询
???并且只是将这种特殊的
代码放在前端??。
这是最简单的做事方式，但如果有可能的话
让我在SQL 2005查询中做到这一点，我不会
仅限于使用Access这个特定的查询吗？
以下是使用Mid
公式的select查询中的SQL代码本身的一部分（我从某个地方获得了它）：
Mid（12+ [txtDescription]&"，" + [memComments]&"，
" + [memMedicationErrorComments]，3）AS txtAdditionalInformation
以下是来自select查询的SQL代码本身的示例，其中
访问模块调用：
txtStaffResponsible：
fConcatChild（" tblIncidentStaff"，" IncidentID"，" txtS taffName"，" Long"，[tblIncidents]。[IncidentID]）
fConcatChild是一个函数来自
http://www.mvps.org/access/modules/mdl0004.htm 从子表的字段中连接
字段值。
谢谢，
Tom
Hello,
We have Access databases (backends) that will eventually be
consolidated into a SQL Server database, to facilitate data reporting,
analysis, etc.
Some queries in one Access database front end contain expressions that
reference code in Access modules in the Access front end, primarily the
ones from mvps.org/access that will concatenate data from various
fields in *other* additional tables than the main query table.
Other queries use the Mid function to concatenate field values from
within the same table.
Can such queries be carried over to SQL Server 2005, or will I have to
rewrite those queries somehow??
I have the Chapman/Baron reference book and will be reading that a lot
eventually.
Thank you, Tom
tlyczko wrote:Hello,
We have Access databases (backends) that will eventually be
consolidated into a SQL Server database, to facilitate data reporting,
analysis, etc.
Some queries in one Access database front end contain expressions that
reference code in Access modules in the Access front end, primarily
the ones from mvps.org/access that will concatenate data from various
fields in *other* additional tables than the main query table.
Other queries use the Mid function to concatenate field values from
within the same table.
Can such queries be carried over to SQL Server 2005, or will I have to
rewrite those queries somehow??You''ll either have to re-write them so that you get the same functionality
on the SQL Server (SPs or UDFs) or you can use a SQL query or SP to return
the basic data to Access and then still use your Access functions on the
results that are returned.
(moving data to SQL Server does not mean all of your queries are moved to
SQL Server).
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
tlyczko wrote:
Can such queries be carried over to SQL Server 2005, or will I have to
rewrite those queries somehow??No. Yes.
You can do the calculations in line. Perhaps better, you can create
Functions within the SQL-Servers T-SQL and use them in your Stored
Procedures.
An example:
ALTER FUNCTION [dbo].[ProperCase]
(
@VarString varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @NewString varchar(8000)
DECLARE @Length int
DECLARE @Position int
DECLARE @CharAtPosition varchar(1)
DECLARE @ASCIIOfChar tinyint
DECLARE @WordStart bit
SET @NewString = ''''
SET @Length = LEN (@VarString)
SET @Position = 1
SET @WordStart = 1
WHILE (@Position <= @Length)
BEGIN
SET @CharAtPosition = LOWER(SUBSTRING (@VarString, @Position, 1))
IF (@WordStart = 1)
BEGIN
SET @CharAtPosition = UPPER (@CharAtPosition)
END
SET @ASCIIOfChar = ASCII(@CharAtPosition)
IF ((@ASCIIOfChar>64 AND @ASCIIOfChar<92) OR (@ASCIIOfChar>96 AND
@ASCIIOfChar<123))
SET @WordStart = 0
ELSE
SET @WordStart = 1
SET @NewString = @NewString + @CharAtPosition
SET @Position = @Position + 1
END
RETURN @NewString
END
And it could be used:
CREATE PROCEDURE MakeFFDBAAccountsProperCase
AS
UPDATE FFDBAAccounts SET CommonName = dbo.ProperCase(CommonName)
RETURN
Rick Brandt wrote:
You''ll either have to re-write them so that you get the same functionality
on the SQL Server (SPs or UDFs) or you can use a SQL query or SP to return
the basic data to Access and then still use your Access functions on the
results that are returned.
(moving data to SQL Server does not mean all of your queries are moved to
SQL Server).Rick, from your second comment, it sounds like for the few queries
where the query uses Access module code, I should use an Access query
against the linked SQL tables??? and just put this particular kind of
code in the front end??.
That would be the simplest way to do things, but if it were possible
for me to figure out doing this in a SQL 2005 query, I would not be
limited to using Access only for this particular query??
Here is part of the SQL code per se from the select query with the Mid
formula (I got it from this NG somewhere):
Mid("12"+[txtDescription] & ", "+[memComments] & ",
"+[memMedicationErrorComments],3) AS txtAdditionalInformation
Here is an example of the SQL code per se from the select query with
the Access module call:
txtStaffResponsible:
fConcatChild("tblIncidentStaff","IncidentID","txtS taffName","Long",[tblIncidents].[IncidentID])
fConcatChild is a function from
http://www.mvps.org/access/modules/mdl0004.htm, which concatenates
field values from a child table''s field.
Thank you,
Tom
这篇关于SQL Server，表达式可以调用模块中的代码吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
