procedure in SQL
-
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[^]