problems in Views creation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

problems in Views creation

Author

Topic

md.nasir6
Starting Member

3 Posts

Posted - 2010-11-24 : 01:08:00

Hi Everyone,

I got a problem while creating a views in sql server 2005. In my database there are several tables which are
accounts_transaction
bill_info
member_bill
member_info
relationship is exist between “member_info and accounts_transaction, member_info and member_bill, bill_info and member_bill” but there is not any relationship between bill_info and accounts_transaction.
Now I would like to create views to show member information along with their total bill stored in bill_info and member_bill, total payment stored in accounts_transaction. Therefore I have written the following sql query but these does not show me actual output of total bill and total payment due to not having relationship between accounts_transactiona and bill_info.

SQL Query:
select member_info.member_id, sum(bill_info.bill_amount) as billamount,
sum(accounts_transaction.credit_amount) as payamount,
sum(bill_info.bill_amount) - sum(accounts_transaction.credit_amount) as duesamount
from member_info, bill_info, member_bill, accounts_transaction
where member_info.member_id = accounts_transaction.member_id
and member_info.member_id = member_bill.member_id
and bill_info.bill_id = member_bill.bill_id
group by member_info.member_id

AND

SELECT dbo.member_info.member_id, SUM(dbo.bill_info.bill_amount) AS billamount, SUM(dbo.accounts_transaction.credit_amount) AS payamount,
SUM(dbo.bill_info.bill_amount) - SUM(dbo.accounts_transaction.credit_amount) AS duesamount
FROM dbo.member_info INNER JOIN
dbo.accounts_transaction ON dbo.member_info.member_id = dbo.accounts_transaction.member_id INNER JOIN
dbo.member_bill ON dbo.member_info.member_id = dbo.member_bill.member_id INNER JOIN
dbo.bill_info ON dbo.member_bill.bill_id = dbo.bill_info.bill_id
GROUP BY dbo.member_info.member_id

Please help me to find a good solution. If you need my full ERD please let me know at once.

nasiruddin

Sachin.Nand

2937 Posts

Posted - 2010-11-24 : 01:52:31

You need to post some sample data & required o/p.

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-11-24 : 01:58:50

Here is the link on how to do it

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

PBUH

md.nasir6
Starting Member

3 Posts

Posted - 2010-11-24 : 03:39:48

quote:
Originally posted by Sachin.Nand

You need to post some sample data & required o/p.

PBUH

dear sachin

it's better if i send u the full db in a script file. is it possible for to receive. thanks

nasiruddin

Sachin.Nand

2937 Posts

Posted - 2010-11-24 : 04:01:24

Yes you can do that.But just it should not be so big.You can post in this thread.also post expected o/p.

PBUH

md.nasir6
Starting Member

3 Posts

Posted - 2010-11-25 : 05:35:34

here the code is:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sl_max_voter_list_no]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sl_max_voter_list_no]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
select max(list_no) from voter_list
END'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sl_max_election_date]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sl_max_election_date]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
---select election_date from voter_list
---where list_no=(select max(list_no) from voter_list)
select max(election_date) from voter_list
END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[in_voter_info]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[in_voter_info]
-- Add the parameters for the stored procedure here
(
@id_no int,
@list_no int,
@voter_no int,
@member_id varchar(50),
@shop_no nvarchar(50),
@member_name nvarchar(50),
@total_bill real,
@total_payment real,
@dues real,
@voter_type nvarchar(50),
@condition nvarchar(50)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO dbo.voter_info
(
id_no,
list_no,
voter_no,
member_id,
shop_no,
member_name,
total_bill,
total_payment,
dues,
voter_type,
condition
)
VALUES
(
@id_no,
@list_no,
@voter_no,
@member_id,
@shop_no,
@member_name,
@total_bill,
@total_payment,
@dues,
@voter_type,
@condition
)
END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[in_voter_list]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[in_voter_list]
-- Add the parameters for the stored procedure here
(
@list_no int,
@start_date datetime,
@close_date datetime,
@election_date datetime,
@create_date datetime,
@regular_voter int,
@irregular_voter int,
@total_voter int,
@created_by nvarchar(50),
@approved_by nvarchar(50)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO dbo.voter_list
(
list_no,
start_date,
close_date,
election_date,
create_date,
regular_voter,
irregular_voter,
total_voter,
created_by,
approved_by
)
VALUES
(
@list_no,
@start_date,
@close_date,
@election_date,
@create_date,
@regular_voter,
@irregular_voter,
@total_voter,
@created_by,
@approved_by
)
END'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_voter_list]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[up_voter_list]
-- Add the parameters for the stored procedure here
(
@list_no int,
@regular_voter int,
@irregular_voter int,
@total_voter int
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
update voter_list set regular_voter=@regular_voter, irregular_voter=@irregular_voter,
total_voter=@total_voter where list_no=@list_no
END'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sl_max_voter_info_no]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sl_max_voter_info_no]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
select max(id_no) from voter_info
END'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[voter_list]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[voter_list](
[list_no] [nvarchar](50) NOT NULL,
[start_date] [datetime] NULL,
[close_date] [datetime] NULL,
[election_date] [datetime] NULL,
[create_date] [datetime] NULL,
[regular_voter] [int] NULL,
[irregular_voter] [int] NULL,
[total_voter] [int] NULL,
[created_by] [nvarchar](50) NULL,
[approved_by] [nvarchar](50) NULL,
CONSTRAINT [PK_voter_list] PRIMARY KEY CLUSTERED
(
[list_no] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[in_login_info]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[in_login_info]
-- Add the parameters for the stored procedure here
(
@login_name nvarchar(50),
@password nvarchar(50),
@create_date datetime,
@create_by nvarchar(50),
@approval_by nvarchar(50)

)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO dbo.login_info
(
login_name,
password,
create_date,
create_by,
approval_by
)
VALUES
(
@login_name,
@password,
@create_date,
@create_by,
@approval_by
)
END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[login_info]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[login_info](
[login_name] [nvarchar](50) NOT NULL,
[password] [nvarchar](50) NULL,
[create_date] [datetime] NULL,
[create_by] [nvarchar](50) NULL,
[approval_by] [nvarchar](50) NULL,
CONSTRAINT [PK_login_info_1] PRIMARY KEY CLUSTERED
(
[login_name] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[member_info]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[member_info](
[member_id] [varchar](50) NOT NULL,
[member_name] [nvarchar](50) NULL,
[member_type] [nvarchar](50) NULL,
[gender] [nvarchar](50) NULL,
[dob] [datetime] NULL,
[blood_group] [nvarchar](50) NULL,
[home_phone] [nvarchar](50) NULL,
[home_mobile] [nvarchar](50) NULL,
[permanent_address] [nvarchar](50) NULL,
[present_address] [nvarchar](50) NULL,
[member_photo] [image] NULL,
[shop_no] [nvarchar](50) NULL,
[company_name] [nvarchar](50) NULL,
[floor_no] [nvarchar](50) NULL,
[shop_position] [nvarchar](50) NULL,
[company_type] [nvarchar](50) NULL,
[office_address] [nvarchar](50) NULL,
[run_by] [nvarchar](50) NULL,
[relationship] [nvarchar](50) NULL,
[office_phone] [nvarchar](50) NULL,
[office_mobile] [nvarchar](50) NULL,
[other_photo] [image] NULL,
CONSTRAINT [PK_member_info] PRIMARY KEY CLUSTERED
(
[member_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[transaction_type]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[transaction_type](
[tr_type_id] [nvarchar](50) NOT NULL,
[tr_name] [nvarchar](50) NULL,
[tr_type] [nvarchar](50) NULL,
[create_date] [datetime] NULL,
[approval_by] [nvarchar](50) NULL,
[create_by] [nvarchar](50) NULL,
[description] [ntext] NULL,
CONSTRAINT [PK_transaction_type] PRIMARY KEY CLUSTERED
(
[tr_type_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sl_current_balance]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sl_current_balance]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
select balance from dbo.vw_current_balance
END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[voter_info]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[voter_info](
[id_no] [int] NOT NULL,
[list_no] [nvarchar](50) NULL,
[voter_no] [int] NULL,
[member_id] [varchar](50) NULL,
[shop_no] [nvarchar](50) NULL,
[member_name] [nvarchar](50) NULL,
[total_bill] [real] NULL,
[total_payment] [real] NULL,
[dues] [real] NULL,
[voter_type] [nvarchar](50) NULL,
[condition] [nvarchar](50) NULL,
CONSTRAINT [PK_voter_info] PRIMARY KEY CLUSTERED
(
[id_no] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[member_bill]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[member_bill](
[bill_id] [varchar](50) NULL,
[member_id] [varchar](50) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[accounts_transaction]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[accounts_transaction](
[transaction_no] [nvarchar](50) NOT NULL,
[member_id] [varchar](50) NULL,
[voucher_no] [varchar](50) NULL,
[transaction_date] [datetime] NULL,
[transaction_type] [nvarchar](50) NULL,
[tr_type_id] [nvarchar](50) NULL,
[credit_amount] [real] NULL,
[debit_amount] [real] NULL,
[transaction_mode] [nvarchar](50) NULL,
[check_no] [nvarchar](50) NULL,
[bank_name] [nvarchar](50) NULL,
[branch_name] [nvarchar](50) NULL,
[approval_date] [datetime] NULL,
[check_status] [nvarchar](50) NULL,
[current_balance] [real] NULL,
[description] [nvarchar](50) NULL,
[transaction_status] [nvarchar](50) NULL,
[create_by] [nvarchar](50) NULL,
[approval_by] [nvarchar](50) NULL,
CONSTRAINT [PK_accounts_transaction] PRIMARY KEY CLUSTERED
(
[transaction_no] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[bill_info]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[bill_info](
[bill_id] [varchar](50) NOT NULL,
[bill_date] [datetime] NULL,
[bill_amount] [real] NULL,
[pay_date] [datetime] NULL,
[approved_by] [nvarchar](50) NULL,
[create_by] [nvarchar](50) NULL,
[tr_type_id] [nvarchar](50) NULL,
[member_type] [nvarchar](100) NULL,
CONSTRAINT [PK_bill_info] PRIMARY KEY CLUSTERED
(
[bill_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sl_regular_voter_info]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sl_regular_voter_info]
-- Add the parameters for the stored procedure here
(
@start_date datetime,
@close_date datetime
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
select member_info.member_id, member_info.shop_no, member_info.member_name,
sum(accounts_transaction.credit_amount), sum(bill_info.bill_amount)
from member_info, bill_info, member_bill, accounts_transaction, voter_list, voter_info
where voter_list.start_date>=@start_date and
voter_list.close_date<=@close_date and
member_info.member_id != voter_info.member_id and
member_info.member_id=member_bill.member_id and
member_info.member_id=accounts_transaction.member_id and
bill_info.bill_id = member_bill.bill_id and
voter_list.list_no = voter_info.list_no
group by member_info.member_id,member_info.shop_no,member_info.member_name
having sum(accounts_transaction.credit_amount)-sum(bill_info.bill_amount)=0

END'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[in_member_bill]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[in_member_bill]
-- Add the parameters for the stored procedure here
(
@bill_id varchar(50),
@member_id varchar(50)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO dbo.member_bill
(
bill_id,
member_id
)
VALUES
(
@bill_id,
@member_id
)
END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sl_test]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
create PROCEDURE [dbo].[sl_test]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
select member_info.member_id, sum(bill_info.bill_amount) as billamount,
sum(accounts_transaction.credit_amount) as payamount,
sum(bill_info.bill_amount) - sum(accounts_transaction.credit_amount) as duesamount
from member_info, bill_info, member_bill, accounts_transaction
where member_info.member_id = member_bill.member_id
and bill_info.bill_id = member_bill.bill_id
and member_info.member_id = accounts_transaction.member_id
group by member_info.member_id, member_info.member_name
END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_member_balance]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vw_member_balance]
AS
SELECT dbo.member_info.member_id, SUM(dbo.bill_info.bill_amount) AS billamount, SUM(dbo.accounts_transaction.credit_amount) AS payamount,
SUM(dbo.bill_info.bill_amount) - SUM(dbo.accounts_transaction.credit_amount) AS duesamount
FROM dbo.member_info INNER JOIN
dbo.accounts_transaction ON dbo.member_info.member_id = dbo.accounts_transaction.member_id INNER JOIN
dbo.member_bill ON dbo.member_info.member_id = dbo.member_bill.member_id INNER JOIN
dbo.bill_info ON dbo.member_bill.bill_id = dbo.bill_info.bill_id
GROUP BY dbo.member_info.member_id
'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPane1' , N'SCHEMA',N'dbo', N'VIEW',N'vw_member_balance', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = "(H (1[20] 4[26] 2[40] 3) )"
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = "(H (1 [50] 4 [25] 3))"
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = "(H (1 [50] 2 [25] 3))"
End
Begin PaneConfiguration = 3
NumPanes = 3
Configuration = "(H (4 [30] 2 [40] 3))"
End
Begin PaneConfiguration = 4
NumPanes = 2
Configuration = "(H (1 [56] 3))"
End
Begin PaneConfiguration = 5
NumPanes = 2
Configuration = "(H (2 [66] 3))"
End
Begin PaneConfiguration = 6
NumPanes = 2
Configuration = "(H (4 [50] 3))"
End
Begin PaneConfiguration = 7
NumPanes = 1
Configuration = "(V (3))"
End
Begin PaneConfiguration = 8
NumPanes = 3
Configuration = "(H (1[56] 4[18] 2) )"
End
Begin PaneConfiguration = 9
NumPanes = 2
Configuration = "(H (1 [75] 4))"
End
Begin PaneConfiguration = 10
NumPanes = 2
Configuration = "(H (1[66] 2) )"
End
Begin PaneConfiguration = 11
NumPanes = 2
Configuration = "(H (4 [60] 2))"
End
Begin PaneConfiguration = 12
NumPanes = 1
Configuration = "(H (1) )"
End
Begin PaneConfiguration = 13
NumPanes = 1
Configuration = "(V (4))"
End
Begin PaneConfiguration = 14
NumPanes = 1
Configuration = "(V (2))"
End
ActivePaneConfig = 0
End
Begin DiagramPane =
Begin Origin =
Top = 0
Left = 0
End
Begin Tables =
Begin Table = "member_info"
Begin Extent =
Top = 96
Left = 437
Bottom = 211
Right = 614
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "accounts_transaction"
Begin Extent =
Top = 6
Left = 38
Bottom = 121
Right = 225
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "member_bill"
Begin Extent =
Top = 6
Left = 437
Bottom = 91
Right = 589
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "bill_info"
Begin Extent =
Top = 6
Left = 247
Bottom = 121
Right = 399
End
DisplayFlags = 280
TopColumn = 0
End
End
End
Begin SQLPane =
End
Begin DataPane =
Begin ParameterDefaults = ""
End
End
Begin CriteriaPane =
Begin ColumnWidths = 12
Column = 1440
Alias = 900
Table = 1170
Output = 720
Append = 1400
NewValue = 1170
SortType = 1350
SortOrder = 1410
GroupBy = 1350
Filter = 1350
Or = 1350
Or = 1350
Or = 1350
End
End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_member_balance'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPaneCount' , N'SCHEMA',N'dbo', N'VIEW',N'vw_member_balance', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_member_balance'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sl_max_voter_list_info]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sl_max_voter_list_info]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
select
start_date,
close_date,
created_by,
approved_by,
create_date,
list_no,
election_date,
total_voter
from
voter_list
where
list_no=(select max(list_no) from voter_list)
END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sl_last_total_irregular_voter]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sl_last_total_irregular_voter]
-- Add the parameters for the stored procedure here
(
@list_no int
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
select irregular_voter from voter_list where list_no=@list_no
END'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sl_last_total_regular_voter]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sl_last_total_regular_voter]
-- Add the parameters for the stored procedure here
(
@list_no int
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
select regular_voter from voter_list where list_no=@list_no
END'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sl_last_total_voter]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sl_last_total_voter]
-- Add the parameters for the stored procedure here
(
@list_no int
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
select total_voter from voter_list where list_no=@list_no
END'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_current_balance]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[up_current_balance]
-- Add the parameters for the stored procedure here
(
@transaction_no nvarchar(50),
@current_balance real
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
update accounts_transaction set current_balance=@current_balance
where transaction_no=@transaction_no
END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sl_max_accounts_no]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sl_max_accounts_no]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
select max(transaction_no) from accounts_transaction
END'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_expense_transaction]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[up_expense_transaction]
-- Add the parameters for the stored procedure here
(
@transaction_no nvarchar(50),
@voucher_no varchar(50),
@transaction_date datetime,
@transaction_type nvarchar(50),
@tr_type_id nvarchar(50),
@debit_amount real,
@transaction_mode nvarchar(50),
@check_no nvarchar(50),
@bank_name nvarchar(50),
@branch_name nvarchar(50),
@approval_date datetime,
@check_status nvarchar(50),
@description nvarchar(50),
@transaction_status nvarchar(50),
@create_by nvarchar(50),
@approval_by nvarchar(50)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
Update dbo.accounts_transaction
set
voucher_no=@voucher_no,
transaction_date = @transaction_date,
transaction_type = @transaction_type,
tr_type_id = @tr_type_id,
debit_amount = @debit_amount,
transaction_mode = @transaction_mode,
check_no = @check_no,
bank_name = @bank_name,
branch_name = @branch_name,
approval_date = @approval_date,
check_status = @check_status,
description = @description,
transaction_status = @transaction_status,
create_by = @create_by,
approval_by = @approval_by

where transaction_no = @transaction_no
END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_transaction_status]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[up_transaction_status]
-- Add the parameters for the stored procedure here
(
@transaction_no nvarchar(50),
@transaction_status nvarchar(50)

)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
Update dbo.accounts_transaction
set
transaction_status = @transaction_status
where
transaction_no = @transaction_no
END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sl_income_details_by_vid]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[sl_income_details_by_vid]
-- Add the parameters for the stored procedure here
(
@voucher_no varchar(50)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT transaction_no,
voucher_no,
credit_amount,
tr_type_id,
transaction_mode,
bank_name,
branch_name,
check_no,
approval_date,
create_by,
transaction_date,
member_id

from dbo.accounts_transaction
where voucher_no=@voucher_no
and transaction_type=''Income Type''
and transaction_status=''OK''
END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sl_expense_details_by_vid]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[sl_expense_details_by_vid]
-- Add the parameters for the stored procedure here
(
@voucher_no varchar(50)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT transaction_no,
voucher_no,
debit_amount,
tr_type_id,
transaction_mode,
bank_name,
branch_name,
check_no,
approval_date,
approval_by ,
description,
create_by,
transaction_date
from dbo.accounts_transaction
where voucher_no=@voucher_no
and transaction_type=''Expense Type''
and transaction_status=''OK''
END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_payment_transaction]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[up_payment_transaction]
-- Add the parameters for the stored procedure here
(
@transaction_no nvarchar(50),
@member_id varchar(50),
@voucher_no varchar(50),
@transaction_date datetime,
@transaction_type nvarchar(50),
@tr_type_id nvarchar(50),
@credit_amount real,
@transaction_mode nvarchar(50),
@check_no nvarchar(50),
@bank_name nvarchar(50),
@branch_name nvarchar(50),
@approval_date datetime,
@check_status nvarchar(50),
@description nvarchar(50),
@transaction_status nvarchar(50),
@create_by nvarchar(50)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
UPDATE dbo.accounts_transaction SET
member_id = @member_id,
voucher_no = @voucher_no,
transaction_date = @transaction_date,
transaction_type = @transaction_type,
tr_type_id = @tr_type_id,
credit_amount = @credit_amount,
transaction_mode = @transaction_mode,
check_no = @check_no,
bank_name = @bank_name,
branch_name = @branch_name,
approval_date = @approval_date,
check_status = @check_status,
description = @description,
transaction_status = @transaction_status,
create_by = @create_by
WHERE transaction_no = @transaction_no
END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[in_payment_transaction]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[in_payment_transaction]
-- Add the parameters for the stored procedure here
(
@transaction_no nvarchar(50),
@member_id varchar(50),
@voucher_no varchar(50),
@transaction_date datetime,
@transaction_type nvarchar(50),
@tr_type_id nvarchar(50),
@credit_amount real,
@transaction_mode nvarchar(50),
@check_no nvarchar(50),
@bank_name nvarchar(50),
@branch_name nvarchar(50),
@approval_date datetime,
@check_status nvarchar(50),
@description nvarchar(50),
@transaction_status nvarchar(50),
@create_by nvarchar(50)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO dbo.accounts_transaction
(
transaction_no,
member_id,
voucher_no,
transaction_date,
transaction_type,
tr_type_id,
credit_amount,
transaction_mode,
check_no,
bank_name,
branch_name,
approval_date,
check_status,
description,
transaction_status,
create_by
)
VALUES
(
@transaction_no,
@member_id,
@voucher_no,
@transaction_date,
@transaction_type,
@tr_type_id,
@credit_amount,
@transaction_mode,
@check_no,
@bank_name,
@branch_name,
@approval_date,
@check_status,
@description,
@transaction_status,
@create_by
)
END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[in_expense_transaction]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[in_expense_transaction]
-- Add the parameters for the stored procedure here
(
@transaction_no nvarchar(50),
@voucher_no varchar(50),
@transaction_date datetime,
@transaction_type nvarchar(50),
@tr_type_id nvarchar(50),
@debit_amount real,
@transaction_mode nvarchar(50),
@check_no nvarchar(50),
@bank_name nvarchar(50),
@branch_name nvarchar(50),
@approval_date datetime,
@check_status nvarchar(50),
@description nvarchar(50),
@transaction_status nvarchar(50),
@create_by nvarchar(50),
@approval_by nvarchar(50)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO dbo.accounts_transaction
(
transaction_no,
voucher_no,
transaction_date,
transaction_type,
tr_type_id,
debit_amount,
transaction_mode,
check_no,
bank_name,
branch_name,
approval_date,
check_status,
description,
transaction_status,
create_by,
approval_by
)
VALUES
(
@transaction_no,
@voucher_no,
@transaction_date,
@transaction_type,
@tr_type_id,
@debit_amount,
@transaction_mode,
@check_no,
@bank_name,
@branch_name,
@approval_date,
@check_status,
@description,
@transaction_status,
@create_by,
@approval_by
)
END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sl_login_info]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sl_login_info]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT * FROM dbo.login_info
END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[del_login_info]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[del_login_info]
-- Add the parameters for the stored procedure here
(
@login_name nvarchar(50)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
delete from dbo.login_info where login_name=@login_name
END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ck_login_info]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[ck_login_info]
-- Add the parameters for the stored procedure here
(
@login_name nvarchar(50),
@password nvarchar(50)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
select * from dbo.login_info
where login_name=@login_name
and password=@password

END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sl_login_name]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sl_login_name]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT login_name FROM dbo.login_info
END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sl_max_member_no]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sl_max_member_no]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
select max(member_id) from member_info
END'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sl_max_shop_no]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sl_max_shop_no]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
select max(shop_no) from member_info
END'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sl_member_details_by_mid]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[sl_member_details_by_mid]
-- Add the parameters for the stored procedure here
(
@member_id varchar(50)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT * from dbo.member_info where member_id=@member_id
END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_member_info]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: M Nasir Uddin
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[up_member_info]
-- Add the parameters for the stored procedure here
(
@member_id varchar(50),
@member_name nvarchar(50),
@member_type nvarchar(50),
@gender nvarchar(50),
@dob datetime,
@blood_group nvarchar(50),
@home_phone nvarchar(50),
@home_mobile nvarchar(50),
@permanent_address nvarchar(50),
@present_address nvarchar(50),
@member_photo image,
@shop_no nvarchar(50),
@company_name nvarchar(50),
@floor_no nvarchar(50),
@shop_position nvarchar(50),
@company_type nvarchar(50),
@office_address nvarchar(50),
@run_by nvarchar(50),
@relationship nvarchar(50),
@office_phone nvarchar(50),
@office_mobile nvarchar(50),
@other_photo image

)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
update dbo.member_info set

member_name = @member_name,
member_type = @member_type,
gender = @gender,
dob = @dob,
blood_group = @blood_group,
home_phone = @home_phone,
home_mobile = @home_mobile,
permanent_address = @permanent_address,
present_address = @present_address,
member_photo = @member_photo,
shop_no = @shop_no,
company_name = @company_name,
floor_no = @floor_no,
shop_position = @shop_position,
company_type = @company_type,
office_address = @office_address,
run_by = @run_by,
relationship = @relationship,
office_phone = @office_phone,
office_mobile = @office_mobile,
other_photo = @other_photo

where member_id = @member_id;
END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sl_other_image]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sl_other_image]
-- Add the parameters for the stored procedure here
(
@member_id varchar(50)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT other_photo FROM dbo.member_info WHERE member_id=@member_id
END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sl_member_info_bill]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sl_member_info_bill]
-- Add the parameters for the stored procedure here
(
@member_type nvarchar(50)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT member_id, shop_no, member_name FROM member_info
where member_type = @member_type
END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sl_member_image]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sl_member_image]
-- Add the parameters for the stored procedure here
(
@member_id varchar(50)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT member_photo FROM dbo.member_info WHERE member_id=@member_id
END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[in_member_info]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: M Nasir Uddin
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[in_member_info]
-- Add the parameters for the stored procedure here
(
@member_id varchar(50),
@member_name nvarchar(50),
@member_type nvarchar(50),
@gender nvarchar(50),
@dob datetime,
@blood_group nvarchar(50),
@home_phone nvarchar(50),
@home_mobile nvarchar(50),
@permanent_address nvarchar(50),
@present_address nvarchar(50),
@member_photo image,
@shop_no nvarchar(50),
@company_name nvarchar(50),
@floor_no nvarchar(50),
@shop_position nvarchar(50),
@company_type nvarchar(50),
@office_address nvarchar(50),
@run_by nvarchar(50),
@relationship nvarchar(50),
@office_phone nvarchar(50),
@office_mobile nvarchar(50),
@other_photo image

)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO dbo.member_info
(
member_id,
member_name,
member_type,
gender,
dob,
blood_group,
home_phone,
home_mobile,
permanent_address,
present_address,
member_photo,
shop_no,
company_name,
floor_no,
shop_position,
company_type,
office_address,
run_by,
relationship,
office_phone,
office_mobile,
other_photo
)
VALUES
(
@member_id,
@member_name,
@member_type,
@gender,
@dob,
@blood_group,
@home_phone,
@home_mobile,
@permanent_address,
@present_address,
@member_photo,
@shop_no,
@company_name,
@floor_no,
@shop_position,
@company_type,
@office_address,
@run_by,
@relationship,
@office_phone,
@office_mobile,
@other_photo
)
END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sl_all_member_id]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[sl_all_member_id]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT member_id FROM dbo.member_info
END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sl_trans_name_by_id]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sl_trans_name_by_id]
-- Add the parameters for the stored procedure here
(
@tr_type_id nvarchar(50)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT tr_name
FROM dbo.transaction_type
WHERE tr_type_id=@tr_type_id
END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sl_trans_info]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sl_trans_info]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT tr_type_id, tr_name, tr_type, create_date, approval_by, create_by, description
FROM dbo.transaction_type
END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[in_trans_info]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[in_trans_info]
-- Add the parameters for the stored procedure here
(
@tr_type_id nvarchar(50),
@tr_name nvarchar(50),
@tr_type nvarchar(50),
@create_date datetime,
@approval_by nvarchar(50),
@create_by nvarchar(50),
@description ntext
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
INSERT INTO dbo.transaction_type
(
tr_type_id,
tr_name,
tr_type,
create_date,
approval_by,
create_by,
description
)
VALUES
(
@tr_type_id,
@tr_name,
@tr_type,
@create_date,
@approval_by,
@create_by,
@description
)
END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sl_trans_name]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[sl_trans_name]
-- Add the parameters for the stored procedure here
(
@tr_type nvarchar(50)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT tr_name FROM dbo.transaction_type WHERE tr_type=@tr_type
END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sl_trans_type_ID]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sl_trans_type_ID]
-- Add the parameters for the stored procedure here
(
@tr_name nvarchar(50)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT tr_type_id FROM dbo.transaction_type WHERE tr_name=@tr_name
END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sl_max_trans_type_no]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sl_max_trans_type_no]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
select max(tr_type_id) from transaction_type
END'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sl_max_Bill_no]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sl_max_Bill_no]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
select max(bill_id) from bill_info
END'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[del_member_bill]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[del_member_bill]
-- Add the parameters for the stored procedure here
(
@bill_id varchar(50)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
delete from dbo.bill_info where bill_id=@bill_id
END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sl_bill_info_by_bid]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sl_bill_info_by_bid]
-- Add the parameters for the stored procedure here
(
@bill_id varchar(50)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT * FROM dbo.bill_info where bill_id = @bill_id
END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_bill_info]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[up_bill_info]
-- Add the parameters for the stored procedure here
(
@bill_id varchar(50),
@bill_date datetime,
@bill_amount real,
@pay_date datetime,
@approved_by nvarchar(50),
@create_by nvarchar(50),
@tr_type_id nvarchar(50),
@member_type nvarchar(100)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
update dbo.bill_info
set
bill_date = @bill_date,
bill_amount = @bill_amount,
pay_date = @pay_date,
approved_by = @approved_by,
create_by = @create_by,
tr_type_id = @tr_type_id,
member_type = @member_type
where
bill_id = @bill_id
END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[in_bill_info]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[in_bill_info]
-- Add the parameters for the stored procedure here
(
@bill_id varchar(50),
@bill_date datetime,
@bill_amount real,
@pay_date datetime,
@approved_by nvarchar(50),
@create_by nvarchar(50),
@tr_type_id nvarchar(50),
@member_type nvarchar(100)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO dbo.bill_info
(
bill_id,
bill_date,
bill_amount,
pay_date,
approved_by,
create_by,
tr_type_id,
member_type
)
VALUES
(
@bill_id,
@bill_date,
@bill_amount,
@pay_date,
@approved_by,
@create_by,
@tr_type_id,
@member_type
)
END

'
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_voter_info_member_info]') AND parent_object_id = OBJECT_ID(N'[dbo].[voter_info]'))
ALTER TABLE [dbo].[voter_info] WITH CHECK ADD CONSTRAINT [FK_voter_info_member_info] FOREIGN KEY([member_id])
REFERENCES [dbo].[member_info] ([member_id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[voter_info] CHECK CONSTRAINT [FK_voter_info_member_info]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_voter_info_voter_list]') AND parent_object_id = OBJECT_ID(N'[dbo].[voter_info]'))
ALTER TABLE [dbo].[voter_info] WITH CHECK ADD CONSTRAINT [FK_voter_info_voter_list] FOREIGN KEY([list_no])
REFERENCES [dbo].[voter_list] ([list_no])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[voter_info] CHECK CONSTRAINT [FK_voter_info_voter_list]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_member_bill_bill_info]') AND parent_object_id = OBJECT_ID(N'[dbo].[member_bill]'))
ALTER TABLE [dbo].[member_bill] WITH CHECK ADD CONSTRAINT [FK_member_bill_bill_info] FOREIGN KEY([bill_id])
REFERENCES [dbo].[bill_info] ([bill_id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[member_bill] CHECK CONSTRAINT [FK_member_bill_bill_info]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_member_bill_member_info1]') AND parent_object_id = OBJECT_ID(N'[dbo].[member_bill]'))
ALTER TABLE [dbo].[member_bill] WITH CHECK ADD CONSTRAINT [FK_member_bill_member_info1] FOREIGN KEY([member_id])
REFERENCES [dbo].[member_info] ([member_id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[member_bill] CHECK CONSTRAINT [FK_member_bill_member_info1]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_accounts_transaction_member_info]') AND parent_object_id = OBJECT_ID(N'[dbo].[accounts_transaction]'))
ALTER TABLE [dbo].[accounts_transaction] WITH CHECK ADD CONSTRAINT [FK_accounts_transaction_member_info] FOREIGN KEY([member_id])
REFERENCES [dbo].[member_info] ([member_id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[accounts_transaction] CHECK CONSTRAINT [FK_accounts_transaction_member_info]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_accounts_transaction_transaction_type]') AND parent_object_id = OBJECT_ID(N'[dbo].[accounts_transaction]'))
ALTER TABLE [dbo].[accounts_transaction] WITH CHECK ADD CONSTRAINT [FK_accounts_transaction_transaction_type] FOREIGN KEY([tr_type_id])
REFERENCES [dbo].[transaction_type] ([tr_type_id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[accounts_transaction] CHECK CONSTRAINT [FK_accounts_transaction_transaction_type]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_bill_info_transaction_type]') AND parent_object_id = OBJECT_ID(N'[dbo].[bill_info]'))
ALTER TABLE [dbo].[bill_info] WITH CHECK ADD CONSTRAINT [FK_bill_info_transaction_type] FOREIGN KEY([tr_type_id])
REFERENCES [dbo].[transaction_type] ([tr_type_id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[bill_info] CHECK CONSTRAINT [FK_bill_info_transaction_type]

please see the views vw_member_balance. here u can find the wrong data both in billamount and payamount field in comparison with 'accounts_transaction' and bill_info and member_bill table.
please reply if u know the solution.

nasiruddin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources