SQL SERVER中的存储过程 [英] Stored procedure in SQL SERVER
本文介绍了SQL SERVER中的存储过程的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
大家好,
我必须编写存储过程,它将文件名,文件名没有扩展名从给定的路径。我从代码项目获得以下代码。问题是我必须在给定文件夹中获取所有文件名。以下代码适用于给定路径。你能帮我吗?
ALTER PROCEDURE [dbo]。[ReadFileName]
- 在此处添加存储过程的参数
@ FILE_PATH VARCHAR (MAX),
@ NAME VARCHAR (MAX) OUTPUT
AS
BEGIN
- 添加SET NOCOUNT ON以防止额外的结果集
- 干扰SELECT语句。
SET NOCOUNT ON 矿泉n>键;
DECLARE @ FILE_LENGTH INT
DECLARE @ FILE_NAME VARCHAR (MAX)
SET @ FILE_LENGTH = LEN(< span class =code-sdkkeyword> @ FILE_PATH )
WHILE ( @ FILE_LENGTH <> 0 )
BEGIN
IF SUBSTRING( @ FILE_PATH , @ FILE_LENGTH , 1 )= ' \'
< span class =code-keyword> BEGIN
SET @ FILE_NAME = RIGHT ( @ FILE_PATH ,LEN( @ FILE_PATH ) - @ FILE_LENGTH )
BREAK
END
SET @ FILE_LENGTH = @ FILE_LENGTH - 1
END
SET @ NAME = @ FILE_NAME
INSERT INTO TABLE_NAME(COL1,COL2,COL3)值( @ FILE_NAME , @ FIL E_PATH , @ FILE_NAME )
更新 TABLE_NAME
SET COL1 = REVERSE(SUBSTRING(REVERSE(COL1),
CHARINDEX(' 。',REVERSE(COL1))+ 1 , 999 ))
返回
END
解决方案
Hello XYZ,
您可以使用master.sys.xp_dirtree存储过程来实现此目的。此过程采用以下参数
- 目录 - 要扫描的目录的完整路径
- depth - 数量遍历的许多子文件夹级别。默认值为0将显示所有子文件夹。
- file - 启用文件或文件夹显示的值。默认值0不会显示任何文件。
声明 @ files table (ID int IDENTITY ,FileName varchar ( 100 ))
插入 进入 @ files 执行 xp_cmdshell ' dir c:\ / b'
选择 * 来自 @ files
原始答案:如何阅读文件列表在使用T-SQL的文件夹中...... [ ^ ]
Hi all,
I have to write stored procedure which will file name,file name without extension from given path.I got following code from code project.The Problem is I have to get all files name in a given folder.The below code is working for given path. Can you please help me.
ALTER PROCEDURE [dbo].[ReadFileName]
-- Add the parameters for the stored procedure here
@FILE_PATH VARCHAR(MAX),
@NAME VARCHAR(MAX) OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @FILE_LENGTH INT
DECLARE @FILE_NAME VARCHAR(MAX)
SET @FILE_LENGTH = LEN(@FILE_PATH)
WHILE (@FILE_LENGTH <> 0)
BEGIN
IF SUBSTRING(@FILE_PATH, @FILE_LENGTH, 1) = '\'
BEGIN
SET @FILE_NAME = RIGHT(@FILE_PATH, LEN(@FILE_PATH) - @FILE_LENGTH)
BREAK
END
SET @FILE_LENGTH = @FILE_LENGTH - 1
END
SET @NAME = @FILE_NAME
INSERT INTO TABLE_NAME(COL1,COL2,COL3) values(@FILE_NAME,@FILE_PATH,@FILE_NAME)
UPDATE TABLE_NAME
SET COL1 = REVERSE(SUBSTRING(REVERSE(COL1),
CHARINDEX('.', REVERSE(COL1)) + 1, 999))
RETURN
END
解决方案
Hello XYZ,
You can use master.sys.xp_dirtree stored procedure to achieve this. This procedure takes following parameters
- Directory - The full path of the directory to be scanned
- depth - Number of how many subfolder levels to traverse. The default of 0 will display all subfolders.
- file - A value to enable a file or folder display. The default of 0 will not display any files.
Please have a look at this[^] or this[^] articles related to this procedure.
Regards,
declare @files table (ID int IDENTITY, FileName varchar(100)) insert into @files execute xp_cmdshell 'dir c:\ /b' select * from @files
Original answer : How to read list of files in folder using T-SQL ...[^]
这篇关于SQL SERVER中的存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文