solarisinfosys
asked on
Best way to use a procedure/function in mssql server when you have to insert and return a row
Hi,
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.
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 ?
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.
USE [KingsBayY]
GO
/****** Object: UserDefinedFunction [dbo].[test_fn_transaction_search] Script Date: 01/30/2015 11:57:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE test_fn_transaction_insert_log @receipt varchar(25), @amount varchar(25), @stdt varchar(25),
@enddt varchar(25)
AS
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,
tr.Voided,it.ItemLookupCode, tr.Comment
from [Transaction] tr, TransactionEntry trent, Item it, Customer c
where trent.TransactionID = tr.ID
and trent.ItemID = it.ID
and c.ID=tr.CustomerID
and tr.ReceiptNumber=@receipt
--condition to ensure no duplicates are entered and [TransactionLog].ID not in (select ID from [TransactionLog] where [TransactionLog].ID=tr.ID)
GO
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 ?
ASKER
how is the above answer different from the procedure i wrote? it does not return anything.
Use Output parameter like
CREATE PROCEDURE Myproc
@parm varchar(10),
**@parm1OUT varchar(30) OUTPUT**,
**@parm2OUT varchar(30) OUTPUT**
AS
SELECT @parm1OUT='parm 1' + @parm
SELECT @parm2OUT='parm 2' + @parm
GO
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @parmIN VARCHAR(10)
DECLARE @parmRET1 VARCHAR(30)
DECLARE @parmRET2 VARCHAR(30)
SET @parmIN=' returned'
SET @SQLString=N'EXEC Myproc @parm,
@parm1OUT OUTPUT, @parm2OUT OUTPUT'
SET @ParmDefinition=N'@parm varchar(10),
@parm1OUT varchar(30) OUTPUT,
@parm2OUT varchar(30) OUTPUT'
EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@parm=@parmIN,
@parm1OUT=@parmRET1 OUTPUT,@parm2OUT=@parmRET2 OUTPUT
SELECT @parmRET1 AS "parameter 1", @parmRET2 AS "parameter 2"
GO
DROP PROCEDURE Myproc
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can use SP to do insert. Here is one example.
Open in new window