procedure in SQL
-
i got a procedure in SQL Server at the end of the program i write this code SELECT @@IDENTITY COMMIT TRANSACTION INSERT_NOMENCLATURE RETURN the problem that i don't have the column name of SELECT @@IDENTITY how can i manage it to get the value with a column name thanks!
papy-boom wrote:
i don't have the column name of SELECT @@IDENTITY how can i manage it to get the value with a column name
You don't know from which column @@IDENTITY returns the value. It just returns the last inserted identity value in your sesion. It can be any table and any identity column. You can narrow the possibilities using SCOPE_IDENTITY, but you'd still face the same problem. Normally you know where @@IDENTITY comes from in your procedure since it's the previous insert to identity columned table. What's the cause in your case that prevents you identifying the statement (and after that the table and the column)?
The need to optimize rises from a bad design.My articles[^]
-
papy-boom wrote:
i don't have the column name of SELECT @@IDENTITY how can i manage it to get the value with a column name
You don't know from which column @@IDENTITY returns the value. It just returns the last inserted identity value in your sesion. It can be any table and any identity column. You can narrow the possibilities using SCOPE_IDENTITY, but you'd still face the same problem. Normally you know where @@IDENTITY comes from in your procedure since it's the previous insert to identity columned table. What's the cause in your case that prevents you identifying the statement (and after that the table and the column)?
The need to optimize rises from a bad design.My articles[^]
-
papy-boom wrote:
you mean if i replaced by select max from my table it will works?
No, that's not what I mean. Since you didn't post any code or any explanation why do you want to know the column name, I can't say what's the answer for you. Consider that I have a table:
CREATE TABLE Artist (
[Artist#] int NOT NULL IDENTITY(1,1) PRIMARY KEY,
[Name] nvarchar(100) NOT NULL
);Then when I'm executing this piece of statement:
INSERT INTO Artist ([Name]) VALUES (@name);
SET @artistIdentity= @@IDENTITY;Now on the 2'nd line I know that @@IDENTITY returns a value that is assigned to Artist# column in Artist table since that's the previous
INSERTstatement to a table that has an identity column.The need to optimize rises from a bad design.My articles[^]
-
papy-boom wrote:
you mean if i replaced by select max from my table it will works?
No, that's not what I mean. Since you didn't post any code or any explanation why do you want to know the column name, I can't say what's the answer for you. Consider that I have a table:
CREATE TABLE Artist (
[Artist#] int NOT NULL IDENTITY(1,1) PRIMARY KEY,
[Name] nvarchar(100) NOT NULL
);Then when I'm executing this piece of statement:
INSERT INTO Artist ([Name]) VALUES (@name);
SET @artistIdentity= @@IDENTITY;Now on the 2'nd line I know that @@IDENTITY returns a value that is assigned to Artist# column in Artist table since that's the previous
INSERTstatement to a table that has an identity column.The need to optimize rises from a bad design.My articles[^]
the problem that is that in code part i have a datareader to read the value returned value and the argument to pass to datareader is the name of the column so if my procedure return me a value without the name of column it's not simple to get the value so i tried to get select @@IDENTITY AS returnedvalue to get the name of column but it doesn't work
-
the problem that is that in code part i have a datareader to read the value returned value and the argument to pass to datareader is the name of the column so if my procedure return me a value without the name of column it's not simple to get the value so i tried to get select @@IDENTITY AS returnedvalue to get the name of column but it doesn't work
papy-boom wrote:
tried to get select @@IDENTITY AS returnedvalue to get the name of column but it doesn't work
No, because it only returns the value.
papy-boom wrote:
i have a datareader to read the value returned value and the argument to pass to datareader is the name of the column so if my procedure return me a value without the name of column it's not simple to get the value
Now I don't follow the logic. You cannot ask the last added value for a single column using @@IDENTITY. It doesn't work that way. If you need to know what's the last added identity value to a specific table, you can select MAX from the identity column. But I don't see the point why you should do this since the value of an identity column is normally irrelevant. Refer to: http://en.wikipedia.org/wiki/Surrogate_key[^] So the question is, why do you wan't to know the value at all?
The need to optimize rises from a bad design.My articles[^]
-
papy-boom wrote:
tried to get select @@IDENTITY AS returnedvalue to get the name of column but it doesn't work
No, because it only returns the value.
papy-boom wrote:
i have a datareader to read the value returned value and the argument to pass to datareader is the name of the column so if my procedure return me a value without the name of column it's not simple to get the value
Now I don't follow the logic. You cannot ask the last added value for a single column using @@IDENTITY. It doesn't work that way. If you need to know what's the last added identity value to a specific table, you can select MAX from the identity column. But I don't see the point why you should do this since the value of an identity column is normally irrelevant. Refer to: http://en.wikipedia.org/wiki/Surrogate_key[^] So the question is, why do you wan't to know the value at all?
The need to optimize rises from a bad design.My articles[^]
when i excute my procedure In SQL i have the value but in the name of column i have (no column name) i write a script in C# to get the value with data reader but the method used in datareder is GetValueParam(Command,columName) the procedure dosen't return a name for this column i tried to give a name to returned column so i modify it with COMMIT TRANSACTION INSERT_Artist RETURN SELECT max(Artist#) AS ArtistId from Artist LBL_ERROR: ROLLBACK TRANSACTION INSERT_Artist but i have the same problem (no column name) for returned value i need the column name to read it with a datareader without using another transaction excuse my ignorance but i'm really dump in SQL Server
-
i got a procedure in SQL Server at the end of the program i write this code SELECT @@IDENTITY COMMIT TRANSACTION INSERT_NOMENCLATURE RETURN the problem that i don't have the column name of SELECT @@IDENTITY how can i manage it to get the value with a column name thanks!
Try
Select @@Identity IDNever underestimate the power of human stupidity RAH
-
when i excute my procedure In SQL i have the value but in the name of column i have (no column name) i write a script in C# to get the value with data reader but the method used in datareder is GetValueParam(Command,columName) the procedure dosen't return a name for this column i tried to give a name to returned column so i modify it with COMMIT TRANSACTION INSERT_Artist RETURN SELECT max(Artist#) AS ArtistId from Artist LBL_ERROR: ROLLBACK TRANSACTION INSERT_Artist but i have the same problem (no column name) for returned value i need the column name to read it with a datareader without using another transaction excuse my ignorance but i'm really dump in SQL Server
papy-boom wrote:
the method used in datareder is GetValueParam(Command,columName) the procedure dosen't return a name for this column
Okay, now I understand your problem. You can give the column a name or rename a column in a result set using AS, like:
SELECT @@IDENTITY AS SomeColumnName
The need to optimize rises from a bad design.My articles[^]
-
Try
Select @@Identity IDNever underestimate the power of human stupidity RAH
-
papy-boom wrote:
the method used in datareder is GetValueParam(Command,columName) the procedure dosen't return a name for this column
Okay, now I understand your problem. You can give the column a name or rename a column in a result set using AS, like:
SELECT @@IDENTITY AS SomeColumnName
The need to optimize rises from a bad design.My articles[^]