Sql Stored Procedure

Question

0.00/5 (No votes)

See more:

1.FileInfo

SQL

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

SQL

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

SQL

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

SQL

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 :
&lt;pre&gt;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	&lt;/pre&gt;

if userId is 2 can select Multiple file depending upon the status id is 1,2,3,4.

Procedure

SQL

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'.

Posted 4-Apr-13 19:29pm

Rekhash

Updated 4-Apr-13 19:45pm

Prasad Khandekar

v3

Add a Solution

Comments

Sandeep Mewara 5-Apr-13 1:34am

Please use 'Improve Question' link and edit/update your question instead of posting as comments.

Further, it's not clear what are you doing and what is the issue that troubles you.

Rekhash 5-Apr-13 1:47am

@Sandeep Mewara
i improved My question..

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 2707515 · Answer 1 · 2013-04-04T20:14:00

Solution 3

SQL

alter proc C @statusId bigint,@userId bigint
 as
select a.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

its working fine

Posted 4-Apr-13 20:14pm

Member 2707515

Updated 4-Apr-13 20:59pm

v2

RelicV · Answer 2 · 2013-04-04T21:21:00

Hi Rekhash,

Please start using alias name followed by the '.' and column name for better coding. It just makes sense when someone tries to go through a stored procedure (sproc). Here in your case, the Database was unable to retrieve the data as the statusId column exists in both the table mentioned in the sproc.

It is a good practice to always use alias prefixes as it makes it clear which columns are coming from which tables when you read the query, and eliminates issues like this one.

Eg:

SQL

Alter proc C  @statusId bigint,@userId bigint
 as
select a.statusId, a.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

Happy coding.!

Thank you,
Vamsi