Output Parameters And Select Statements

Hi,





thanks for reading!





Here is my problem: I have a strored procedure that inserts some records





into one table and then selects some records from another table at the end.





The stored procedure takes several parameters, first one of them is marked as





OUTPUT. I'm using it to return an id of the inserted record. The procedure is called from asp.net code with first parameter set as ParameterDirection.InputOutput (tried with just Output as well). Now for the problem: if the the select statement at the end returns 0 records everything works and i my first parameter contains the @@IDENTITY value from the insert statement like it is supposed to.





If the select statement at the end returns 1 or more records my output parameter is not updated at all and contains the same value as before the procedure was run. All the records are inserted correctly.





if i try to return the @@identity as a plain select statement instead of through the parameter


i get System.DBNull.





I hope you can shed some light on this for me. Here is my stored procedure:











CREATE PROCEDURE cwSaveProductInquiry


@inquiryId int OUTPUT,


@libraryName nvarchar(500),


@contactName nvarchar(200),


@address nvarchar(100),


@city nvarchar(50),


@state nvarchar(3),


@zip nvarchar(10),


@phone nvarchar(50),


@email nvarchar(100),


@comment nvarchar(3000),


@productIds nvarchar(2000)





AS





INSERT INTO INQUIRY (LibraryName, ContactName, Address, City, State, Zip, Phone, Email, Comment) VALUES(@libraryName, @contactName, @address, @city, @state, @zip, @phone, @email,@comment)








--i tried including this statement at the end as well but that did not do the





--trick either





select @inquiryId=@@IDENTITY FROM INQUIRY

















set nocount on


declare @separator_position int -- This is used to locate each separator character


declare @objectId varchar(200) -- this holds each array value as it is returned





if(@productIds is not null)


begin


while patindex('%,%' , @productIds) <> 0


begin


select @separator_position = patindex('%,%' , @productIds)


select @objectId= left(@productIds, @separator_position - 1)


INSERT INTO PRODUCT_INQUIRY_LOOKUP (ProductId,InquiryId) VALUES(@objectId, @inquiryId)


select @productIds = stuff(@productIds, 1, @separator_position, '')


end


end


set nocount off





Select Distinct Email from vPRODUCT_CONTACT WHERE ProductId in


(Select ProductId From Product_Inquiry_Lookup Where InquiryId=@inquiryId)





GO