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
)
Ïà¹ØÎĵµ£º
¹¤×÷ÖÐÅöµ½µÄС¼¼ÇÉ£¬±ÈÈç˵ҪÓÃÊý¾Ý±íÀïÈ¡³ö×¢²áÓû§µÄ×ÊÁÏ£¬ÐÔ±ðΪ1ÔòÏÔʾΪÄÐÉú£»ÐÔ±ðΪ2ÔòÏÔʾΪŮÉú£»ÒÔǰ¶¼ÊÇ´Ó±íÀïÈ¡³öÊý¾Ý£¬È»ºóÔÙÓôúÂë½øÐÐÅжϣ¬Îª1ÔòÏÔʾΪÄÐÉú£»Îª2ÔòÏÔʾΪŮÉú£»Æäʵµ¥ÓÃSQLÓï¾ä¾Í¿ÉÒÔ×öµ½£¬ÈçÏ£º
Ö±½ÓʹÓÃcase when else Óï¾ä
Case¾ßÓÐÁ½ÖÖ¸ñʽ¡£¼òµ¥Caseº¯ÊýºÍCaseË ......
Êý¾Ý¿â¿ìÕÕÊÇMSSQL2005µÄй¦ÄÜ£¬½öÔÚ Microsoft SQL Server 2005 Enterprise Edition ÖпÉÓ᣶øÇÒSQL Server Management Studio ²»Ö§³Ö´´½¨Êý¾Ý¿â¿ìÕÕ£¬´´½¨¿ìÕÕµÄΨһ·½Ê½ÊÇʹÓà Transact-SQL¡£
Êý¾Ý¿â¿ìÕÕÊÇÊý¾Ý¿â£¨³ÆÎª“Ô´Êý¾Ý¿â”£©µÄÖ»¶Á¾²Ì¬ÊÓͼ¡£ÔÚ´´½¨Ê±£¬Ã¿¸öÊý¾Ý¿â¿ìÕÕÔÚÊÂÎñÉ϶¼ÓëÔ´Êý¾Ý¿âÒ»Ö ......
SQlÊý¾ÝÀàÐÍ(-_-ѧSQL¾Í´ÓÊý¾ÝÀàÐÍ¿ªÊ¼°É£¡)
bit ÕûÐÍ
ÆäÖµÖ»ÄÜÊÇ0¡¢1»ò¿ÕÖµ¡£
ÕâÖÖÊý¾ÝÀàÐÍÓÃÓÚ´æ´¢Ö»ÓÐÁ½ÖÖ¿ÉÄÜÖµµÄÊý¾Ý£¬ÈçYes »òNo¡¢True »òFalse ¡¢On »òOff.
int ÕûÐÍ 4¸ö×Ö½Ú
´Ó- 2^31(-2147483648)µ½2^31 (2147483 647)Ö®¼äµÄÕûÊý¡£
´æ´¢µ½Êý¾Ý¿âµÄ¼¸ºõËùÓÐÊýÖµÐ͵ÄÊý¾ ......
1. SQLÐÐתÁдúÂë
-
-
±íÀïÃæÓÐÕâôһ×éÊý¾Ý:
code price
'MCU0-3' 15
'MCU0-3' 20
'MCU0-3' 22
'MCU ......