Please help - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Please help

Author

Topic

raky
Aged Yak Warrior

767 Posts

Posted - 2008-04-06 : 04:23:14

In my stored procedure i getting out put like
this
cell details
7
150
1

celldetails
8
120
5

But i want out put this

7 150 1
8 120

raky
Aged Yak Warrior

767 Posts

Posted - 2008-04-06 : 04:24:27

In my stored procedure i getting out put like
this
cell details
7
150
1

celldetails
8
120
5

But i want out put this
cell details
7 150 1
8 120 5

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-06 : 05:28:44

Post structures of your tables involved with some sample data for quick & accurate soln.

raky
Aged Yak Warrior

767 Posts

Posted - 2008-04-06 : 05:43:07

Hi,

--EXEC usp_OrderEntryLineItem_InventorySearch_List_New 6, '7||~C~||750||~C~||1||~R~||8||~C~||850||~C~||2'
ALTER PROCEDURE [dbo].[usp_OrderEntryLineItem_InventorySearch_List_New]
(
@company_Id NUMERIC(18,0),
@selectedWarehouseDetails VARCHAR(MAX)
)
AS
BEGIN
DECLARE @WarehouseDetails TABLE
(
inventory_wh_lot_id NUMERIC(18,0) ,
RequiredQuantitySelected VARCHAR(255) null,
NumberOfUnitsSelected VARCHAR(255) null
)
-- TO GET ROW DETAILS
DECLARE @RowDetails TABLE (RowDetails VARCHAR(MAX))
DECLARE @RowDetailsXML XML;
SELECT @RowDetailsXML = CAST('<d>' + REPLACE(@selectedWarehouseDetails, '||~R~||', '</d><d>') + '</d>' AS XML);

INSERT INTO @RowDetails(RowDetails)
SELECT T.split.value('.', 'nvarchar(max)') AS data
FROM @RowDetailsXML.nodes('/d') T(split)

-- TO GET COLUMN DETAILS
DECLARE @CellDetails TABLE (CellDetails VARCHAR(MAX))
DECLARE @CellDetailsXML XML;

DECLARE @CursorRowDetails VARCHAR(MAX)

DECLARE RowDetails_Cursor CURSOR FOR
SELECT RowDetails FROM @RowDetails
OPEN RowDetails_Cursor
FETCH NEXT FROM RowDetails_Cursor INTO @CursorRowDetails
WHILE @@FETCH_STATUS = 0
BEGIN
DELETE FROM @CellDetails
SELECT @CellDetailsXML = CAST('<d>' + REPLACE(@CursorRowDetails, '||~C~||', '</d><d>') + '</d>' AS XML);

INSERT INTO @CellDetails(CellDetails)
SELECT T.split.value('.', 'nvarchar(max)') AS data
FROM @CellDetailsXML.nodes('/d') T(split)

-- To Add Warehouse Details
SELECT * FROM @CellDetails

FETCH NEXT FROM RowDetails_Cursor INTO @CursorRowDetails
END
CLOSE RowDetails_Cursor
DEALLOCATE RowDetails_Cursor

Select * from @WarehouseDetails

I want output like this

inventory_wh_lot_id RequiredQuantitySelected NumberOfUnitsSelected
7 750 1
8 850 2

Thanks inadvance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-06 : 12:59:32

Change like this and try:-

--EXEC usp_OrderEntryLineItem_InventorySearch_List_New 6, '7||~C~||750||~C~||1||~R~||8||~C~||850||~C~||2'
ALTER PROCEDURE [dbo].[usp_OrderEntryLineItem_InventorySearch_List_New]
(
@company_Id NUMERIC(18,0),
@selectedWarehouseDetails VARCHAR(MAX)
)
AS
BEGIN
DECLARE @WarehouseDetails TABLE 
(
inventory_wh_lot_id NUMERIC(18,0) , 
RequiredQuantitySelected VARCHAR(255) null, 
NumberOfUnitsSelected VARCHAR(255) null
)
DECLARE @Value varchar(1000),@Residue varchar(8000)

SET @selectedWarehouseDetails=REPLACE(REPLACE(@selectedWarehouseDetails,'||~C~||','.'),'||~R~||',',')

WHILE @selectedWarehouseDetails IS NOT NULL
BEGIN
 SELECT  @Value=CASE WHEN CHARINDEX(',',@selectedWarehouseDetails)>0
                     THEN LEFT(@selectedWarehouseDetails,CHARINDEX(',',@selectedWarehouseDetails)-1)-1)
 ELSE @selectedWarehouseDetails
END,
@Residue=CASE WHEN CHARINDEX(',',@selectedWarehouseDetails)>0
                     THEN SUBSTRING(@selectedWarehouseDetails,CHARINDEX(',',@selectedWarehouseDetails)+1,LEN(@selectedWarehouseDetails)-CHARINDEX(',',@selectedWarehouseDetails))
 ELSE NULL
END

INSERT INTO @WarehouseDetails
SELECT PARSENAME(@Value,3),PARSENAME(@Value,2),PARSENAME(@Value,1)
END

SELECT * FROM @WarehouseDetails

END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources