SQLTeam.com | Weblogs | Forums

Stored procedure


#1

Hi,
I have a table
CREATE TABLE [dbo].[OP_Transaction](

[prod_id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,

[productid] [numeric](18, 0) NULL,

[productname] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[OS_GS_IdNo] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[OF_FeeHeadcode] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[accountno] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

CONSTRAINT [PK_OP_Transaction] PRIMARY KEY CLUSTERED

(

[prod_id] ASC

)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
table with sample values
prod_id productid productname OS_GS_IdNo OF_FeeHeadcode accountno

1
1
Product 1
1,2,3,4,5
450, 451
A/C no 12345678

2
2
Product 2
1,2,3,4,5
452,453
A/C no 78965412

3
3
Product 3
6,7,8,9,10
450,451
A/C no 98745632

4
4
product 4
6,7,8,9,10
452,453, 454
A/C no 478521693

From the above table
OS_GS_IdNo = 8

              i have string[] headcodes and string[] feeamt values .

              headcodes[0] =  451

              headcodes[1] = 453

              headcodes[2] = 454


              feeamt[0] = 1500

              feeamt [1] = 800

              feeamt [2] = 1200

               

               if headcode value is 451 then its corresponding 

feeamt value (1500) and accountno and product name (from table)should
come

               and if headcode values is 453 and 454 , its  feeamt 

value should sum up (800+ 1200) (as it comes under same accountno) and
get total feeamount and get account no and product name from the table.

               How to write query for that? Could anyone please help to solve this

#2

Can you convert the data lines to actual INSERT statements:
INSERT INTO [dbo].[OP_Transaction] VALUES(
1
1
Product 1
1,2,3,4,5
450, 451
A/C no 12345678
)

And show what result you want to see from the sample data you posted?