April 3, 2012 at 10:08 am
Quick and probably easy answer for most of you. I want to created a stored procedure that simply is run and returns the max ID value from a table. I have tried output and return but can't seem to get it to work.
The below script doesn't work for some reason and get the following error:
CREATE PROC sp_GetMaxID (@MaxID INT OUTPUT)
AS
SELECT @MaxID = max(ID) FROM tblFileWatchMaxID
Msg 201, Level 16, State 4, Procedure sp_GetMaxID, Line 0
Procedure or function 'sp_GetMaxID' expects parameter '@MaxID', which was not supplied.
A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
April 3, 2012 at 10:38 am
This seems to work and has the added benefit of incrementing the ID by one which will work nicely with my next insert statement:
CREATE PROCEDURE [dbo].[spGenerateID]
--(@id int output)
AS
BEGIN
DECLARE @dynsql NVARCHAR(1000)
DECLARE @id int
SET @dynsql = 'select @id =isnull(max([ID]),0)+1 from [tblFileWatchMaxID];'
EXEC sp_executesql @dynsql, N'@id int output', @id OUTPUT
select @id
END
A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
April 3, 2012 at 11:08 am
TeraByteMe (4/3/2012)
Quick and probably easy answer for most of you. I want to created a stored procedure that simply is run and returns the max ID value from a table. I have tried output and return but can't seem to get it to work.The below script doesn't work for some reason and get the following error:
CREATE PROC sp_GetMaxID (@MaxID INT OUTPUT)
AS
SELECT @MaxID = max(ID) FROM tblFileWatchMaxID
Msg 201, Level 16, State 4, Procedure sp_GetMaxID, Line 0
Procedure or function 'sp_GetMaxID' expects parameter '@MaxID', which was not supplied.
When calling a proc with an output parameter you must provide a variable for SQL Server to use to place the outputted value. Using your proc example it would look like this:
DECLARE @MaxID_output_variable INT;
EXEC sp_GetMaxID
@MaxID = @MaxID_output_variable OUTPUT;
SELECT @MaxID_output_variable AS MaxID_output_variable;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 3, 2012 at 11:09 am
TeraByteMe (4/3/2012)
This seems to work and has the added benefit of incrementing the ID by one which will work nicely with my next insert statement:CREATE PROCEDURE [dbo].[spGenerateID]
--(@id int output)
AS
BEGIN
DECLARE @dynsql NVARCHAR(1000)
DECLARE @id int
SET @dynsql = 'select @id =isnull(max([ID]),0)+1 from [tblFileWatchMaxID];'
EXEC sp_executesql @dynsql, N'@id int output', @id OUTPUT
select @id
END
May I ask why you are maintaining your own ID values and chose not to use an IDENTITY column in your table?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 3, 2012 at 3:34 pm
Thanks opc.three. To answer your question, it isn't an identity column, it is an ID that is meant to track a file through-out it's journey on the network. So there will be many possible records in the table with the same ID. The only time a new ID is created is when an new file comes along.
A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
April 3, 2012 at 4:13 pm
Fair enough, but an IDENTITY would still be the preferred way to generate a new file's ID provided gaps in the sequence were tolerable. Managing you're own ID-generation scheme can prove to be problematic, and re-invents the wheel in a sense.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 3, 2012 at 5:08 pm
Preferred in your mind but in your mind you don't know what my table structure and business requirements are.
A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
April 3, 2012 at 5:15 pm
TeraByteMe (4/3/2012)
Preferred in your mind but in your mind you don't know what my table structure and business requirements are.
Very true, just having a discussion here 🙂
Preferred in the sense of using a Best Practice, and not re-inventing the proverbial wheel. I did mention not tolerating gaps in the sequence which is the only reason I can think of at the moment that would justify not using an IDENTITY.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 3, 2012 at 6:15 pm
TeraByte, to answer your question and to try to ease future pain of concurrent attempts to insert, which is what Orlando was leading to, can I recommend you take steps to prevent yourself some pain.
Below find the code to generate a test table scenario and then two procs. The first, GetNextID, does not require any dynamic SQL to get the OUTPUT to function. Test code follows it.
The second, GenerateNewRecordNewID, creates a record while simultaneously incrementing the internal IDs stored in the table. Because this happens simultaneously and the TABLOCKX precludes anyone else from doing a concurrent read/insert to duplicate an ID, it will protect you from accidental concurrency issues. I built it to present it for you so you can see what I'm talking about. From there, you'll have to decide if it's worth it.
Without it, it's quite possible two people trying to generate a new record may end up duplicating the ID. It also has the benefit of one less 'round trip' from the code.
CREATE TABLE TestTable
(IDFld INT NOT NULL,
SomeValue VARCHAR(20) DEFAULT ('ABC'))
INSERT INTO TestTable(IDFld) VALUES ( 1)
INSERT INTO TestTable(IDFld) VALUES ( 1)
INSERT INTO TestTable(IDFld) VALUES ( 1)
INSERT INTO TestTable(IDFld) VALUES ( 1)
INSERT INTO TestTable(IDFld) VALUES ( 2)
INSERT INTO TestTable(IDFld) VALUES ( 2)
INSERT INTO TestTable(IDFld) VALUES ( 2)
-- DROP PROC GetNextID
CREATE PROC GetNextID ( @ID INT OUTPUT)
AS
SET @ID =(SELECT
MAX( IDFld)
FROM
TestTable
)
GO
DECLARE @NewID INT
EXEC GetNextID @NewID OUTPUT
PRINT @NewID
GO
--DROP PROC GenerateNewRecordNewID
--GO
CREATE PROC GenerateNewRecordNewID
( @SomeValue VARCHAR(20),
@ID INT OUTPUT)
AS
DECLARE @MidTable TABLE (mt_IDFld INT)
INSERT INTO TestTable
OUTPUT
INSERTED.IDFld
INTO @MidTable
SELECT
MAX( IDFld) + 1 AS IDFld,
@SomeValue
FROM
TestTable (TABLOCKX)
SET @ID = (SELECT TOP 1 mt_IDFld FROM @MidTable)
GO
DECLARE @NewID INT
EXEC GenerateNewRecordNewID @SomeValue = 'DEF', @ID = @NewID OUTPUT
PRINT @NewID
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 3, 2012 at 9:16 pm
The ID column will never be changed. It is a chronological record of a file moving from one place to another. The only purpose outside that for the table is for reporting purposes. Oh, and yeah. Often reporting tables are de-normalized and don't have to fit into the perfect world of normalization. There is no concern about update or insert issues, there will never be a delete. This is a static table other then new records being inserted. All new records are guaranteed to be unique by the timestamp. Truth being the table is supposed to have multiple records with the same ID (File Identifier).
There is no concern about duplication of ID's because one process is running a sequential fashion and it will always increment the ID by the last max id of the column.
Incremental seeded ID in my situation doesn't fit with the requirements of the task at hand. Sure I could create an incremental identify(1,1) on a column in the table but the only benefit I can see to doing that is for creating an index cover between the FileID and the IDENTITY ID. That could be a benefit later down the road for reporting purposes. For now my biggest obstacle is how to create a ID (FileID) that will uniquely identify a file that has been created (from an external source and dropped onto our FTP site) and From there I must track each rename, change, and delete of that file alone a multiple of seven possible paths (all depending on it's file type and naming conversion). Actually also folder that it is dropped in (which numbers around 200). The files could come in with any name in actuality at any time. There are a total of 5 different files extensions that could be dropped to these locations. If PGP then they are decrypted and either and save the folder or they could be saved to another directory and the PGP deleted. The hops between directory locations along these paths could be as much as six different directories. So the possibilities are for any one file generated on the FTP site could have as many records in the table as 6x4 = 24 - and that isn't counting all the decryption and renames that could happen to the file in each directory. There are numerous processes that act and transform these files along the path. For any of them they could rename the file, copy the file, open the file and change its contents, or delete the file, and create another file to a different directory. I must put that all together by using C# and SQL stored procedures. I must have a uniquely identifying ID for each original file and all the events that could happen to that file through all processes. There needs to be a path to follow the life of that life (information) as it progresses through all the processes it need to take.
Oh, and I didn't mention this part. There are 54 people that have access to all these numerous directories. Manual processing, renaming, and dropping files to directories, is common place. I would say it is about 20% of the changes that can happen to these files.
A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
April 4, 2012 at 10:12 am
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
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 4, 2012 at 10:25 am
TeraByteMe,
Looking at your original post, I think I know the problem. Modified the code to return max(id) + 1 and it doesn't use any dynamic sql.
ALTER PROC dbo.sp_GetMaxID (@MaxID INT OUTPUT)
AS
BEGIN
SELECT @MaxID = max(ID) + 1 FROM tblFileWatchMaxID;
RETURN @MaxID
END
To test the code, run this:
declare @MyMaxID int;
exec dbo.sp_GetMaxID @MaxID = @MyMaxID OUTPUT;
select @MyMaxID;
As for what you are trying to explain to everyone, please let me know if I am paraphrasing this correctly.
The value of ID relates to one file, and there may be multiple instances of this ID in the table as the file moves around (what ever that means). Each time a new file is added, you need a new ID value, which you determine by getting the current MAX(ID) value and adding 1 to it.
Does that pretty much sum it up?
April 4, 2012 at 10:40 am
Actually, I got this to work with no problem.
ALTER PROC dbo.sp_GetMaxID (@MaxID INT OUTPUT)
AS
BEGIN
SELECT @MaxID = max(ID) + 1 FROM tblFileWatchMaxID;
END
Use the same test code from my post above.
April 4, 2012 at 3:53 pm
Thanks Lynn for the code. Yes, I think you paraphrased it pretty well. The moving around of the file is the file and it's data being processed and moved to different directory. An analogy would be a TCP/IP packet along its journey across different points of a network. Each "hop" being a point where the file is processed and forwarded on. The ID of the file is captured at creation (when it comes into the FTP site). I want a identifying number for that initial file as I record each "hop" along its jouney. In short like this (many more data in columns will be capture):
ID FileName Directory Event
2001 Somefile_Renamed.txt \\server\dir Renamed
2001 Somefile.txt \\server\dir Deleted
2001 Somefile.txt \\server2\dir2 Created
I went ahead a created a Master type table that will capture the initial file creation on the FTP site. This table will created the ID via IDENTITY(1,1). I then will populate the table I depict above from that ID and record each event as it unfolds.
Lynn Pettis (4/4/2012)
Actually, I got this to work with no problem.
ALTER PROC dbo.sp_GetMaxID (@MaxID INT OUTPUT)
AS
BEGIN
SELECT @MaxID = max(ID) + 1 FROM tblFileWatchMaxID;
END
Use the same test code from my post above.
A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
April 4, 2012 at 9:28 pm
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 tempdbGO
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))
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply