1.FileInfo
CREATE TABLE [dbo].[fileInfo](
[fileId] [bigint] IDENTITY(1,1) NOT NULL,
[userId] [bigint] NULL,
[fileName] [nvarchar](50) NOT NULL,
[fileType] [nvarchar](50) NOT NULL,
[fileSize] [nvarchar](100) NOT NULL,
[filePath] [nvarchar](200) NOT NULL,
[createdOn] [datetime] NULL,
[statusId] [bigint] NULL,
[lastModifiedOn] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[fileId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
2.UserInfo
CREATE TABLE [dbo].[userInfo](
[userId] [bigint] IDENTITY(1,1) NOT NULL,
[email] [nvarchar](30) NOT NULL,
[password] [nvarchar](20) NOT NULL,
[firstName] [nvarchar](50) NULL,
[lastName] [nvarchar](50) NULL,
[activationKey] [nvarchar](800) NULL,
[active] [bit] NULL,
[createdOn] [datetime] NULL,
[modifiedOn] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[userId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[email] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
3.Status
CREATE TABLE [dbo].[status](
[statusId] [bigint] IDENTITY(1,1) NOT NULL,
[statusName] [nvarchar](30) NOT NULL,
[statusColor] [nvarchar](10) NOT NULL,
[active] [bit] NULL,
[createdOn] [datetime] NULL,
[modifiedOn] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[statusId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Status
Insert into status(statusName,statusColor,createdOn) VALUES ('completed','#86d280',CURRENT_TIMESTAMP)
Insert into status(statusName,statusColor,createdOn) VALUES ('success','#86d280',CURRENT_TIMESTAMP)
Insert into status(statusName,statusColor,createdOn) VALUES ('pending','#f8de80',CURRENT_TIMESTAMP)
Insert into status(statusName,statusColor,createdOn) VALUES ('progressing','#f8de80',CURRENT_TIMESTAMP)
Insert into status(statusName,statusColor,createdOn) VALUES ('yet to start','#e28383',CURRENT_TIMESTAMP)
Insert into status(statusName,statusColor,createdOn) VALUES ('failure','#e28383',CURRENT_TIMESTAMP)
FileInfo table :
<pre>fileId UserId fileName fileType fileSize StatusId
8 2 Asp notes.docx .docx 13539 2
9 2 Asp.net.docx .docx 14041 2
10 2 create database G2O.docx .docx 39484 2
11 2 Introduction to ASP.docx .docx 16451 2
12 2 Master Page.docx .docx 13514 2
13 2 page without refresh.docx .docx 16124 2
14 2 paper sizes new.doc .doc 421888 3
15 2 Paper sizes.docx .docx 103226 2
16 2 Printing Type.doc .doc 37888 3
17 2 promote product.docx .docx 17358 2
18 2 promote products.docx .docx 14723 2
19 2 rekha.docx .docx 14023 2
20 2 screenshot creator.docx .docx 13784 2
21 2 Sql server 2008 in index.docx .docx 16505 2
22 2 SQL Server sp.doc .doc 46592 3
23 2 Table.xlsx .xlsx 15177 4
24 2 task-3.docx .docx 17494 2
25 2 today_index.docx .docx 14271 2
26 2 Top eCommerce Websites in India.docx .docx 13798 2
27 2 top ten web analystic.docx .docx 14019 2
28 2 use test.docx .docx 15294 2
30 2 zoomit.png .png 218944 1
31 2 Literal and Label Controls.docx .docx 16141 2
32 2 Master Page Overview.docx .docx 12976 2
33 2 map.xlsx .xlsx 11692 4
34 2 Key Elements of BuddyPress.docx .docx 183234 2
35 2 Index.docx .docx 13299 2
36 2 Blend Application.docx .docx 13470 2
37 2 WPF Website.doc .doc 26112 3
38 2 wpf in links.doc .doc 22016 3
39 2 WPF in blend appln.doc .doc 25600 3
40 2 url.png .png 1711 1 </pre>
if userId is 2 can select Multiple file depending upon the status id is 1,2,3,4.
Procedure
Alter proc C @statusId bigint,@userId bigint
as
select statusId,statusColor from status a
inner join fileInfo b
on a.statusId=b.statusId and a.statusId=@statusId
inner join userInfo c
on b.userId=c.userId and b.userId=@userId
Procedure displays error as
Msg 209, Level 16, State 1, Procedure C, Line 3
Ambiguous column name 'statusId'.