SQL ×¢Èë
SQL×¢Èë¾Í²»ÓýéÉÜÁË£¬ÍøÉϺܶࡣÏÂÃæ½éÉÜһϷÀÖ¹
SQL×¢ÈëµÄ·½·¨¡£
ʹÓÃ
quotename º¯ÊýºÍ
sp_executesql
²Î¿¼Èç
ϱí½á¹¹£ºÕâÊÇÒ»¸öÎĵµ±íÀïÃæÓÐһЩ¼òµ¥µÄ×Ö¶ÎÐÅÏ¢
CREATE
TABLE
[dbo]
.
[DocumentInfo]
(
[ID]
[int]
IDENTITY
(
1,
1) primary key
NOT
NULL,--
Ö÷¼ü
[Name]
[varchar]
(
100)
NOT
NULL,--
ÎĵµÃû×Ö
[FunctionID]
[int]
NOT
NULL
£¬
--
¹¦ÄÜ
ID
Íâ¼ü
[TypeInfo]
[nvarchar]
(
2000)
NULL—
ÃèÊöÐÅÏ¢
£©
ÎÒÃÇ¿ÉÄܻᰴ£¬Ö÷¼ü
ID£¬ÎĵµµÄÃû×Ö£¬¹¦ÄÜ
id£¬
ÃèÊöÐÅÏ¢½øÐвéѯ
£¬Õâ¸öÒ»¿´¾ÍÊÇÆ´½Ó
sqlÓï¾ä£¬Èç¹û²»ÓÃÉÏÃæµÄ·½·¨ºÜ¿ÉÄÜÔì³É
sql×¢Èë
ÀýÈçÄãдµÄ´æ´¢¹ý³Ì¿ÉÄÜÈçÏ£º
CREATE
proc
[dbo]
.
[Doc_search]
(
@ID
int
,
@name
varchar
(
10),
@FID
int
,
@Info
nvarchar
(
20)
)
as
begin
declare
@sql
varchar
(
300)
set
@sql
=
'select * from
[DocumentInfo] where 1=1'
if
@ID
<>-
1
set
@sql
=
@sql
+
' and ID='
+
CAST
(
@ID
as
varchar
)
if
@name
<>
''
set
@sql
=
@sql
+
' and name='''
+
CAST
(
@name
as
varchar
)
+
''''
if
@FID
<>-
1
set
@sql
=
@sql
+
' and
[FunctionID]='
+
CAST
(
@FID
as
varchar
)
if
@Info
<>
''
set
@sql
=
@sql
+
' and [TypeInfo] like ''%'
+
CAST
(
@Info
as
varchar
)+
'%'''
print
@sql
end
exec
(
@sql
)
ËäÈ»ÄãÓÃÁË´ø²ÎÊýµÄ´æ´¢¹ý³Ì
£¬µ«±¾ÖÊÉÏ»¹ÊÇÆ´½Ó×Ö·û´®£¬Ã»ÓÐÓÐЧµÄ·ÀÖÎ
sql×¢Èë¡£ÏÖÔÚ
·Ö±ðÓÃ
quotename()º¯ÊýºÍ
sp_executesql¡£
Quotename()º¯Êý²»ÖªµÀÓõÄÇë×ÔÐвéÕÒ£¬
sp_executesqlµÄÓ÷¨
¼ûÉÏһƪ¶¯Ì¬
sql
Quotename¸Ä½øÈçÏ£º
alter
proc
[dbo]
.
[Doc_search]
(
@ID
int
,
@name
varchar
(
10),
@FID
int
,
@Info
nvarchar
(
20)
)
as
begin
declare
@sql
varchar
(
300)
set
@sql
=
'select * from
[DocumentInfo] where 1=1'
if
@ID
<>-
1
set
@sql
=
@sql
+
' and ID='
+
CAST
(
@ID
as
varchar
)
Ïà¹ØÎĵµ£º
ÒÔÏÂÊÇÕª×ÔOracle¹ÙÍø:
¢ñ Oracle SQL Developer ÊÇÒ»¸öÃâ·ÑµÄͼÐλ¯Êý¾Ý¿â¿ª·¢¹¤¾ß¡£Ê¹Óà SQL Developer£¬Äú¿ÉÒÔä¯ÀÀÊý¾Ý¿â¶ÔÏó¡¢ÔËÐÐ SQL Óï¾äºÍ SQL ½Å±¾£¬²¢ÇÒ»¹¿ÉÒԱ༺͵÷ÊÔ PL/SQL Óï¾ä¡£Äú»¹¿ÉÒÔÔËÐÐËùÌṩµÄÈκÎÊýÁ¿µÄ±¨±í£¬ÒÔ¼°´´½¨ºÍ±£´æÄú×Ô¼ºµÄ±¨±í¡£SQL Developer ¿ÉÒÔÌá¸ß¹¤×÷ЧÂʲ¢¼ò»¯Êý¾Ý¿â¿ª·¢ÈÎÎñ¡£ ......
±ÜÃâʹÓÃIN ²Ù×÷·û
ÓÃINд³öÀ´µÄSQLµÄÓŵãÊDZȽÏÈÝÒ×д¼°ÇåÎúÒ×¶®£¬Õâ±È½ÏÊʺÏÏÖ´úÈí¼þ¿ª·¢µÄ·ç¸ñ¡£
µ«ÊÇÓÃINµÄSQLÐÔÄÜ×ÜÊDZȽϵ͵쬴ÓORACLEÖ´ÐеIJ½ÖèÀ´·ÖÎöÓÃINµÄSQLÓë²»ÓÃINµÄSQLÓÐÒÔÏÂÇø±ð£º
¡¡ORACLEÊÔͼ½«Æäת»»³É¶à¸ö±íµÄÁ¬½Ó£¬Èç¹ûת»»²»³É¹¦ÔòÏÈÖ´ÐÐINÀïÃæµÄ×Ó²éѯ£¬ÔÙ²éѯÍâ²ãµÄ±í¼Ç ......
ʹÓÃLINQ to SQL½¨Ä£NorthwindÊý¾Ý¿â
ÔÚÕâ֮ǰһÆðѧ¹ýLINQ to SQLÉè¼ÆÆ÷µÄʹÓã¬ÏÂÃæ¾ÍʹÓÃÈçϵÄÊý¾ÝÄ£ÐÍ£º
µ±Ê¹ÓÃLINQ to
SQLÉè¼ÆÆ÷Éè¼ÆÒÔÉ϶¨ÒåµÄÎå¸öÀࣨProduct£¬Category£¬Customer£¬OrderºÍOrderDetail£©µÄʱºò£¬Ã¿¸öÀàÖеÄÊôÐÔ
¶¼Ó³ÉäÁËÏàÓ¦Êý¾Ý¿âÖбíµÄÁУ¬Ã¿¸öÀàµÄʵÀýÔò´ú±íÁËÊý¾Ý¿â±íÖеÄÒ»Ìõ¼Ç¼¡£ÁíÍ⣬ ......
SQLÓï¾ä¼¶±ðµÄÓÅ»¯ÊÇÎÒÃÇʵ¼Ê¹¤×÷ÖнӴ¥×î¶àµÄ£¬¼¸ºõËùÓеÄÊý¾Ý¿âÓ¦ÓÃ˵µ½µ×¶¼ÊÇSQLÓï¾äµÄ¾ßÌåÖ´ÐУ¬Òò´ËÈçºÎ¶ÔSQL½Å±¾±¾Éí½øÐÐÓÅ»¯£¬Ìá¸ßÖ´ÐÐЧÂʾÍÏÔµÃÓÈÎªÖØÒª¡£ÒÔÏÂÊÇһЩͨÓõÄÉè¼ÆÔÔò¡£
(1)ʹÓÃselectÓï¾ä²éѯʱ£¬¾¡Á¿²»ÒªÊ¹ÓÃselect *¡¡Óï¾ä£¬¶øÊÇÓ¦¸ÃÖ¸Ã÷¾ßÌåÒª²éѯµÄ×ֶΣ¬ÌáÈ¡µÄ×Ö¶ÎÊýÁ¿Ô½É ......