Разработка автоматизированной системы оценки кредитоспособности физических лиц (АС "CreditAnalysis")

Проектирование автоматизированной информационной системы по оценке кредитоспособности клиента Банка для принятия решения по выдаче кредита. Разработка интерфейса и алгоритма работы программы. Составление сметы затрат на создание программного изделия.

Рубрика Программирование, компьютеры и кибернетика
Вид дипломная работа
Язык русский
Дата добавления 26.07.2014
Размер файла 2,3 M

Отправить свою хорошую работу в базу знаний просто. Используйте форму, расположенную ниже

Студенты, аспиранты, молодые ученые, использующие базу знаний в своей учебе и работе, будут вам очень благодарны.

aCommand.Parameters[48].Value = RadioButtonList4.SelectedValue;

aCommand.ExecuteNonQuery();

System.Diagnostics.Process.Start("C:/ConsoleApplication1/ConsoleApplication1.exe");

Response.Redirect("request.htm");

}

conn.Close();

}

protected void CheckBox1_CheckedChanged(object sender, EventArgs e)

{

}

}

Вызов функций пересчёта параметров, принятия решение, формирования и отправки письма.

Исполняемый код приложения описан в файле ConsoleApplication1.cs

using System;

using System.Collections.Generic;

using System.Text;

using System.Data.SqlClient;

using System.Web;

using System.Net;

using System.Net.Mail;

using System.Net.Mime;

namespace ConsoleApplication1

{

class Program

{

static void Main(string[] args)

{

string Email, MessageText;

string source = "Data Source=Mario-PK;Initial Catalog=BANK;Integrated Security=True";

SqlConnection conn = new SqlConnection(source);

conn.Open();

string query = @"exec Update_ayear";

SqlCommand command = new SqlCommand(query, conn);

SqlDataReader reader = command.ExecuteReader();

reader.Close();

string query2 = @"exec Update_fvcountry";

SqlCommand command2 = new SqlCommand(query2, conn);

SqlDataReader reader2 = command2.ExecuteReader();

reader2.Close();

string query3 = @"exec Update_gender";

SqlCommand command3 = new SqlCommand(query3, conn);

SqlDataReader reader3 = command3.ExecuteReader();

reader3.Close();

string query4 = @"exec Update_region";

SqlCommand command4 = new SqlCommand(query4, conn);

SqlDataReader reader4 = command4.ExecuteReader();

reader4.Close();

string query6 = @"exec blacklist";

SqlCommand command6 = new SqlCommand(query6, conn);

SqlDataReader reader6 = command6.ExecuteReader();

reader6.Close();

string query5 = @"exec update_main";

SqlCommand command5 = new SqlCommand(query5, conn);

SqlDataReader reader5 = command5.ExecuteReader();

reader5.Close();

string query7 = @"exec update_text";

SqlCommand command7 = new SqlCommand(query7, conn);

SqlDataReader reader7 = command7.ExecuteReader();

reader7.Close();

string query8 = @"SELECT Email,MessageText from Request1 where Message = 'NO'";

SqlCommand command8 = new SqlCommand(query8, conn);

SqlDataReader reader8 = command8.ExecuteReader();

while (reader8.Read())

{

Email = (string)reader8["Email"];

MessageText = (string)reader8["MessageText"];

//Адрес SMTP-сервера

String smtpHost = "mx.bmstu.ru";

//Порт SMTP-сервера

int smtpPort = 25;

//Логин

String smtpUserName = "universitybank@mail.ru";

//Пароль

String smtpUserPass = "university2011";

//Создание подключения

SmtpClient client = new SmtpClient(smtpHost, smtpPort);

client.Credentials = new NetworkCredential(smtpUserName, smtpUserPass);

//Адрес для поля "От"

String msgFrom = "UniversityBank@mail.ru";

//Адрес для поля "Кому" (адрес получателя)

String msgTo = Email;

Console.WriteLine(msgTo);

//Тема письма

String msgSubject = "Заявка на кредит в UniversityBank";

//Текст письма

String msgBody = MessageText;

MailMessage message = new MailMessage(msgFrom, msgTo, msgSubject, msgBody);

try

{

//Отсылаем сообщение

client.Send(message);

}

catch (SmtpException ex)

{

//В случае ошибки при отсылке сообщения можем увидеть, в чем проблема

Console.WriteLine(ex.InnerException.Message.ToString());

}?

}

reader8.Close();

string query9 = @"Update Request1 set Message = 'YES' where Message = 'NO'";

SqlCommand command9 = new SqlCommand(query9, conn);

SqlDataReader reader9 = command9.ExecuteReader();

reader9.Close();

conn.Close();

}

}

}

