ããä¸ãåå¨è¿ç¨çæ¦å¿µ
ããT-SQlåCè¯è¨ä¸æ · ï¼æ¯ä¸é¨ç»æåçè¯è¨ã
ããä»ä¹æ¯åå¨è¿ç¨ï¼
ããåå¨è¿ç¨æ¯SQLæ¥è¯¢è¯å¥ä¸æ§å¶æµç¨è¯å¥çé¢ç¼è¯éåï¼å¹¶ä»¥ç¹å®çå称ä¿åå¨æ°æ®åºä¸ãåå¨è¿ç¨ä¹æ¯æ°æ®åºå¯¹è±¡
ããåç±»ï¼
ããç³»ç»åå¨è¿ç¨ï¼ 以sp_æxp_æ头
ããç¨æ·èªå®ä¹ ï¼ä»¥proc_æ头
ããåå¨è¿ç¨çä¼ç¹ï¼
ããæ§è¡é度快 æçé«
ãã模åå¼ç¼ç¨
ããåå°ç½ç»æµé
ããæé«å®å
¨æ§
ããäºãç³»ç»åå¨è¿ç¨
ããSQl server çç³»ç»åå¨è¿ç¨ä¿åå¨masteræ°æ®åºä¸ï¼ä¸ææå½åçç³»ç»åå¨è¿ç¨å½å以âSp_âå¼å¤´ãå¨masteræ°æ®åºä¸ï¼
ããç³»ç»åå¨è¿ç¨æ°éå¦ä¸ï¼
ãã代ç å¦ä¸ å¤å¶ä»£ç
ããselect count([name])as 'ç³»ç»åå¨æ°é' from sysobjects
ããwhere [name] like 'sp_%'
ããEXECUTE ç¨æ¥è¡¨ç¤ºè°ç¨åå¨è¿ç¨ï¼ä¹å¯ä»¥ç¼©å为EXECï¼
ããè°ç¨åå¨çè¯æ³å¦ä¸ï¼
ããEXECUTE âåå¨è¿ç¨åâ âåæ°â ---å¦æ没æåæ°åçç¥åæ°
ãã常ç¨çç³»ç»åå¨è¿ç¨
ããEXEC sp_databases ååºå½åç³»ç»ä¸çæ°æ®åº
ããEXEC sp_renamedb 'Northwind','Northwind1' ä¿®æ¹æ°æ®åºçå称(åç¨æ·è®¿é®)
ããUSE stuDB GO EXEC sp_tables è¿åæ个表åçä¿¡æ¯
ããEXEC sp_columns æ¥çæå®åçä¿¡æ¯
ããEXEC sp_help æ¥çæ个表çææä¿¡æ¯
ããEXEC sp_helpconstraint '表å' æ¥çæ个表ç约æ
ããEXEC sp_helpdb 'æ°æ®åºå' æ EXEC sp_helpdb æ¥çæå®æ°æ®åºææææ°æ®åºä¿¡æ¯
ããEXEC sp_helptext '对象å称' æ¾ç¤ºæ°æ®åºå¯¹è±¡ï¼åå¨è¿ç¨ã触åå¨ãè¯å¾ï¼çå®ä¹ææ¬
ããEXEC sp_helpindex '表å' æ¥çæå®è¡¨çç´¢å¼ä¿¡æ¯
ããEXEC sp_renamedb 'åå称','æ°å称' æ´æ¹æ°æ®åºå称
ããEXEC sp_stored_procedures ååºå½åç¯å¢å¯ç¨çææåå¨è¿ç¨
ããé¤äºç³»ç»åå¨è¿ç¨ï¼SQL Server è¿æä¾ä»¥Xp_å¼å¤´çæ©å±åå¨è¿ç¨ï¼å¦å¯ä»¥è°ç¨DOSå½åçï¼XP_cmdshell åå¨è¿ç¨
ããç¨æ³å¦ä¸ï¼
ãã
ãã代ç å¦ä¸ å¤å¶ä»£ç
ããEXEC Xp_cmdshell DOS å½å [NO_OUTPUT]
ãã
ããNO_OUTPUT 为å¯éåæ°ï¼è¡¨ç¤ºæ¯å¦è¾å
¥åå¨è¿ç¨è¿åçä¿¡æ¯
ããä¸ãç¨æ·èªå®ä¹åå¨è¿ç¨
ãã1ãè¯æ³
ãã代ç å¦ä¸ å¤å¶ä»£ç
ããcreate procedure åå¨è¿ç¨å
ãã@åæ°1å æ°æ®ç±»å [=é»è®¤å¼] [åæ°ç±»å(è¾å
¥/è¾åº)]
ãã... ...
ãã@åæ°nå æ°æ®ç±»å [=é»è®¤å¼] [åæ°ç±»å(è¾å
¥/è¾åº)]
ããas
ããbegin
ããsqlè¯å¥
ããend;
ããgo
ãã
ããåæ°ç±»åå为è¾å
¥åæ°åè¾åºåæ°ï¼é»è®¤ä¸ºè¾å
¥åæ°ï¼ä½¿ç¨OUTPUT表示è¾åºåæ°ãå建åå¨è¿ç¨æ好以procå¼å¤´
ãã
ãã2ãå建ä¸å¸¦åæ°çåå¨è¿ç¨
ãã
ãã代ç å¦ä¸ å¤å¶ä»£ç
ãã--å¤æåå¨è¿ç¨æ¯å¦åå¨
ããif object_id('proc_student','procedure') is not null
ããdrop procedure proc_student
ããgo
ããcreate procedure proc_student
ããas
ããbegin
ããselect pcid as 'çµèç¼å·',
ããcase pcuse
ããwhen 0 then '空é²'
ããwhen 1 then 'å¿ç¢'
ããend as '使ç¨ç¶æ' from pc
ããend;
ãã--è°ç¨åå¨è¿ç¨
ããexecute proc_student select * from pc
ããgo
ãã
ãã3ãå建带è¾å
¥åæ°çåå¨è¿ç¨
ããè¯æ³ï¼
ãã代ç å¦ä¸ å¤å¶ä»£ç
ããcreate procedure åå¨è¿ç¨å
ãã@åæ°1å æ°æ®ç±»å [=é»è®¤å¼]
ãã....
ãã@åæ°2å æ°æ®ç±»å[=é»è®¤å¼]
ããas
ããSQlä¸è¯å¥
ãã...
ããgo
ãã
ãã--ä¾å¦
ãã--å建带è¾å
¥åæ°çåå¨è¿ç¨
ãã代ç å¦ä¸ å¤å¶ä»£ç
ããif object_id('proc_stu','procedure') is not null
ããdrop procedure proc_stu
ããgo
ããcreate procedure proc_stu
ãã@pcuse int
ããas
ããbegin
ããselect pcid as 'çµèç¼å·',
ããcase pcuse
ããwhen 0 then '空é²'
ããwhen 1 then 'å¿ç¢'
ããend as '使ç¨ç¶æ' from pc where pcuse=@pcuse end;
ãã--è°ç¨åå¨è¿ç¨
ããexecute proc_stu @pcuse=1
ãã
ãã4ãå建带è¾åºåæ°çåå¨è¿ç¨
ãã代ç å¦ä¸ å¤å¶ä»£ç
ãã--å建带è¾åºåæ°çåå¨è¿ç¨
ããif OBJECT_ID('proc_s','procedure') is not null
ããdrop procedure proc_s
ããgo
ããcreate procedure proc_s
ãã@pcid int,
ãã@pcus int output
ããas
ããbegin
ããselect @pcus=pcuse from pc where pcid=@pcid end;
ãã--è°ç¨åå¨è¿ç¨
ããdeclare @pcus int execute proc_s 5,@pcus output
ãã
ããåãå¤çé误信æ¯
ããå½åå¨è¿ç¨çè¯å¥ååå¤ææ¶ï¼å¯ä»¥å¨åå¨è¿ç¨ä¸å å
¥é误è¯è¨ãSQL Serverä¸å¯ä»¥ä½¿ç¨RAISERROR è¿åç¨æ·èªå®ä¹çé误信æ¯ã
ããRAISERROR è¯æ³å¦ä¸ï¼
ãã
ããRAISERROR (èªå®ä¹çé误信æ¯ï¼é误ç严é级å«ï¼é误ç¶æ)
ãã
ããèªå®ä¹é误信æ¯ï¼è¡¨ç¤ºè¾åºä¿¡æ¯ï¼è¡¨ç¤ºè¾åºçé误æ示ææ¬
ããé误ç严é级å«ï¼è¡¨ç¤ºç¨æ·èªå®ä¹é误ç严éæ§çº§å«ãï¼0-18æï¼
ããé误çç¶æï¼è¡¨ç¤ºèªå®ä¹é误çç¶æï¼å¼çèå´å¨1-127
温馨提示:答案为网友推荐,仅供参考