August 20, 2008 at 1:41 pm
Basicly my reuslts are 2 records like this
|__|id|RowOrderNumber|ASCCode|ME|
-------------------------------------
1 1 1 A1 M1
2 2 2 A4 M4
What I need to do is take these results and have a record say
1,1,1,A5,M5
basicly need the results added together...
drop Table ##Code
Create Table ##Code
(
[id] [int] IDENTITY(1,1) NOT NULL,
[RowOrderNumber] [int] NULL,
[ASCCode] [varchar](10) NOT NULL,
[ME] [varchar](50) NULL
)
begin
Insert into ##Code (ASCCode,ME)
SELECT ASCcode ,ME From spWebGetProductAttributes as s
JOIN productlist as p ON p.productid = s.productid
WHERE p.siteid = 301013575 and p.producttype = '6' and p.version ='2008' and p.status ='1'
if exists (select top 1 * from ##code where id = '1')
begin
Update ##code
set RowOrderNumber = '1' where id = '1'
end
if exists (select * from ##code where id = '2')
begin
Update ##code
set RowOrderNumber = '2' where id ='2'
end
end
August 20, 2008 at 4:08 pm
I finally figured it out myself 🙂
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER Proc [dbo].[spWebGetBundleCode]
(
-- input variables
@siteid int,
-- output variables
@SPCodeA varchar(5) output
)
as
Create Table #code
(
[id] [int] IDENTITY(1,1)not NULL,
[RowOrderNumber] [int] NULL,
[ASCCode] [varchar](10) NULL,
[ME] [varchar](50) NULL,
[ProductID] [int] NULL,
[a1] varchar(2),
[m1] Varchar(2)
)
begin
set @SPCodeA = ''
if exists ( select bundleid from bundlelist as b WHERE b.siteid = @siteid and b.bundletype = '6' and b.version ='2008' and b.status ='1' )
begin
SELECT @SPCodeA = ASCcode + '-' + ME From spWebGetBundleAttributes as s
JOIN bundlelist as b
ON B.bundleid = s.bundleid
WHERE b.siteid = @siteid and b.bundletype = '6' and b.version ='2008' and b.status ='1'
end
else
if exists ( select productid from productlist as p WHERE p.siteid = @siteid and p.producttype = '6' and p.version ='2008' and p.status ='1' )
begin
Insert into code (ProductID,ASCCode,ME)
SELECT s.ProductID,right(ASCcode,1) ,right(ME,1) From spWebGetProductAttributes as s
JOIN productlist as p ON p.productid = s.productid
WHERE p.siteid = @siteid and p.producttype = '6' and p.version ='2008' and p.status ='1'
if exists (select top 1 * from #code where id = '1')
begin
Update #code
set RowOrderNumber = '1' where id = '1'
Update #code set A1 = 'a'
Update #code set M1 = 'm'
end
if exists (select * from #code where id = '2')
begin
Update #code
set RowOrderNumber = '2' where id ='2'
end
declare @m1 Varchar(2)
declare @m2 Varchar(2)
declare @n1 varchar(2)
declare @n2 varchar(2)
set @m1 = (select top 1 a1 from #code)
set @m2 = (select + sum(cast(asccode as int)) from #code)
set @n1 = (select top 1 m1 from #code)
set @n2 = (select + sum(cast(me as int)) from #code)
select @SPCodeA = @m1+@m2+'-'+@n1+@n2
end
end
return
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply