scan records using stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

scan records using stored procedure

Author

Topic

tatasmove
Starting Member

4 Posts

Posted - 2008-07-14 : 13:54:04

Hi SQL experts,

Im new in creating a stored procedure. I need to create an income report using stored procedure, my main concern is I dont know how to scan a record using stored procedure, the reason of doing this is I want to validate the existence of the items rendered to customers, if the item is already exists it will not insert instead i will get the qty for rendered item and the amount rendered then, from that I can get the sum of the qty rendered and the amount.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-14 : 14:07:15

just use

IF EXISTS(SELECT 1 FROM YourTable WHERE PKCol=@YourParam)
BEGIN
 SELECT @Qty=QTY,@Amt=Amt
 FROM yOurTable
 WHERE PK =@Param
END
ELSE
BEGIN
INSERT INTO YourTable
SELECT values....
END

tatasmove
Starting Member

4 Posts

Posted - 2008-07-14 : 14:16:06

Sample output:

itemdesc QTY AMOUNT
CARDEPINE 10MG/10ML AMPULE 5 500.00
CARDEPINE 2MG/2ML AMPULE 10 1,000.00
CARDIAC MONITOR 7 700.00

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-14 : 14:23:47

quote:
Originally posted by tatasmove

Sample output:

itemdesc QTY AMOUNT
CARDEPINE 10MG/10ML AMPULE 5 500.00
CARDEPINE 2MG/2ML AMPULE 10 1,000.00
CARDIAC MONITOR 7 700.00

CREATE PROC GetYourData
@itemdesc varchar(100),
... other params
@Qty int=0 OUTPUT,
@Amount decimal(10,2)=0.00 OUTPUT
AS

IF EXISTS(SELECT 1 FROM YourTable WHERE itemdesc =@itemdesc)
BEGIN
 SELECT @Qty=QTY,@Amount=AMOUNT
 FROM yOurTable
 WHERE itemdesc =@itemdesc
END
ELSE
BEGIN
INSERT INTO YourTable
SELECT @itemdesc,.. other values
END
GO

and you will invoke it like

DECLARE @rQty int, @rAmt decimal(10,2)

EXEC GetYourData value1,...,@rQty,@rAmt

SELECT @rQty,@rAmt will give back the values

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources