Sql存储过程 [英] Sql Stored Procedure

问题描述

1.UserInfo

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

2.FileInfo

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

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

我需要如果userid 1是上传文件，用userid保存在fileinfo中的db中，并且所有deatils都要存储在db中

fileId userId fileName fileType fileSize filePath createdOn statusId lastModifiedOn

8 2 A. sp 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



我在存储过程中实现它存储的Proc.It 's不工作。

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.

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

推荐答案

对不起请清除您的查询。



如果2表合并记录使用简单连接和获取数据。

Sorry please Clear Your Query.

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

这篇关于Sql存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！