SQL：编写更简洁的paramaterized SQL [英] SQL: writing more concise paramaterized SQL

问题描述

我正在尝试使用参数化SQL。我已经开始工作，

但是现在我的一些查询看起来不必要了。例如：


strSQL =" IF NOT EXISTS（SELECT * FROM We_Link_SiteMenus_To_DirectoryContacts

WHERE contactID =？AND pageID =？）INSERT INTO

We_Link_SiteMenus_To_DirectoryContacts（contactID，pageID）VALUES（？，？）"


objCommand.Parameters.Add（" @ contactID"，

System.Data.OleDb.OleDbType.Numeric）.Value = Request（" hid_contactIDadd_"&

contactsCount）

objCommand.Parameters.Add（" @ pageID" ，

System.Data.OleDb.OleDbType.Numeric）.Value = Request.QueryString（" pageID"）

objCommand.Parameters.Add（" @ contactID" ，

System.Data.OleDb.OleDbType.Numeric）.Value = Request（" hid_contactIDadd_"&

contactsCount）

objCommand。 Parameters.Add（" @ pageID"，

System.Data.OleDb.OleDbType.Numeric）.Value = Request.QueryString（" pageID"）


如你所见，我是代表两次吃这两个值。是否有更简洁的

方式传递这些参数？


-Darrel

I''m trying to get the hang of using parameterized SQL. I''ve gotten to work,
but now some of my queries seem unecessarily long. For instance:

strSQL = "IF NOT EXISTS(SELECT * FROM We_Link_SiteMenus_To_DirectoryContacts
WHERE contactID = ? AND pageID = ?) INSERT INTO
We_Link_SiteMenus_To_DirectoryContacts (contactID, pageID) VALUES (?, ?)"

objCommand.Parameters.Add("@contactID",
System.Data.OleDb.OleDbType.Numeric).Value = Request("hid_contactIDadd_" &
contactsCount)
objCommand.Parameters.Add("@pageID",
System.Data.OleDb.OleDbType.Numeric).Value = Request.QueryString("pageID")
objCommand.Parameters.Add("@contactID",
System.Data.OleDb.OleDbType.Numeric).Value = Request("hid_contactIDadd_" &
contactsCount)
objCommand.Parameters.Add("@pageID",
System.Data.OleDb.OleDbType.Numeric).Value = Request.QueryString("pageID")

As you can see, I''m repeating the two values twice. Is there a less verbose
way to pass those parameters?

-Darrel

推荐答案

在2006年3月29日星期三13:54:31 -0600，darrel写道：

On Wed, 29 Mar 2006 13:54:31 -0600, darrel wrote:

正如你所看到的，我正在重复这两个值两次。是否有更简洁的方式来传递这些参数？

As you can see, I''m repeating the two values twice. Is there a less verbose
way to pass those parameters?

strSQL =" IF NOT EXISTS（SELECT * FROM e_Link_SiteMenus_To_DirectoryContacts

WHERE contactID = @contactID AND pageID = @pageID）INSERT INTO

We_Link_SiteMenus_To_DirectoryContacts（contactID，pageID）VALUES

（@ contactID，@ pageID）"

strSQL = "IF NOT EXISTS(SELECT * FROM e_Link_SiteMenus_To_DirectoryContacts
WHERE contactID = @contactID AND pageID = @pageID) INSERT INTO
We_Link_SiteMenus_To_DirectoryContacts (contactID, pageID) VALUES
(@contactID, @pageID)"

strSQL =" IF NOT EXISTS（SELECT * FROM
e_Link_SiteMenus_To_DirectoryContacts
WHERE contactID = @contactID AND pageID = @pageID） INSERT INTO
We_Link_SiteMenus_To_DirectoryContacts（contactID，pageID）VALUES
（@ contactID，@ pageID）"

strSQL = "IF NOT EXISTS(SELECT * FROM
e_Link_SiteMenus_To_DirectoryContacts
WHERE contactID = @contactID AND pageID = @pageID) INSERT INTO
We_Link_SiteMenus_To_DirectoryContacts (contactID, pageID) VALUES
(@contactID, @pageID)"

我一直看到这个用过在示例中，但我从来没有能够使用

'''''语法来使用MSSql。事实上，人们已经说'''''是我需要使用的价格。


我会试一试，虽然; o）


-Darrel

I''ve always seen this used in examples, but I''ve never been able to get the
''@'' syntax to work with MSSql. In fact, folks have said that the ''?'' is what
I need to use.

I''ll give it a shot, though ;o)

-Darrel

我见过''？''在OleDB中使用，带有Access - 但是我一直都知道并且是原来教的那个
，使用SQL Server 2000及以上版本，使用'''''符号

以及那个，无论你是哪个' '在声明和参数中使用，

绝对应该匹配


David Wier
http://aspnet101.com/
http://aspexpress.com/

MCP-VB6 / SQL Server / MVP（ASP.Net）

darrel ; <无***** @ nowhere.com>在消息中写道

news：％2 **************** @ tk2msftngp13.phx.gbl ...

I''ve seen ''?'' used in OleDB, with Access - but I''ve always known and was
originally taught, that with SQL Server 2000 and above, to use the ''@'' sign
and that, whichever you''re using, in the statement and the parameters,
should definitely match

David Wier
http://aspnet101.com/
http://aspexpress.com/
MCP-VB6/SQL Server/MVP (ASP.Net)
"darrel" <no*****@nowhere.com> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...

strSQL =" IF NOT EXISTS（SELECT * FROM
e_Link_SiteMenus_To_DirectoryContacts
WHERE contactID = @contactID AND pageID = @pageID）INSERT INTO
We_Link_SiteMenus_To_DirectoryContacts（contactID，pageID）VALUES
（@ contactID，@ pageID）"

strSQL = "IF NOT EXISTS(SELECT * FROM
e_Link_SiteMenus_To_DirectoryContacts
WHERE contactID = @contactID AND pageID = @pageID) INSERT INTO
We_Link_SiteMenus_To_DirectoryContacts (contactID, pageID) VALUES
(@contactID, @pageID)"

我一直看到这个在例子中使用，但我从来没能得到
'@''语法与MSSql一起使用。事实上，人们已经说'''''
是我需要使用的。

我会试一试; o）
< br-> -Darrel

I''ve always seen this used in examples, but I''ve never been able to get
the ''@'' syntax to work with MSSql. In fact, folks have said that the ''?''
is what I need to use.

I''ll give it a shot, though ;o)

-Darrel

这篇关于SQL：编写更简洁的paramaterized SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！