如何从存储过程中提取目录路径 [英] How Do I Extract A Directory Path From Stored Procedure

问题描述

我有一个存储过程，它返回SQL Server实例备份目录的路径。

我需要做的就是提取路径并将其存储在c＃中的局部变量中。 />


存储过程在这里：

I have a stored procedure that returns the path for the backup directory of SQL Server Instances.
All i need to do is to extract the path and store it in local variable in c#.

Stored Procedure is here:

IF OBJECT_ID('tempdb..#instances') is not null DROP TABLE #instances
IF OBJECT_ID('tempdb..#directories') is not null DROP TABLE #directories

CREATE TABLE #instances (Name VARCHAR(255), InstanceName VARCHAR(255))
CREATE TABLE #directories (TypeName VARCHAR(255), DirPathName VARCHAR(255))

DECLARE @regInstanceKey VARCHAR(500), @s VARCHAR(500);
DECLARE @instance VARCHAR(255)       -- current instance
DECLARE @ver VARCHAR(255)            -- MS SQL version
DECLARE @key VARCHAR(255)            -- Registry key on server side
DECLARE @value_name VARCHAR(255)     -- Registry value name
DECLARE @instance_name VARCHAR(255)  -- Key value name for the instance of SQL Server 2000
DECLARE @version INT                 -- SQL Server version (integer)

DECLARE @ic INT
SET @instance_name = ''

INSERT INTO #instances
EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',
                         @key     = 'SOFTWARE\\Microsoft\\Microsoft SQL Server',
                         @vn      = 'InstalledInstances',
                         @s       = @regInstanceKey OUTPUT;

SELECT @ic = COUNT(*) FROM #instances
 WHERE InstanceName = @@servicename
IF( @ic = 1 ) BEGIN
    SELECT @instance = InstanceName FROM #instances
    WHERE InstanceName = @@servicename
  
    SELECT @ver = SUBSTRING(CAST(SERVERPROPERTY('productversion') AS VARCHAR(255)), 1, 2)
    IF(RIGHT(@ver,1) = '.') SET @version = CAST(LEFT(@ver,1) AS INT)
    ELSE SET @version = CAST(@ver AS INT)

    IF  ( @version = 8) BEGIN
        IF (@instance <> 'MSSQLSERVER' ) SET @key = 
            'SOFTWARE\\Microsoft\\Microsoft SQL Server\\' + @instance + '\\MSSQLServer\\'
        ELSE                             SET @key = 'SOFTWARE\\Microsoft\\MSSQLServer\\MSSQLServer\\'
        END
    ELSE IF ( @version > 8) BEGIN
        EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',
                                 @key = 'SOFTWARE\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL',
                                 @vn = @instance,
                                 @value = @instance_name out
        SET @key = 'SOFTWARE\\Microsoft\\Microsoft SQL Server\\' + @instance_name + '\\MSSQLServer'
    END

    SET @value_name = 'DefaultData'
    EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',
                             @k = @key,
                             @vn = @value_name,
                             @s = @regInstanceKey OUTPUT;
    IF @regInstanceKey = @instance_name SET @regInstanceKey = NULL

    INSERT INTO #directories
    VALUES ('DefaultData', @regInstanceKey)
    SET @value_name = 'DefaultLog'

    EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',
                             @k = @key,
                             @vn = @value_name,
                             @s = @regInstanceKey OUTPUT;

    IF @regInstanceKey = @instance_name SET @regInstanceKey = NULL
    INSERT INTO #directories
    VALUES ('DefaultLog', @regInstanceKey)

    SET @value_name = 'BackupDirectory'
    EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',
                             @k = @key,
                             @vn = @value_name,
                             @s = @regInstanceKey OUTPUT;

    INSERT INTO #directories
    VALUES ('BackupDirectory', @regInstanceKey)
    IF @regInstanceKey = @instance_name SET @regInstanceKey = NULL
END

SELECT * FROM #directories
SELECT * From #instances
DROP TABLE #instances
DROP TABLE #directories

从sp中提取路径的代码应该是什么??

谢谢

What should be the code to extract the path from the sp??
Thanks

推荐答案

这两行意味着你要从SQL脚本中选择记录集

These two lines mean you're selecting record sets back from your SQL script

SELECT * FROM #directories
SELECT * From #instances

因此你对命令对象使用 ExecuteNonQuery 的事实有点奇怪。



您可以使用 DataReader
$ b从SQL命令读取 SELECT 中的任何内容$ b

So that fact you using ExecuteNonQuery against your command object is a bit strange.

You can read anything you SELECT from an SQL command by using a DataReader

IDataReader dr = cmd.ExecuteReader();

然后你有 dr.Read（）命令，它将返回 true ，直到它到达记录集的末尾。



您还有 dr.NextResult（），在您用完记录集之前返回true。



这意味着您可以从通过ADO执行的任何SQL语句中访问多个记录集中的所有行。

You then have the dr.Read() command which will return true until it reaches the end of the record set.

You also have dr.NextResult() which returns true until you run out of record sets.

This means you can access all the rows from multiple record sets from any SQL statement executed through ADO.

这篇关于如何从存储过程中提取目录路径的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！