¾È³çÇϼ¼¿ä.
SQL Server µ¥ÀÌÅͺ£À̽º ÇÁ·ÎÁ§Æ®ÀÇ
dbo/Stored Procedures Æú´õ¿¡
¸¶¿ì½º ¿À¸¥ÂÊ ¹öÆ° Ŭ¸¯ ÈÄ [Ãß°¡] - ÀúÀå ÇÁ·Î½ÃÀú¸¦
¼±Åà ÈÄ
WriteUsers ÀúÀå ÇÁ·Î½ÃÀú ±¸¹®¸¸ µû·Î »ý¼ºÇØ º¸½Ã±â ¹Ù¶ø´Ï´Ù.
°¡²û
µ¿ÀÏÇÑ SQL ÆÄÀÏÀÌÁö¸¸,
ÀúÀå ÇÁ·Î½ÃÀú°¡ ¾Æ´Ñ ¼ø¼ö SQL ÆÄÀÏ·Î »ý¼ºµÇ´Â °æ¿ì¿¡´Â
(¶Ç´Â Á¦°¡ ¹Ì¸® ¸¸µé¾î ³õÀº ¼Ò½º ÆÄÀÏ ÀÚü¸¦ º¹»çÇؼ ºÙ¿©³Ö±â ÇÑ °æ¿ì µî...)
ÇØ´ç ÇÁ·Î½ÃÀú ¶Ç´Â Å×À̺íÀº
½ÇÁ¦ SQL Server¿¡ °Ô½ÃÇÒ ¶§ ¹Ý¿µµÇÁö ¾Ê½À´Ï´Ù.
Áï,
WriteUsers ÀúÀå ÇÁ·Î½ÃÀú ±¸¹®¸¸
±âÁ¸ °ÍÀ» Á¦°ÅÇÑ ÈÄ
»õ·Ó°Ô ÀúÀå ÇÁ·Î½ÃÀú ŸÀÔÀ¸·Î ¸¸µç ÈÄ °Ô½ÃÆÇ º¸½Ã¸é µÉ °Í °°½À´Ï´Ù.
ÀÌ»óÀÔ´Ï´Ù.
¹ÚÁÖ¿µ ´ÔÀÌ ¾²½Å ¿ø¹® ±ÛÀÔ´Ï´Ù.
asp.net °Á¸¦ º¸¸ç °øºÎÇÏ°í µû¶óÇÏ°íÀÖ½À´Ï´Ù.
°ÁÂÁß mssqlµ¥ÀÌÅÍ º£À̽ºÇÁ·ÎÁ§Æ®¸¦ publishÀ» ÇÑÈÄ
sql¼¹ö¿¡ È®ÀÎÀ»Çϸé Å×À̺íÀº Àß µé¾î°¡Àִµ¥,
6°³ÀÇÁß ÇÁ·Î½ÃÀú¿¡¼ WriteUsers ÇÁ·Î½ÃÀú°¡ °è¼Ó ´©¶ôÀ̵˴ϴÙ.
-- ÀÔ·Â ÀúÀå
Create Proc dbo.WriteUsers
@UserID VarChar(25),
@Password VarChar(20)
as
Insert Into Users Values(@UserID, @Password)
go
-- WriteUser 'ID' , 'PASS'
-- Ãâ·Â ÀúÀå ÇÁ·Î½ÃÀú
Create Proc dbo.ListUsers
as
Select * From Users Order By UID Desc
go
-- ListUser
-- »ó¼¼ ÀúÀå ÇÁ·Î½ÃÀú
Create Proc dbo.ViewUsers
@UID Int
as
Select * From Users Where UID=@UID
go
-- ViewUsers 5
-- ¼öÁ¤ ¶Ç´Â Ãß°¡ ÀúÀå ÇÁ·Î½ÃÀú
Create Proc dbo.ModifyUsers
@UserID VarChar(25),
@Password VarChar(20),
@UID Int
as
Begin Tran
Update Users
Set
UserID=@UserID,
[Password]=@Password
Where UID=@UID
Commit Tran
go
-- ModifyUsers 'master ¶Ç´Â sa' ,' pass', 2
-- »èÁ¦ ÀúÀå ÇÁ·Î½ÃÀú
Create Proc dbo.DeleteUsers
@UID Int
as
Delete Users Where UID=@UID
go
-- DelteUsers 2
-- °Ë»ö ÀúÀå ÇÁ·Î½ÃÀú
Create Proc dbo.SearchUsers
@SearchField VarChar(25),
@SearchQuery VarChar(25)
as
Declare @strSql VarChar(255)
set @strSql='
Select * From Users
Where
' + @SearchField + ' Like ''%' + @SearchQuery + '%''
'
-- ÇÁ¸°Æ® @strSql
Exec(@strSql)
go
--SearchUsers 'UserID','admin'
Ȥ½Ã À߸øµÈ ³»¿ëÀÌ ÀÖ´ÂÁö ±Ã±ÝÇÕ´Ï´Ù.
|