Процедуры пересчёта параметров, принятия решение, хранимые в базе данных

Процедура сравнения данных пользователя с чёрным списком

Исполняемый код процедуры описан в файле dbo.blacklist

CREATE PROCEDURE dbo.blacklist As

Begin

declare @cursor CURSOR

declare c CURSOR FOR

SELECT ID FROM Request1 where Offer = null

declare @ID int

declare @Secname VarChar(20)

declare @Firstname VarChar(20)

declare @Thirdname VarChar(20)

declare @Bdate VarChar(20)

declare @black VarChar(20)

set @cursor = c

OPEN @cursor

fetch next from @cursor into @ID

WHILE (@@FETCH_STATUS=0) BEGIN

Select @Secname = Cast((select Secname from Request1 Where ID = @ID) As Char)

Select @Firstname = Cast((select Firstname from Request1 Where ID = @ID) As Char)

Select @Thirdname = Cast((select Thirdname from Request1 Where ID = @ID) As Char)

Select @Bdate = Cast((select Bdate from Request1 Where ID = @ID) As Char)

Select @black = Cast((Select count(*) from His_blacklist where SecondName = @Secname and FirstName = @Firstname and ThirdName = @Thirdname and BirthDate = @Bdate) As Char)

If @black = 1

Update Request1 Set Offer = 'NO' Where ID = @ID

fetch next from @cursor into @ID

END

CLOSE @CURSOR

DEALLOCATE @CURSOR

DEALLOCATE c

End

1.3.1.1 Процедура пересчёта весовых коэффициентов, связанных с годом рождения

Исполняемый код процедуры описан в файле dbo.update_ayear

CREATE PROCEDURE dbo.update_ayear

AS

begin

declare @cursor CURSOR

declare c CURSOR FOR

SELECT ID FROM Ayear

declare @ID int

declare @Weight VarChar(20)

declare @OldWeight VarChar(20)

declare @MidWeight VarChar(20)

set @cursor = c

set @Weight = null

set @OldWeight = null

OPEN @cursor

fetch next from @cursor into @ID

WHILE (@@FETCH_STATUS=0) BEGIN

Select @OldWeight = Cast((select weight from Ayear Where ID = @ID) As Char)

Select @Weight = Cast ( (select sum(Category) from His_customer where Year(BirthDate) = (Select AyearNM FROM Ayear Where ID = @ID)) / (select count(*) from His_customer where Year(BirthDate) = (Select AyearNM FROM Ayear Where ID = @ID)) As Char)

if ((@Weight != null) and (@OldWeight != null))

Set @MidWeight = Cast(ROUND(((ABS(@Weight) + ABS(@OldWeight))/2),0) As Char)

IF @OldWeight = NULL

Update Ayear Set Weight = @Weight Where ID = @ID

IF @Weight = NULL

Update Ayear Set Weight = @OldWeight Where ID = @ID

fetch next from @cursor into @ID

END

CLOSE @CURSOR

DEALLOCATE @CURSOR

DEALLOCATE c

End

Процедура пересчёта весовых коэффициентов, связанных с гражданством

Исполняемый код процедуры описан в файле dbo.update_fvcountry

CREATE PROCEDURE dbo.update_fvcountry

AS

begin

declare @cursor CURSOR

declare c CURSOR FOR

SELECT ID FROM Fvcountry

declare @ID int

declare @Weight VarChar(20)

declare @OldWeight VarChar(20)

declare @MidWeight VarChar(20)

set @cursor = c

set @Weight = null

set @OldWeight = null

OPEN @cursor

fetch next from @cursor into @ID

WHILE (@@FETCH_STATUS=0) BEGIN

Select @OldWeight = Cast((select weight from Fvcountry Where ID = @ID) As Char)

Select @Weight = Cast ( (select sum(Category) from His_customer where Fvcountry = (Select FvcountryNM FROM Fvcountry Where ID = @ID)) / (select count(*) from His_customer where Fvcountry = (Select FvcountryNM FROM Fvcountry Where ID = @ID)) As Char)

if ((@Weight != null) and (@OldWeight != null))

Set @MidWeight = Cast(ROUND(((ABS(@Weight) + ABS(@OldWeight))/2),0) As Char)

IF @OldWeight = NULL

Update Fvcountry Set Weight = @Weight Where ID = @ID

IF @Weight = NULL

Update Fvcountry Set Weight = @OldWeight Where ID = @ID

fetch next from @cursor into @ID

END

CLOSE @CURSOR

DEALLOCATE @CURSOR

DEALLOCATE c

End

Процедура пересчёта весовых коэффициентов, связанных с полом

Исполняемый код процедуры описан в файле dbo.update_gender

CREATE PROCEDURE dbo.update_gender

AS

begin

declare @cursor CURSOR

declare c CURSOR FOR

SELECT ID FROM Gender

declare @ID int

declare @Weight VarChar(20)

declare @OldWeight VarChar(20)

declare @MidWeight VarChar(20)

set @cursor = c

set @Weight = null

set @OldWeight = null

OPEN @cursor

fetch next from @cursor into @ID

WHILE (@@FETCH_STATUS=0) BEGIN

Select @OldWeight = Cast((select weight from Gender Where ID = @ID) As Char)

Select @Weight = Cast ( (select sum(Category) from His_customer where Gender = (Select GenderNM FROM Gender Where ID = @ID)) / (select count(*) from His_customer where Gender = (Select GenderNM FROM Gender Where ID = @ID)) As Char)

if ((@Weight != null) and (@OldWeight != null))

Set @MidWeight = Cast(ROUND(((ABS(@Weight) + ABS(@OldWeight))/2),0) As Char)

IF @OldWeight = NULL

Update Gender Set Weight = @Weight Where ID = @ID

IF @Weight = NULL

Update Gender Set Weight = @OldWeight Where ID = @ID

fetch next from @cursor into @ID

END

CLOSE @CURSOR

DEALLOCATE @CURSOR

DEALLOCATE c

End

Процедура пересчёта весовых коэффициентов, связанных с регионом

Исполняемый код процедуры описан в файле dbo.update_region

CREATE PROCEDURE dbo.update_region

AS

begin

declare @cursor CURSOR

declare c CURSOR FOR

SELECT ID FROM Region

declare @ID int

declare @Weight VarChar(20)

declare @OldWeight VarChar(20)

declare @MidWeight VarChar(20)

set @cursor = c

set @Weight = null

set @OldWeight = null

OPEN @cursor

fetch next from @cursor into @ID

WHILE (@@FETCH_STATUS=0) BEGIN

Select @OldWeight = Cast((select weight from Region Where ID = @ID) As Char)

Select @Weight = Cast ( (select sum(Category) from His_customer where Fregion = (Select RegionNM FROM Region Where ID = @ID)) / (select count(*) from His_customer where Fregion = (Select RegionNM FROM Region Where ID = @ID)) As Char)

if ((@Weight != null) and (@OldWeight != null))

Set @MidWeight = Cast(ROUND(((ABS(@Weight) + ABS(@OldWeight))/2),0) As Char)

IF @OldWeight = NULL

Update Region Set Weight = @Weight Where ID = @ID

IF @Weight = NULL

Update Region Set Weight = @OldWeight Where ID = @ID

fetch next from @cursor into @ID

END

CLOSE @CURSOR

DEALLOCATE @CURSOR

DEALLOCATE c

End

Процедура принятия решения о выдачи кредита

Исполняемый код процедуры описан в файле dbo.update_main

CREATE PROCEDURE dbo.update_main As

Begin

declare @cursor CURSOR

declare c CURSOR FOR

SELECT ID FROM Request1 where Offer = null

declare @ID int

declare @MidWeight VarChar(20)

declare @Ftime VarChar(20)

declare @WeightFtime VarChar(20)

declare @Ident VarChar(20)

declare @WeightIdent VarChar(20)

declare @Wtermpres VarChar(20)

declare @WeightWtermpres VarChar(20)

declare @Wtermlast VarChar(20)

declare @WeightWtermlast VarChar(20)

declare @Wpaym VarChar(20)

declare @WeightWpaym VarChar(20)

declare @Crsum VarChar(20)

declare @WeightCrsum VarChar(20)

declare @Region VarChar(20)

declare @Workform VarChar(20)

declare @Fvcountry VarChar(20)

declare @Ayear VarChar(20)

declare @Gender VarChar(20)

declare @Ftype VarChar(20)

declare @Fampos VarChar(20)

declare @Education VarChar(20)

declare @Children VarChar(20)

declare @Comptype VarChar(20)

declare @Compamount VarChar(20)

declare @Automob VarChar(20)

declare @Travel VarChar(20)

declare @Commitment VarChar(20)

declare @Debtloan VarChar(20)

set @cursor = c

OPEN @cursor

fetch next from @cursor into @ID

WHILE (@@FETCH_STATUS=0) BEGIN

Select @Ftime = Cast((select Ftime from Request1 Where ID = @ID) As Char)

IF (@Ftime <= 12)

Set @WeightFtime = 6

IF (@Ftime > 12) AND (@Ftime < 60)

Set @WeightFtime = 7

IF (@Ftime >= 60)

Set @WeightFtime = 9

Select @Ident = Cast((select Add_ident from Request1 Where ID = @ID) As Char)

IF (@Ident = 1)

Set @WeightIdent = 8

else

Set @WeightIdent = 7

Select @Wtermpres = Cast((select Wtermpres from Request1 Where ID = @ID) As Char)

IF (@Wtermpres <= 12)

Set @WeightWtermpres = 6

IF (@Wtermpres > 12) AND (@Wtermpres < 60)

Set @WeightWtermpres = 8

IF (@Wtermpres >= 60)

Set @WeightWtermpres = 9

Select @Wtermlast = Cast((select Wtermlast from Request1 Where ID = @ID) As Char)

IF (@Wtermlast <= 12)

Set @WeightWtermlast = 7

IF (@Wtermlast > 12) AND (@Wtermlast < 60)

Set @WeightWtermlast = 8

IF (@Wtermlast >= 60)

Set @WeightWtermlast = 9

Select @Wpaym = Cast((select Wpaym from Request1 Where ID = @ID) As Char)

IF (@Wpaym <= 30000)

Set @WeightWpaym = 4

IF (@Wpaym > 30000) AND (@Wpaym < 100000)

Set @WeightWpaym = 7

IF (@Wpaym >= 100000)

Set @WeightWpaym = 9

Select @Crsum = Cast((select Crsum from Request1 Where ID = @ID) As Char)

IF (@Crsum <= 50000)

Set @WeightCrsum = 9

IF (@Crsum > 50000) AND (@Crsum < 500000)

Set @WeightCrsum = 7

IF (@Crsum >= 500000)

Set @WeightCrsum = 5

Select @Region = Cast((select Weight from Region Where ID IN (SELECT RegionID from Request1 Where ID = @ID)) As Char)

Select @Workform = Cast((select Weight from Workform Where ID IN (SELECT WorkformID from Request1 Where ID = @ID)) As Char)

Select @Fvcountry = Cast((select Weight from Fvcountry Where ID IN (SELECT FvcountryID from Request1 Where ID = @ID)) As Char)

Select @Ayear = Cast((select Weight from Ayear Where AyearNM IN (SELECT Year(Bdate) from Request1 Where ID = @ID)) As Char)

Select @Gender = Cast((select Weight from Gender Where ID IN (SELECT GenderID from Request1 Where ID = @ID)) As Char)

Select @Ftype = Cast((select Weight from Ftype Where ID IN (SELECT FtypeID from Request1 Where ID = @ID)) As Char)

Select @Fampos = Cast((select Weight from Fampos Where ID IN (SELECT FamposID from Request1 Where ID = @ID)) As Char)

Select @Education = Cast((select Weight from Education Where ID IN (SELECT EducationID from Request1 Where ID = @ID)) As Char)

Select @Children = Cast((select Weight from Children Where ID IN (SELECT ChildrenID from Request1 Where ID = @ID)) As Char)

Select @Comptype = Cast((select Weight from Comptype Where ID IN (SELECT ComptypeID from Request1 Where ID = @ID)) As Char)

Select @Compamount = Cast((select Weight from Compamount Where ID IN (SELECT CompamountID from Request1 Where ID = @ID)) As Char)

Select @Automob = Cast((select Weight from Automob Where ID IN (SELECT AutomobID from Request1 Where ID = @ID)) As Char)

Select @Travel = Cast((select Weight from Travel Where ID IN (SELECT TravelID from Request1 Where ID = @ID)) As Char)

Select @Commitment = Cast((select Weight from Commitment Where ID IN (SELECT CommitmentID from Request1 Where ID = @ID)) As Char)

Select @Debtloan = Cast((select Weight from Debtloan Where ID IN (SELECT DebtloanID from Request1 Where ID = @ID)) As Char)

