CODEWITHSUNDEEP
sqlsql-server
will
will

Reputation: 1407

SQL Server - compound statements

I'm working on a project in C#, and am executing this statement, but not getting back anything (using a datatable to get data back)

string SQL = 
  "DECLARE @NewID int; SELECT TOP 1 @NewID = [ItemID] + 1 FROM [GroupItems] ORDER BY [ItemID] DESC; " + 
  "INSERT INTO [GroupItems] ([InstanceID], [ItemID], [GroupID], [ItemName], [DisplayName], [Units], [Updated]) " +
  "VALUES(@instanceID, @NewID, @groupID, @name, @display, @units, CURRENT_TIMESTAMP); SELECT * FROM [GroupItems] WHERE [ItemID]=@NewID";

Basically what I want to do is return the row I just created. Any help?

Upvotes: 0

Views: 455

Answers (1)

gbn
gbn

Reputation: 432261

  • You are probably missing information because of the @variables being NULL. How do you set them?
  • Are suppressing errors somehow.
  • Do you get data stored in the table, or just no results?

Anyway, there are easier ways to do this in one line

First off, make the ItemID column an IDENTITY. Otherwise this code will fail at some point because it isn't safe for overlappping calls

Then second, change the code to use the OUTPUT clause. You can do this in one go

INSERT INTO [GroupItems] ([InstanceID], [GroupID], [ItemName], [DisplayName], [Units], [Updated])
OUTOUT INSERTED.*
VALUES(@instanceID, @groupID, @name, @display, @units, CURRENT_TIMESTAMP);

Upvotes: 2

Related Questions