Sql Stored Procedure

Question

0.00/5 (No votes)

See more:

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

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

ALTER TABLE [dbo].[fileInfo]  WITH CHECK ADD FOREIGN KEY([statusId])
REFERENCES [dbo].[status] ([statusId])
GO

ALTER TABLE [dbo].[fileInfo]  WITH CHECK ADD FOREIGN KEY([statusId])
REFERENCES [dbo].[status] ([statusId])
GO

ALTER TABLE [dbo].[fileInfo]  WITH CHECK ADD FOREIGN KEY([statusId])
REFERENCES [dbo].[status] ([statusId])
GO

ALTER TABLE [dbo].[fileInfo]  WITH CHECK ADD FOREIGN KEY([userId])
REFERENCES [dbo].[userInfo] ([userId])
GO

ALTER TABLE [dbo].[fileInfo]  WITH CHECK ADD FOREIGN KEY([userId])
REFERENCES [dbo].[userInfo] ([userId])
GO

ALTER TABLE [dbo].[fileInfo]  WITH CHECK ADD FOREIGN KEY([userId])
REFERENCES [dbo].[userInfo] ([userId])
GO

I need if userid 1 is upload file to save value in db in fileinfo by userid and all deatils to store in db
fileId userId fileName fileType fileSize filePath createdOn statusId lastModifiedOn
8 2 Asp notes.docx .docx 13539 2013-04-04-05-52\ 2013-04-04 17:52:20.173 6 NULL
9 2 Asp.net.docx .docx 14041 2013-04-04-05-52\ 2013-04-04 17:52:20.647 6 NULL

I Implement in stored proc it's stored Proc.It's not working.

SQL

create proc Insert_fileinfo @userId bigint,@FileName nvarchar(30), @FileType  nvarchar(30),@fileSize  nvarchar(30),@filePath  nvarchar(30),@statusId int
as
insert into fileInfo(userId,fileName,fileType,fileSize,filePath,statusId,createdOn)
values(
(select userID from fileInfo where
fileName=@FileName and fileType=@FileType and fileSize=@fileSize and filePath=@filePath and statusId=3 and createdOn=CURRENT_TIMESTAMP and userId=
(select userId from userInfo where userId=@userId)))

Thanks Friends...

Posted 25-Apr-13 2:48am

Rekhash

Add a Solution

Comments

[no name] 25-Apr-13 8:53am

What does "It's not working" mean? Does it run? Does it not run? It runs but your cat ran away?

Rekhash 25-Apr-13 9:02am

@ThePhantomUpvoter
fileId userId fileName fileType fileSize filePath createdOn statusId lastModifiedOn
8 2 Asp notes.docx .docx 13539 2013-04-04-05-52\ 2013-04-04 17:52:20.173 6 NULL
9 3 Asp.net.docx .docx 14041 2013-04-04-05-52\ 2013-04-04 17:52:20.647 6 NULL
Like that db Want to store. If userId 3 means change insert the vales 3 for Db in UserID

1 solution

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)

Abhai Oza · Answer 1 · 2013-04-25T02:54:00

Solution 1

Sorry please Clear Your Query.

if 2 table merge record to use Simple Join and Get Data.

Posted 25-Apr-13 2:54am

Abhai Oza

Comments

Rekhash 25-Apr-13 9:03am

@Achal Oza
It's Problem
Like that db Want to store. If userId 3 means change insert the vales 3 for Db in UserID
create proc Insert_fileinfo @userId bigint,@FileName nvarchar(30), @FileType nvarchar(30),@fileSize nvarchar(30),@filePath nvarchar(30),@statusId int
as
insert into fileInfo(userId,fileName,fileType,fileSize,filePath,statusId,createdOn)
values(
(select userID from fileInfo where
fileName=@FileName and fileType=@FileType and fileSize=@fileSize and filePath=@filePath and statusId=3 and createdOn=CURRENT_TIMESTAMP and userId=
(select userId from userInfo where userId=@userId)))
merge tables and insert values in FileInfo Table...