Set @MidWeight = Cast(ROUND((((ABS(@WeightFtime) + ABS(@WeightIdent) + ABS(@WeightWtermpres) + ABS(@WeightWtermlast) + ABS(@WeightWpaym) + ABS(@WeightCrsum) + ABS(@Region) + ABS(@Workform) + ABS(@Fvcountry) + ABS(@Ayear) + ABS(@Gender) + ABS(@Ftype) + ABS(@Fampos) + ABS(@Education) + ABS(@Children) + ABS(@Comptype) + ABS(@Compamount) + ABS(@Automob) + ABS(@Travel) + ABS(@Commitment) + ABS(@Debtloan)))/21),0) As Char)

IF (@MidWeight <= 6)

Update Request1 Set Offer = 'NO' Where ID = @ID

IF (@MidWeight > 6)

Update Request1 Set Offer = 'YES' Where ID = @ID

fetch next from @cursor into @ID

END

CLOSE @CURSOR

DEALLOCATE @CURSOR

DEALLOCATE c

End

Процедура формирования письма с решением

Исполняемый код процедуры описан в файле dbo.update_text

CREATE PROCEDURE dbo.update_text As

Begin

declare @cursor CURSOR

declare c CURSOR FOR

SELECT ID FROM Request1 where MessageText = null

declare @ID int

declare @Crsum money

declare @Term VarChar(20)

declare @ProdName VarChar(20)

declare @MessageText VarChar(5000)

declare @Offer VarChar(10)

declare @Prod VarChar(20)

set @cursor = c

OPEN @cursor

fetch next from @cursor into @ID

WHILE (@@FETCH_STATUS=0) BEGIN

Select @Offer = Cast((select Offer from Request1 Where ID = @ID) As Char)

Select @Crsum = Cast((select Crsum from Request1 Where ID = @ID) As money)

Select @Term = Cast((select Wnterm from Request1 Where ID = @ID) As Char)

Select @ProdName = Cast((Select ProdName from His_product where ProdConditID IN (Select ID_prod from His_prod_condit where ID_condit IN (Select ID from His_prodcondit where (@Crsum >= MinSum) and (@Crsum <= MaxSum) and (@Term >= MinTerms) and (@Term <= MaxTerms)))) As Char)

Select @Prod = Cast((Select count(ProdName) from His_product where ProdConditID IN (Select ID_prod from His_prod_condit where ID_condit IN (Select ID from His_prodcondit where (@Crsum >= MinSum) and (@Crsum <= MaxSum) and (@Term >= MinTerms) and (@Term <= MaxTerms)))) As Char)

IF (@Offer = 'YES') and (@Prod = 1)

BEGIN

Set @MessageText = Здравствуйте, Уважаемый(-ая) ' + (select FirstName from Request1 Where ID = @ID) + ' ' + (select ThirdName from Request1 Where ID = @ID) + '! Благодарим Вас за то, что обратили своё внимание на кредиты UniversityBank. Мы рады сообщить Вам, что по Вашей электронной заявке было принято положительное предварительное решение. Исходя из Ваших пожеланий, указанных в заявке, мы можем порекомендовать Вам обратить внимание на линейку наших кредитных продуктов "' + @ProdName + '. В ближайшее время наши сотрудники свяжутся с Вами для дальнейшего обсуждения деталей возможной выдачи кредита.'

Update Request1 Set MessageText = @MessageText Where ID = @ID

END

IF (@Offer = 'YES') and (@Prod = 0)

BEGIN

Set @MessageText = Здравствуйте, Уважаемый(-ая) ' + (select FirstName from Request1 Where ID = @ID) + ' ' + (select ThirdName from Request1 Where ID = @ID) + '! Благодарим Вас за то, что обратили своё внимание на кредиты UniversityBank. Мы рады сообщить Вам, что по Вашей электронной заявке было принято положительное предварительное решение. Исходя из Ваших пожеланий, указанных в заявке, мы пока не можем порекомендовать Вам один из наших кредитных продуктов, но мы готовы также и обсудить с Вами индивидуальные условия. В ближайшее время наши сотрудники свяжутся с Вами для дальнейшего обсуждения деталей возможной выдачи кредита.'

Update Request1 Set MessageText = @MessageText Where ID = @ID

END

IF @Offer = 'NO'

BEGIN

Set @MessageText = 'Здравствуйте, Уважаемый(-ая) ' + (select FirstName from Request1 Where ID = @ID) + ' ' + (select ThirdName from Request1 Where ID = @ID) + '! Благодарим Вас за то, что обратили своё внимание на кредиты UniversityBank. Мы с сожалением сообщаем Вам, что по Вашей электронной заявке было принято отрицательное предварительное решение, и в данный момент наш Банк не готов предоставить Вам кредит. В ближайшее время наши сотрудники свяжутся с Вами для оказания консультации по вопросу возможных условий выдачи кредита.'

