Return a value from a stored procedure

  • opc.three (4/4/2012)


    The point Craig and I are trying to drive home is: unless you have a requirement that says that a sequence of IDs cannot have any gaps in it, then there is no need for you to maintain your own mechanism to generate new IDs.

    From your write-up this is what I was thinking in terms of a basic data model. Some might call it a master-detail, or header-detail model. It is an example of a one-to-many parent-child relationship.

    In the code below the master table that identifies a file uniquely is called FileMaster. Related to that table in the parent-child relationship is the FileDetail table, which maintains the list of operations a file goes through during it's lifetime. The relevant unique ID when tracking a file's movements is the FileMaster.FileID. FileDetail.FileDetailID is simply a way to uniquely arrive at a single row in the detail table, which would be useful to UI developers or for other query uses, like sorting, since it will be ever-increasing over time.

    USE tempdb

    GO

    IF EXISTS ( SELECT *

    FROM sys.objects

    WHERE object_id = OBJECT_ID(N'dbo.FileDetail')

    AND type IN (N'U') )

    DROP TABLE dbo.FileDetail;

    GO

    IF EXISTS ( SELECT *

    FROM sys.objects

    WHERE object_id = OBJECT_ID(N'dbo.FileMaster')

    AND type IN (N'U') )

    DROP TABLE dbo.FileMaster;

    GO

    CREATE TABLE dbo.FileMaster

    (

    FileID INT NOT NULL

    IDENTITY(1, 1),

    FilePathInitial NVARCHAR(260) NOT NULL,

    CreateDate DATETIME NOT NULL

    CONSTRAINT [df_dbo.FileMaster.CreateDate] DEFAULT (GETDATE()),

    CONSTRAINT [pk_dbo.FileMaster] PRIMARY KEY (FileID)

    );

    GO

    CREATE TABLE dbo.FileDetail

    (

    FileDetailID INT NOT NULL

    IDENTITY(1, 1),

    FileID INT NOT NULL,

    OperationType CHAR(3) NOT NULL, -- CPY=copy, DEL=delete, MOV=Move/Rename, DEC=decrypt etc.

    FilePathOriginal NVARCHAR(260) NOT NULL,

    FilePathFinal NVARCHAR(260) NULL, -- will be null when OperationType = D

    CreateDate DATETIME NOT NULL

    CONSTRAINT [df_dbo.FileDetail.CreateDate] DEFAULT (GETDATE()),

    CONSTRAINT [pk_dbo.FileDetail] PRIMARY KEY (FileDetailID),

    CONSTRAINT [fk_dbo.FileDetail.FileID_dbo.FileMaster.FileID] FOREIGN KEY (FileID) REFERENCES dbo.FileMaster (FileID)

    );

    GO

    DECLARE @FileID INT;

    INSERT INTO dbo.FileMaster

    (

    FilePathInitial

    )

    VALUES (

    N'\\server\drop\files\someFile.pgp'

    );

    SET @FileID = SCOPE_IDENTITY();

    INSERT INTO dbo.FileDetail

    (

    FileID,

    OperationType,

    FilePathOriginal,

    FilePathFinal

    )

    VALUES (

    @FileID,

    'MOV',

    N'\\server\drop\files\someFile.pgp',

    N'\\server\drop\files\someFile_20120404_100322.pgp'

    );

    INSERT INTO dbo.FileDetail

    (

    FileID,

    OperationType,

    FilePathOriginal,

    FilePathFinal

    )

    VALUES (

    @FileID,

    'DEC',

    N'\\server\drop\files\someFile_20120404_100322.pgp',

    N'\\server\drop\files\someFile_20120404_100322.txt'

    );

    INSERT INTO dbo.FileDetail

    (

    FileID,

    OperationType,

    FilePathOriginal,

    FilePathFinal

    )

    VALUES (

    @FileID,

    'DEL',

    N'\\server\drop\files\someFile_20120404_100322.txt',

    NULL

    );

    -- show a file's lifespan

    SELECT fd.FileDetailID,

    fd.FileID,

    fd.OperationType,

    fd.FilePathOriginal,

    fd.FilePathFinal,

    fd.CreateDate

    FROM dbo.FileMaster fm

    JOIN dbo.FileDetail fd ON fm.FileID = fd.FileID

    WHERE fm.FileID = 1

    ORDER BY fd.FileDetailID;

    GO

    A clever person solves a problem. A wise person avoids it. ~ Einstein
    select cast (0x5365616E204465596F756E67 as varchar(128))

  • opc.three (4/4/2012)


    The point Craig and I are trying to drive home is: unless you have a requirement that says that a sequence of IDs cannot have any gaps in it, then there is no need for you to maintain your own mechanism to generate new IDs.

    From your write-up this is what I was thinking in terms of a basic data model. Some might call it a master-detail, or header-detail model. It is an example of a one-to-many parent-child relationship.

    In the code below the master table that identifies a file uniquely is called FileMaster. Related to that table in the parent-child relationship is the FileDetail table, which maintains the list of operations a file goes through during it's lifetime. The relevant unique ID when tracking a file's movements is the FileMaster.FileID. FileDetail.FileDetailID is simply a way to uniquely arrive at a single row in the detail table, which would be useful to UI developers or for other query uses, like sorting, since it will be ever-increasing over time.

    USE tempdb

    GO

    IF EXISTS ( SELECT *

    FROM sys.objects

    WHERE object_id = OBJECT_ID(N'dbo.FileDetail')

    AND type IN (N'U') )

    DROP TABLE dbo.FileDetail;

    GO

    IF EXISTS ( SELECT *

    FROM sys.objects

    WHERE object_id = OBJECT_ID(N'dbo.FileMaster')

    AND type IN (N'U') )

    DROP TABLE dbo.FileMaster;

    GO

    CREATE TABLE dbo.FileMaster

    (

    FileID INT NOT NULL

    IDENTITY(1, 1),

    FilePathInitial NVARCHAR(260) NOT NULL,

    CreateDate DATETIME NOT NULL

    CONSTRAINT [df_dbo.FileMaster.CreateDate] DEFAULT (GETDATE()),

    CONSTRAINT [pk_dbo.FileMaster] PRIMARY KEY (FileID)

    );

    GO

    CREATE TABLE dbo.FileDetail

    (

    FileDetailID INT NOT NULL

    IDENTITY(1, 1),

    FileID INT NOT NULL,

    OperationType CHAR(3) NOT NULL, -- CPY=copy, DEL=delete, MOV=Move/Rename, DEC=decrypt etc.

    FilePathOriginal NVARCHAR(260) NOT NULL,

    FilePathFinal NVARCHAR(260) NULL, -- will be null when OperationType = D

    CreateDate DATETIME NOT NULL

    CONSTRAINT [df_dbo.FileDetail.CreateDate] DEFAULT (GETDATE()),

    CONSTRAINT [pk_dbo.FileDetail] PRIMARY KEY (FileDetailID),

    CONSTRAINT [fk_dbo.FileDetail.FileID_dbo.FileMaster.FileID] FOREIGN KEY (FileID) REFERENCES dbo.FileMaster (FileID)

    );

    GO

    DECLARE @FileID INT;

    INSERT INTO dbo.FileMaster

    (

    FilePathInitial

    )

    VALUES (

    N'\\server\drop\files\someFile.pgp'

    );

    SET @FileID = SCOPE_IDENTITY();

    INSERT INTO dbo.FileDetail

    (

    FileID,

    OperationType,

    FilePathOriginal,

    FilePathFinal

    )

    VALUES (

    @FileID,

    'MOV',

    N'\\server\drop\files\someFile.pgp',

    N'\\server\drop\files\someFile_20120404_100322.pgp'

    );

    INSERT INTO dbo.FileDetail

    (

    FileID,

    OperationType,

    FilePathOriginal,

    FilePathFinal

    )

    VALUES (

    @FileID,

    'DEC',

    N'\\server\drop\files\someFile_20120404_100322.pgp',

    N'\\server\drop\files\someFile_20120404_100322.txt'

    );

    INSERT INTO dbo.FileDetail

    (

    FileID,

    OperationType,

    FilePathOriginal,

    FilePathFinal

    )

    VALUES (

    @FileID,

    'DEL',

    N'\\server\drop\files\someFile_20120404_100322.txt',

    NULL

    );

    -- show a file's lifespan

    SELECT fd.FileDetailID,

    fd.FileID,

    fd.OperationType,

    fd.FilePathOriginal,

    fd.FilePathFinal,

    fd.CreateDate

    FROM dbo.FileMaster fm

    JOIN dbo.FileDetail fd ON fm.FileID = fd.FileID

    WHERE fm.FileID = 1

    ORDER BY fd.FileDetailID;

    GO

    A clever person solves a problem. A wise person avoids it. ~ Einstein
    select cast (0x5365616E204465596F756E67 as varchar(128))

  • opc.three (4/4/2012)


    The point Craig and I are trying to drive home is: unless you have a requirement that says that a sequence of IDs cannot have any gaps in it, then there is no need for you to maintain your own mechanism to generate new IDs.

    From your write-up this is what I was thinking in terms of a basic data model. Some might call it a master-detail, or header-detail model. It is an example of a one-to-many parent-child relationship.

    In the code below the master table that identifies a file uniquely is called FileMaster. Related to that table in the parent-child relationship is the FileDetail table, which maintains the list of operations a file goes through during it's lifetime. The relevant unique ID when tracking a file's movements is the FileMaster.FileID. FileDetail.FileDetailID is simply a way to uniquely arrive at a single row in the detail table, which would be useful to UI developers or for other query uses, like sorting, since it will be ever-increasing over time.

    USE tempdb

    GO

    IF EXISTS ( SELECT *

    FROM sys.objects

    WHERE object_id = OBJECT_ID(N'dbo.FileDetail')

    AND type IN (N'U') )

    DROP TABLE dbo.FileDetail;

    GO

    IF EXISTS ( SELECT *

    FROM sys.objects

    WHERE object_id = OBJECT_ID(N'dbo.FileMaster')

    AND type IN (N'U') )

    DROP TABLE dbo.FileMaster;

    GO

    CREATE TABLE dbo.FileMaster

    (

    FileID INT NOT NULL

    IDENTITY(1, 1),

    FilePathInitial NVARCHAR(260) NOT NULL,

    CreateDate DATETIME NOT NULL

    CONSTRAINT [df_dbo.FileMaster.CreateDate] DEFAULT (GETDATE()),

    CONSTRAINT [pk_dbo.FileMaster] PRIMARY KEY (FileID)

    );

    GO

    CREATE TABLE dbo.FileDetail

    (

    FileDetailID INT NOT NULL

    IDENTITY(1, 1),

    FileID INT NOT NULL,

    OperationType CHAR(3) NOT NULL, -- CPY=copy, DEL=delete, MOV=Move/Rename, DEC=decrypt etc.

    FilePathOriginal NVARCHAR(260) NOT NULL,

    FilePathFinal NVARCHAR(260) NULL, -- will be null when OperationType = D

    CreateDate DATETIME NOT NULL

    CONSTRAINT [df_dbo.FileDetail.CreateDate] DEFAULT (GETDATE()),

    CONSTRAINT [pk_dbo.FileDetail] PRIMARY KEY (FileDetailID),

    CONSTRAINT [fk_dbo.FileDetail.FileID_dbo.FileMaster.FileID] FOREIGN KEY (FileID) REFERENCES dbo.FileMaster (FileID)

    );

    GO

    DECLARE @FileID INT;

    INSERT INTO dbo.FileMaster

    (

    FilePathInitial

    )

    VALUES (

    N'\\server\drop\files\someFile.pgp'

    );

    SET @FileID = SCOPE_IDENTITY();

    INSERT INTO dbo.FileDetail

    (

    FileID,

    OperationType,

    FilePathOriginal,

    FilePathFinal

    )

    VALUES (

    @FileID,

    'MOV',

    N'\\server\drop\files\someFile.pgp',

    N'\\server\drop\files\someFile_20120404_100322.pgp'

    );

    INSERT INTO dbo.FileDetail

    (

    FileID,

    OperationType,

    FilePathOriginal,

    FilePathFinal

    )

    VALUES (

    @FileID,

    'DEC',

    N'\\server\drop\files\someFile_20120404_100322.pgp',

    N'\\server\drop\files\someFile_20120404_100322.txt'

    );

    INSERT INTO dbo.FileDetail

    (

    FileID,

    OperationType,

    FilePathOriginal,

    FilePathFinal

    )

    VALUES (

    @FileID,

    'DEL',

    N'\\server\drop\files\someFile_20120404_100322.txt',

    NULL

    );

    -- show a file's lifespan

    SELECT fd.FileDetailID,

    fd.FileID,

    fd.OperationType,

    fd.FilePathOriginal,

    fd.FilePathFinal,

    fd.CreateDate

    FROM dbo.FileMaster fm

    JOIN dbo.FileDetail fd ON fm.FileID = fd.FileID

    WHERE fm.FileID = 1

    ORDER BY fd.FileDetailID;

    GO

    A clever person solves a problem. A wise person avoids it. ~ Einstein
    select cast (0x5365616E204465596F756E67 as varchar(128))

  • Opc.three and Craig thank you very much for you advice and efforts in sharing your knowledge. I do very much value your replies. Thanks.

    A clever person solves a problem. A wise person avoids it. ~ Einstein
    select cast (0x5365616E204465596F756E67 as varchar(128))

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply