مقدار یک فیلد از اولین رکورد پیدا شده

[Davood_amega]

سلام به همه مهندسین عزیز
با یک مثال مشکلم را مطرح می کنم .
Table1 : شماره شناسایی(ID) ، نام (Name) ، فامیلی(Family) و شماره مدرک (Doc_Num)
ضمنا فیلد ID نیز Identity می باشد.
بنده می خوام برای Insert اگر نام و فامیلی و شماره مدرکی از قبل در Table1 وجود دارد شماره شناسایی را برگرداند در غیر این صورت دستور Insert زیر اجرا شود و باز شماره شناسایی را برگرداند.

INSERT INTO Table1(Name,Family,Doc_Num) Values (@Name,@Family,@Doc_Num) SET @Main_ID = SCOPE_IDENTITY()تو این تاپیک (http://barnamenevis.org/forum/showthread.php?t=62230) یک چیزای توضیح داده اما نتونستم تغییرش بدم .
تقریبا به همچین کدی نیاز دارم البته میدونم که Syntaxاش اشتباهه!:اشتباه:

declare @Main_ID int
@Main_ID = (select top 1 ID)
from Table1
where Name = @Name
And Family=@Family
And Doc_Num=@Doc_Num)
if @@rowcount = 0
INSERT INTO Table1(Name,Family,Doc_Num)Values (@Name,@Family,@Doc_Num)SET @Main_ID = SCOPE_IDENTITY()

[HamidNch]

داوود جان سلام.ميشه خيلي خلاصه بگي ميخواي چيكار كني و هدفت چيه تا كمكت كنم؟

[Davood_amega]

عرض کنم که بنده در یک قسمت از برنامه ام نیاز دارم که اگر کاربر قبلا شماره مدرکش را وارد کرده کل اطلاعتش را از بانک بگیرد که این کار را با یک Stored Procedure نوشته ام و بعد از آن ، اگه حداقل یکی از اطلاعاتش تغییر کرد مشخص است که این شخص دیگری است و باید یک رکورد جدید اضافه شود ولی اگر اطلاعاتش تغییر نکرد فقط ID مربوط به آن رکورد را برمی گرداند.

[HamidNch]

من اگه باشم يه تابع براي چك كردن وجود ركورد مورد نظر مي نويسم و خروجي اون كه از نوع Boolean هستش مشخص مي كنه كه ركورد تكراريه يا نه و يك تابع هم براي افزودن ركورد مي نويسم كه داراي پارامترهاي مورد نظر هستش و درصورت تكراري بودن به قول شما شماره شناسائي رو برگردونه درغيراينصورت عمل ثبت انجام بشه.
كدنويسيش هم خيلي راحته البته اگه مقصودت چيز ديگه اي نباشه.بگو تا بيشتر توضيح بدم:

public bool IsDuplicateRecord

}

{
(public void InsertRecord(int id, String name, String family, int doc_Num
{
}

[Davood_amega]

نه کاملا درسته بنده هم همچین کاری می خواهم انجام دهم اما نمیدونم که چه جوری در دستور Select مربوطه مقدار فیلد کلید را به یک متغیر اختصاص بدم .
مثلا می خواهم با شرط زیر یه متغیر را پر کنم .

Declare @Main_ID int
select top 1 ID from Table1 Where Name='ALI' And Family='Mohammadi' And Doc_Num='22'ضمنا شرط وجود رکوردم هم بعد از Select می آید و به این صورت است .

if @@rowcount = 0
INSERT INTO Table1(Name,Family,Doc_Num) Values (@Name,@Family,@Doc_Num)SET @Main_ID = SCOPE_IDENTITY()

[HamidNch]

مشكل اينجاست كه من بيشتر كارا رو با كدنويسي توي برنامه نويسي انجام ميدم و تو مي خواي با SQL انجام بدي و من هم توي SQL يه خورده ضعيف تشريف دارم!

[بهزادصادقی]

ببین این کمکت می کنه یا نه:



create table dbo.Table1
(
id int identity(1,1),
name nvarchar(100),
family nvarchar(100),
Doc_Num nvarchar(100)
)
go

if object_id( 'dbo.Davood_Amega_Proc' ) is not null
drop proc dbo.Davood_Amega_proc;
go

create proc dbo.Davood_Amega_Proc
(
@name nvarchar(100),
@family nvarchar(100),
@Doc_Num nvarchar(100),
@Main_ID int output
)
as
begin

declare @id int;

set transaction isolation level serializable;
begin transaction

select
@id = t.id
from
dbo.Table1 t
where
t.name = @name
and t.family = @family
and t.Doc_Num = @Doc_Num;

if @id is null begin

insert
dbo.Table1
(
name,
family,
Doc_Num
)
values
(
@name,
@family,
@Doc_Num
)

set @Main_ID = scope_identity();

commit transaction;

end else begin

set @Main_ID = @id;
rollback transaction;

end;

set transaction isolation level read committed;

return;

end;
go

declare @id int;

select * from dbo.Table1;

exec dbo.Davood_Amega_Proc 'Davood', 'Amega', 'DA-111', @id output;
select * from dbo.Table1;
select @id;

exec dbo.Davood_Amega_Proc 'Davood', 'Amega', 'DA-111', @id output;
select * from dbo.Table1;
select @id;

exec dbo.Davood_Amega_Proc 'Davood', 'Amega', 'DA-222', @id output;
select * from dbo.Table1;
select @id;

exec dbo.Davood_Amega_Proc 'Davood', 'Amega', 'DA-222', @id output;
select * from dbo.Table1;
select @id;

[Davood_amega]

مشکلم برطرف شد کدی که برای مثالی که زده بودم به این صورت می باشد .

create procedure Test(@a1 nvarchar(20),@a2 nvarchar(40),@a3 int)
as
Declare @ID smallint
IF Not Exists(SELECT top 1 * FROM Table1 where Name=@a1 And Family=@a2 And Doc_Num=@a3)
BEGIN
INSERT INTO Table1(Name,Family,Doc_Num) Values (@a1,@a2,@a3)SET @ID = SCOPE_IDENTITY()
End
ELSE
SELECT top 1 @ID=ID From Table1 where Name=@a1 And Family=@a2 And Doc_Num=@a3
حالا اگه دستور زیر را بزنیم :

Test 'Ali','Mohammadi',12
اگه این رکورد در Table1 وجود داشته باشه IDاش را برمی گرداند و اگر این رکورد وجود نداشته باشه این رکورد را اضافه می کنه و ID را برمی گردانه !

[Davood_amega]

بهزاد صادقی عزیز خیلی عالی بود از کد شما استفاده کردم کد خیلی بهینه تری است ممنون .

[بهزادصادقی]

مشکلم برطرف شد کدی که برای مثالی که زده بودم به این صورت می باشد .

create procedure Test(@a1 nvarchar(20),@a2 nvarchar(40),@a3 int)
as
Declare @ID smallint
IF Not Exists(SELECT top 1 * FROM Table1 where Name=@a1 And Family=@a2 And Doc_Num=@a3)
BEGIN
INSERT INTO Table1(Name,Family,Doc_Num) Values (@a1,@a2,@a3)SET @ID = SCOPE_IDENTITY()
End
ELSE
SELECT top 1 @ID=ID From Table1 where Name=@a1 And Family=@a2 And Doc_Num=@a3
حالا اگه دستور زیر را بزنیم :

Test 'Ali','Mohammadi',12
اگه این رکورد در Table1 وجود داشته باشه IDاش را برمی گرداند و اگر این رکورد وجود نداشته باشه این رکورد را اضافه می کنه و ID را برمی گردانه !

فکر کنم جالب باشد در مورد یک مسئله خاصی که کد بالا دارد صحبت کنیم.

اگر تعداد تراکنش های سیستم بالا باشد، امکان دارد دو کاربر سعی کنند به طور هم زمان یک سطر خاص را به جدول اضافه نمایند. اگر این اتفاق بیافتد، این امکان وجود دارد که کد کاربر اول به دنبال اجرای دستور exists شروع به اجرای عمل insert نماید. ولی درست در بین اجرای این دو دستور، کد کاربر شماره دو نیز دستور exists را اجرا می کند. چون کار بر اول هنوز دستور insert را کامل نکرده، نتیجه اجرای دستور exists به کاربر دوم خواهد گفت که سطر مورد نظر در جدول وجود ندارد و در نتیجه کد این کاربر هم شروع به اجرای دستور insert می نماید. نتیجه نهایی این است که یا این سطر دو بار به جدول اضافه می گردد (اگر جدول جلوی تکرار سطرها را نمی گیرد) و یا اینکه یکی از دو کاربر به خطا برخورد خواهند کرد.

البته این اتفاق فقط در سیستم های پر تراکنش می تواند بیافتد. ولی معمولا بهتر است که حتی کدهای ساده طوری در یک سیستم نوشته شوند که جلوی scale کردن یک سیستم را در آینده نگیرند. یکی از مسائلی که ما با SQL Server در ایران داریم این است که در یک مقیاس محدود، برنامه های ما خوب کار می کنند، ولی وقتی تعداد کاربران، حجم داده ها و یا تعداد تراکنش ها بالا می روند، سیستم های ما توش می مانند. یکی از راه های رفع این مشکل تمرین استفاده از روش هایی می باشد که توانایی رشد را به query های ما می دهد.

کدی که در پست قبلی برای شما درج کرده بودم از یکی از این روش ها برای جلوگیری از برخورد با مسئله بالا استفاده می کند.

[بهزادصادقی]

بهزاد صادقی عزیز خیلی عالی بود از کد شما استفاده کردم کد خیلی بهینه تری است ممنون .

مرسی. نظر لطف شماست. من یک تغییر کوچک در کد دادم. گرچه منطق کد را عوض نمی کند، از لحاظ مدیریت transaction ها رفتارش استاندارد تر شد.

[Davood_amega]

بله کاملا درست است البته یه مثال برای راحتی مشکلم تعریف کردم وگرنه از Transaction ها استفاده می کنم و برای فهمیدن اینکه در دستورات Insert خطای وجود نداشته باشد از کد زیر استفاده می کنم .


Begin Transaction
Insert Into Table1()Values()
if @@ERROR<>0
BEGIN
ROLLBACK
RAISERROR('Error Adding Person_Info Record',16,1)
RETURN
END
Else
Commit Transaction
البته چند خط از کدی که در مورد Transactionنوشته بودید را متوجه نشدم :

set transaction isolation level serializable;
set transaction isolation level read committed;

[بهزادصادقی]

البته چند خط از کدی که در مورد Transactionنوشته بودید را متوجه نشدم :

set transaction isolation level serializable;
set transaction isolation level read committed;

برای جلوگیری از مشکلی که در بالا به آن اشاره کردم، می توان قبل از آغاز transaction خویش، خط اول بالا و بعد از پایان آن خط دوم بالا را اجرا کرد.

وقتی شما transaction isolation level یک transaction خاص را در سطح serializable لحاظ می کنید، در حقیقت دارید به SQL Server این دستور را می دهید:

یک transaction برای من شروع کن. در این transaction امکان دارد من گاهی مواقع داده هایی را بخوانم (با استفاده از دستور select) و یا گاهی مواقع داده هایی را بنویسم (با استفاده از دستورات update، insert، و delete). حال، هر وقت من با زدن یک select از تو تقاضای یک سری داده هایی را کردم، از تو می خواهم که کاری کنی که تا زمانی که transaction من به پایان نرسیده، هیچ کس نتواند هیچ تغییری را در database ایجاد کند که به هر نحوی باعث شود که اگر من دوباره آن دستور select را اجرا کنم، نتایجی که به دست می آورم با آن بار اولی که دستور را اجرا کردم هیچ فرقی داشته باشد.

برای اینکه کد شما درست کار کند، شما لازم دارید بدانید که بعد از اینکه شما چک کردید که آیا همچین سطری در جدول شما وجود دارد یا نه، کسی نیاید و قبل از اینکه شما سطر خود را به جدول اضافه نموده اید، همان سطر را اضافه کند. استفاده از transaction isolation level در سطح serializable و بعد چک کردن وجود داشتن یا نداشتن سطر مذکور در قلمروی آن transaction باعث می شود که تا هنگامی که transaction شما تمام نشده، SQL Server نگذارد کسی کاری کند که انجام آن منجر به عوض شدن نتیجه چک کردن شما می شده. select شما می گوید که هیچ رکوردی که name, family و Doc_Num آن برابر با پارامتر های sp شما باشد در جدول Table1 وجود ندارد. تا زمانی که transaction شما در حال اجرا است، SQL Server می آید و جلو هر عملی را که باعث شود این select نتیجه اش فرق کند را می گیرد. به عبارت دیگر، نخواهد گذاشت کسی همان سطر را به جدول اضافه کند.

استفاده از این توانایی خیلی می تواند خطرناک باشد. اگر بدون دانش عمیق، تجربه و بدون رویه ما شروع کنیم و از transaction isolation level سطح serializable در هر جای کد خویش استفاده کنیم، احتمال بروز مشکلات بزرگ عملکردی و برخورد با مشکل deadlock ها در سیستم خویش را بسیار بالا می بریم. ولی طوری که این sp حاضر نوشته شده، استفاده از این توانایی درست انجام شده.

جمله
set transaction isolation level read committed
سطح transaction isolation level را به وضعیت default آن در SQL Server باز می گرداند. راستش را بخواهید، این یک کار اصولی و درست نیست. چرا که امکان دارد قبل از اجرای sp حاضر، یک کدی خاص قبلا سطح transaction isolation level را به چیزی به غیر از default تغییر داده باشد. کار درست این است که من می بایست قبل از شروع transaction خویش اول چک می کردم که سطح transaction isolation level در حال حاضر چیست، آن را یک جا ذخیره می کردم، و بعد از تمام شدن transaction خود آن را به همان سطی قبلی باز می گرداندم. ولی حقیقتش من اینجا تنبلی کردم و فرضم را بر این می گذارم که احتمالا کد شما از مفهوم transaction isolation level استفاده نمی نماید و بنابرین کافی است که من بعد از اتمام transaction خویش، آن را به سطح default برگردانم.