Update Request1 Set MessageText = @MessageText Where ID = @ID

END

Update Request1 Set Message = 'NO' Where ID = @ID

fetch next from @cursor into @ID

END

CLOSE @CURSOR

DEALLOCATE @CURSOR

DEALLOCATE c

End

Процедура занесения заявки в базу

Исполняемый код процедуры описан в файле dbo.sp_RequestInsert

CREATE PROCEDURE dbo.sp_RequestInsert(@RegionID int, @WorkformID int, @Secname VarChar(100), @Firstname VarChar(100), @Thirdname VarChar(100), @Bdate datetime, @FvcountryID int, @Telnum VarChar (20), @Cellnum VarChar (20), @Email VarChar (50), @Crsum VarChar (50), @Wnterm int, @GenderID int, @Paspnum VarChar (50), @Dpasp datetime, @Infpasp VarChar (300), @Findex VarChar (20), @Farea VarChar (50), @Fcity VarChar (80), @Faddress VarChar (200), @Ftime int, @FtypeID int, @Add_ident binary (1), @Rindex VarChar (20), @Rarea VarChar (50), @Rcity VarChar (80), @Raddress VarChar (200), @Telreg VarChar (20), @FamposID int, @EducationID int, @ChildrenID int, @ComptypeID int, @Compname VarChar(200), @Compinn VarChar(20), @Compogrn VarChar(50), @Comppos VarChar(100), @Worknum VarChar(20), @Windex VarChar(20), @Warea VarChar(50), @Wcity VarChar(80), @Waddress VarChar(200), @CompamountID int, @Wtermpres int, @Wtermlast int, @Wpaym int, @AutomobID int, @TravelID int, @CommitmentID int, @DebtloanID int) As

Begin

Set NoCount Off

Declare @ID Int

Exec sp_newhyperkey @ID OutPut

Insert Into Request1 (ID,RegionID,WorkformID,Secname,Firstname,Thirdname,Bdate,FvcountryID,Telnum,Cellnum,Email,Crsum,Wnterm,GenderID,Paspnum,Dpasp,Infpasp,Findex,Farea,Fcity,Faddress,Ftime,FtypeID,Add_ident,Rindex,Rarea,Rcity,Raddress,Telreg,FamposID,EducationID,ChildrenID,ComptypeID,Compname,Compinn,Compogrn,Comppos,Worknum,Windex,Warea,Wcity,Waddress,CompamountID,Wtermpres,Wtermlast,Wpaym,AutomobID,TravelID,CommitmentID,DebtloanID) Values(@ID, @RegionID, @WorkformID, @Secname, @Firstname, @Thirdname, @Bdate, @FvcountryID, @Telnum, @Cellnum, @Email, @Crsum, @Wnterm, @GenderID, @Paspnum, @Dpasp, @Infpasp, @Findex, @Farea, @Fcity, @Faddress, @Ftime, @FtypeID, @Add_ident, @Rindex, @Rarea, @Rcity, @Raddress, @Telreg, @FamposID, @EducationID, @ChildrenID, @ComptypeID, @Compname, @Compinn, @Compogrn, @Comppos, @Worknum, @Windex, @Warea, @Wcity, @Waddress, @CompamountID, @Wtermpres, @Wtermlast, @Wpaym, @AutomobID, @TravelID, @CommitmentID, @DebtloanID)

End

Процедура генерации уникальных идентификаторов

Исполняемый код процедуры описан в файле dbo.sp_newhyperkey

CREATE procedure dbo.sp_newhyperkey @ret int out as

begin

declare @lastkey int

declare @empty bit

set @empty = 1

select @lastkey = id from main_sequence

if @@rowcount <> 0

set @empty = 0

insert into main_sequence (fdummy) values ('Y')

if @empty = 1

select @ret = id from main_sequence

else

begin

select top 1 @ret = id from main_sequence where id <> @lastkey

order by id desc

delete from main_sequence where id <> @ret;

end

end

Размещено на Allbest.ru


Подобные документы

Работы в архивах красиво оформлены согласно требованиям ВУЗов и содержат рисунки, диаграммы, формулы и т.д.
PPT, PPTX и PDF-файлы представлены только в архивах.
Рекомендуем скачать работу.