SCOPE_IDENTITY Causing Major Depression
-
I have read many posts (from many sites) concerning the use of scope_identity() in stored procedures to return the "just inserted" value of an identity column. Well, I have tried the following code:
CREATE PROCEDURE spInsert
@TXUSERID varchar(32) -- INPUT (The session.userid from website)
AS
DECLARE @SQL varchar(1024) -- DECLARE VAR TO HOLD SQL STATEMENT
SELECT @SQL = 'INSERT INTO TX (TXUSERID) VALUES (' + CHAR(39) + @TXUSERID + CHAR(39) + ')'
-- INSERT TXUSERID INTO TABLE TX
SELECT @SQL = @SQL + ' SELECT SCOPE_IDENTITY() AS MYID'
EXECUTE(@SQL) -- EXECUTE THE SQL INSERT STATEMENT AND SEND ME MY FREAKING MYID
GO
Now, it works GREAT when I am in the SQL Query Analyzer.
I ran the following code in SQL Query Analyzer:
exec spInsert jdshabat
However, it returns nothing when trying to get the value returned to an ASP page. Here's the kicker -- when working in Dreamweaver, I have the option of "Testing" the command to see if it correctly executes the procedure. Well, sure enough, a record gets inserted - BUT NOTHING COMES BACK. Also, when attempting to run the proc from a MS Access 2003 adp, it actually has the balls to ask me for the ID. I am freaking out over this, having put in about 20 meaningless hours.
I am really gettting depressed about not being able to figure this out, after reading literally hundreds of posts - AND Books Online, AND 3 different SQL Server books. If anyone can help me with a working solution, I will honor your Kung Fu for the rest of my career.
-
This was removed by the editor as SPAM
-
Hi,
Try the following links
1) If you want to assign the SCOPE_IDENTITY to a return parameter
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=135608
2) If you simply wanto to SELECT the SCOPE_IDENTITY
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=7311
Hope that helps,
Mauro
-
how about using the OUPUT parameters to obtain the scope_identity() value?
see below for example:
hth
Billy
--- cut here ---
use tempdb
BEGIN TRAN
GO
CREATE TABLE TX1(THE_ID INT NOT NULL IDENTITY(1,1), TX1USERID VARCHAR(100))
GO
CREATE PROCEDURE spInsert (@TX1USERID varchar(32), @inserted_id INT output)
AS
INSERT INTO TX1 (TX1USERID) VALUES (@TX1USERID);
SET @inserted_id = SCOPE_IDENTITY()
GO
DECLARE @the_id INT
EXEC spInsert @TX1USERID = 'your value', @inserted_id = @the_id OUTPUT
SELECT @the_id;
ROLLBACK;
-- cut here --
-
I would also suggest the sp solution - it's brings better security among other things.
This isn't a scope_identity problem more than a 'dynamic SQL vs context' problem. Don't use dynamic SQL unless absolutely necessary! If you're ever wanting to use dynamic SQL, make sure you understand the pros and cons, and how it works!
Worthwhile reading on the subject....
http://www.sommarskog.se/dynamic_sql.html
/Kenneth
-
Thanks for the link on Dynamic sql. All things I'd come across in bits and pieces, but finally an article that puts it all together.
In the case of the stored procedure in question
CREATE PROCEDURE spInsert
@TXUSERID varchar(32) -- INPUT (The session.userid from website)
AS
it's fairly straightforward to safeguard against injection using quotename() OR better still, use sp_executesql as below
SET @sql = 'INSERT INTO TX (TXUSERID) VALUES (@TXUSERID);SELECT @MyID = SCOPE_IDENTITY()'
EXEC sp_executesql @sql, N'TXUSERID varchar(32), @MyID int OUTPUT', @TXUSERID, @MyID OUTPUT
Though in principle it is best practice to avoid dynamic SQL, this is one example of where the risks can be easily avoided. And it's a simple way to do INSERT statements of variables. Is there a better alternative?
Mauro
-
Well Folks,
Thank you all very much for getting as far as I did - which is - creating a stored procedure that will reliably return the ID of the last inserted value
- AS LONG AS YOU HAVE THE FREEDOM TO WRITE "DECLARE" STATEMENTS -
Now, the heart of the matter was NEVER "how to return the value".
I can easily write a proc to return a value.
Now, back to the drawing board. I am trying to write a proc that will work when engaged with VBScript in an ASP page to get the return value.
So far, using an output parameter (like @the_id int output) doesn't return any values to an asp page, using every ADO trick I know (which is why I am writing to a forum for the first time in my seven years of programming).
Second, the dynamic SQL has no problems, since I am working in a totally metadata driven environment. None of my tables can be hard-coded into my final product, all names are derived from master metadata tables, so dynamic SQL is used without a choice.
Third, writing a basic select statement still did not return a value to an ASP page.
AGAIN - I have no trouble getting a return value using Query Analyzer. I never had any trouble getting a return value using Query Analyzer.
So, who's got the real skills? Who can solve this one?
If I am raising my hands for help, I am guessing it's not going to be a 1 minute solution unless you are one amazing programmer.
BTW, I am not about to code "declare" statements into my web pages just so they can hit a weakly-designed proc. There MUST be a way to write the proc OR the VBScript to very simply and cleanly obtain the SCOPE_IDENTITY value using one step. I am looking forward to your input.
jds
-
JDS,
I'm not sure the sarcastic tone is appropriate given that quite a few of us have pitched in to help with a variety of solutions. Your problem was exactly the one I had to tackle and this is how I did it in the SP.
CREATE PROCEDURE UpdateAccount (@AcctId int OUTPUT, @SQL nvarchar(2000), @ErrMsg varchar(200) OUTPUT) AS
/* SAMPLE SP SUBMITTED TO SQLSERVERCENTRAL, 9/9/2004 */
DECLARE @NewAcctID int
DECLARE @Error int
DECLARE @RowCount int
DECLARE @rv int
/* ********************** EXECUTE DYNAMIC SQL & RETURN ACCTID ********************** */
--NOW UPDATE THE Account TABLE
IF @AcctID = 0
BEGIN
SET @NewAcctID = 0
--RETRIEVE THE AUTOID OF THE NEW ACCOUNT
SET @SQL = @SQL + '; SELECT @NewAcctID = SCOPE_IDENTITY()'
EXECUTE sp_executesql @SQL, N'@NewAcctID int OUTPUT', @NewAcctID OUTPUT
END
ELSE
EXECUTE sp_executesql @SQL
--SELECT @Error = @@ERROR, @Rowcount = @@ROWCOUNT, @NewAcctID = SCOPE_IDENTITY()
IF @@ERROR <> 0 OR @@ROWCOUNT = 0
BEGIN
SET @ErrMsg = 'Error updating Account table.'
GOTO ENDSP1
END
ELSE IF @AcctID = 0
--ASSIGN THE AUTOID OF THE NEW ACCOUNT
SET @AcctID = @NewAcctID
/* BTW, another alternative is to return @rv = @NewAcctID, but I prefer to use @rv to indicate error
conditions in the SP*/
SET @rv = 1
GOTO FINAL
ENDSP1:
SET @rv = 0
GOTO FINAL
FINAL:
RETURN @rv
GO
/************************* END SP ************************/
The above SP was written for a new ASP.NET project so I won't give you the ASP.NET code extract. Below is an ASP sample I have taken from an older project, which does the same thing with another stored procedure
Set Cmd = CreateObject("ADODB.Command")
cmd.ActiveConnection = objConnection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "NewEvent"
cmd.Parameters.Append cmd.CreateParameter("autoId", adInteger, adParamOutput)
cmd.Parameters.Append cmd.CreateParameter("sql", adVarChar, , 2000, strSQL)
cmd.Parameters.Append cmd.CreateParameter("errormsg", adVarChar, adParamOutput, 200)
cmd.Execute
'If no record created, then NewAcctID = 0
NewAcctID = cmd("autoId")
Hope that helps.
(BTW, Don't see why you had such a problem returning the parameter to the ASP page:confused
-
Mr. Mauro, thank you for your input. I will implement it and test it out. I cannot explain why return values were not being sent to my ASP page. My lack of skills that led to my writing to this forum are being further exposed. I am a disgrace to programming.
jds
-
If you tried a basic select, it most surely did return it's result to the calling client.
So, if the asp page invokes a proc or whatever, and that in return on the server ends up with a SELECT @newIdentValue as newValue, the column newValue will be returned (and as long as @newIdentValue actually contains something, that value will be row 1 of that column).
It is then the responsibility of the caller to retrieve this value from the resultset. I'm by no means any asp coder (don't have a clue whats 'normal' in that world), but I'm sure there are many ways to make a 'simple select' 'not work' all the way back to an asp page...
/Kenneth
-
Here's the solution. . .
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=8100
Make sure to add the code "Set Nocount On" in the stored procedure.
When my proc ran , it apparently returned the "1 record(s) inserted" message which was interpreted by MSAccess and ADO as a "recordset" -- instead of the actual data.
So, rather than try to use the .NextRecordset syntax, I just added a line to the procedure.
Again, thanks for everyone's help.
Thank goodness it could be resolved in SQL Server.
jds
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply