I am primarily a Java developer with some level of oracle DB experience. I am having to use MSSQL server 2008 R2 for one project. I took a quick tutorial of mssql server on udemy and started creating functions and procedures.
The problem is,
a) i need to pass a few values to a procedure or a function and insert them in a table and return.
i have read online that functions cannot be used for inserting into existing tables .. rather they only insert into temp tables.. is this true ? can you not insert into a table and return the row inserted in a function on MSSQL server? I used the below procedure anyways.
/****** Object: UserDefinedFunction [dbo].[test_fn_transaction_search] Script Date: 01/30/2015 11:57:44 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
alter PROCEDURE test_fn_transaction_insert_log @receipt varchar(25), @amount varchar(25), @stdt varchar(25),
SET NOCOUNT ON
INSERT INTO [TransactionLog] ( TimeStamp, CashierID, Total,RecurringStartDate,
RecurringStopDate, CustomerID, ReceiptNumber, Voided, Attendee, ItemLookupCode)
SELECT CURRENT_TIMESTAMP AS [TimeStamp], tr.CashierID, @amount, @stdt,@enddt,c.ID,tr.ReceiptNumber,
from [Transaction] tr, TransactionEntry trent, Item it, Customer c
where trent.TransactionID = tr.ID
and trent.ItemID = it.ID
--condition to ensure no duplicates are entered and [TransactionLog].ID not in (select ID from [TransactionLog] where [TransactionLog].ID=tr.ID)
b) in the above procedure, i need to return the row just inserted. how can i do this? or atleast get a return code?
c) is this even the best way of writing this procedure for the requirement? can functions be used ?