F
Frawls
Hi
I Am am having problems with a stored Procedure that i wrote.
Basically whats happening is that the Stored procedure Runs fine when i
EXECUTE it in SQL Query analyzer.
But when i debug through the application in Visual Studio .NET 2003 the
application an exception when it executes the query.
I believe that the error is related to the itemQty filled in the table
variable @OrderItemQuantity because when i change the int given to the
itemQuantity the error message will display that number.
The error i am getting in . NET (and my browser) is the following:
--------------------------------------------------------------------------------------------------------------------------------------------------------
Syntax error converting the varchar value ''1'' to a column of data
type int.
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Syntax error
converting the varchar value ''1'' to a column of data type int.
Source Error:
Line 109: catch(Exception oException)
Line 110: {
Line 111: throw oException;
Line 112: }
Line 113: finally
---------------------------------------------------------------------------------------------------------------------------------------------------------
The stored Procedure is as follows:
---------------------------------------------------------------------------------------------------------------------------------------------------------
/********************************************************************************************************
* 0 variables from the code
********************************************************************************************************/
@UserID int,
@CartItems varchar (1000),
@CartItemQuantities varchar (1000),
@DeliveryDirections varchar (3000),
@OrderInstructions varchar (3000),
@OrderIDOutput int OUTPUT
AS
/********************************************************************************************************
* 1 sproc variables
********************************************************************************************************/
declare @SQL varchar(5000)
declare @OrderID varchar(1000)
declare @ErrorNumber int
/********************************************************************************************************
* 2 create a temp table that matches the itemid to the quantity
********************************************************************************************************/
declare @OrderItemQuantity table (itemID int, itemQty int)
insert into @OrderItemQuantity
select cast(FS1.Value as int) as 'itemID', cast(FS2.Value as int) as
'itemQty'
from fnsplitter(@CartItems) FS1 join
fnsplitter(@CartItemQuantities) FS2
on FS1.rowNum = FS2.rowNum
/********************************************************************************************************
* 4 create a temp table for calculating the sale prices
********************************************************************************************************/
create table #Tbl_SalePrice (
ItemId int,
ChargePerItem money,
SaleType Varchar(50),
SaleValue Money,
ItemSaleCharge money)
/********************************************************************************************************
* 5 create a temp table for the order details
********************************************************************************************************/
create table #OrderItemList (
ItemId int,
ChargePerItem money,
Quantity int,
ItemTotalCharge money
)
BEGIN TRAN -- begin the tran--------------------
/********************************************************************************************************
* 6 popluate the temp table with the itemids & charge per item
********************************************************************************************************/
set @SQL = 'insert into #OrderItemList (ItemId, ChargePerItem) '
set @SQL = @SQL + 'select '
set @SQL = @SQL + '[IF].itemId, '
set @SQL = @SQL + 'cast(IF2.FeatureValue as money) '
set @SQL = @SQL + 'from dbo.ectItemFeature [IF] '
set @SQL = @SQL + 'join dbo.ectItemFeature IF2 '
set @SQL = @SQL + 'on [IF].itemId = IF2.itemId '
set @SQL = @SQL + 'where [IF].itemId in ('+@CartItems+') '
set @SQL = @SQL + 'and IF2.FeatureID = 9 '
set @SQL = @SQL + 'and [IF].FeatureID = 9 '
--set @SQL = @SQL + 'order by [IF].itemid '
--print @SQL
exec (@SQL)
-- Check for errors
SET @ErrorNumber= @@ERROR
IF (@ErrorNumber <> 0 )
BEGIN
PRINT 'Error Occured in 6: '
PRINT @@ERROR
ROLLBACK TRAN
RETURN
END
/********************************************************************************************************
* 7 Cursor to apply the sale where applicable
********************************************************************************************************/
--is there a sale??----------------------------------
--i wanted to create a function to apply the sale but couldnt
--do an exec(@SQL1) within a function!
declare @SQL1 varchar(5000)
--popluate the temp table with the itemids & charge per item
set @SQL1 = 'insert into #Tbl_SalePrice (ItemId, ChargePerItem) '
set @SQL1 = @SQL1 + 'select '
set @SQL1 = @SQL1 + '[IF].itemId, '
set @SQL1 = @SQL1 + 'cast(IF2.FeatureValue as money) '
set @SQL1 = @SQL1 + 'from dbo.ectItemFeature [IF] '
set @SQL1 = @SQL1 + 'join dbo.ectItemFeature IF2 '
set @SQL1 = @SQL1 + 'on [IF].itemId = IF2.itemId '
set @SQL1 = @SQL1 + 'where [IF].itemId in ('+@CartItems+') '
set @SQL1 = @SQL1 + 'and IF2.FeatureID = 9 '
set @SQL1 = @SQL1 + 'and [IF].FeatureID = 9 '
--set @SQL = @SQL + 'order by [IF].itemid '
--print @SQL
exec (@SQL1)
--check if theres a saletype and value against the items populate
tempTable
update #Tbl_SalePrice
set #Tbl_SalePrice.SaleType = (
select ST.saleType from dbo.ectSaleType ST
join dbo.ectSale S with (nolock)
on S.saleTypeID = ST.saleTypeID
join dbo.ectSaleItems SI with (nolock)
on S.saleID = SI .saleID
where SI.ItemId = #Tbl_SalePrice.ItemId
)
update #Tbl_SalePrice
set #Tbl_SalePrice.SaleValue = (
select S.saleAmountOff from dbo.ectSale S
join dbo.ectSaleItems SI with (nolock)
on S.saleID = SI .saleID
where SI.ItemId = #Tbl_SalePrice.ItemId
)
--run a cursor over the items to calculate the ItemSaleCharge
declare @ItemID int
declare SaleCalcCursor CURSOR for
select distinct(ItemId) from #Tbl_SalePrice
open SaleCalcCursor
fetch next from SaleCalcCursor
INTO @ItemID
WHILE @@FETCH_STATUS = 0
BEGIN
declare @SaleType varchar(50)
select @SaleType = SaleType From #Tbl_SalePrice WHERE ItemId =
@ItemID
if(@SaleType = 'MONEYOFF')
BEGIN
update #Tbl_SalePrice
set ItemSaleCharge = (ChargePeritem - SaleValue)
END
if(@SaleType = 'PERCENTOFF')
BEGIN
update #Tbl_SalePrice
set ItemSaleCharge = (ChargePeritem - ((ChargePeritem / 100) *
SaleValue))
END
--Can add the rest of the sale types here as the retailer wants
them
FETCH NEXT FROM SaleCalcCursor INTO @ItemID
END
close SaleCalcCursor
deallocate SaleCalcCursor
-- Check for errors
SET @ErrorNumber= @@ERROR
IF (@ErrorNumber <> 0 )
BEGIN
PRINT 'Error Occured in 7: '
PRINT @@ERROR
ROLLBACK TRAN
RETURN
END
/********************************************************************************************************
* 8 Populate the temp table with the quantities
********************************************************************************************************/
update #OrderItemList
set #OrderItemList.Quantity = (
select OIQ.itemQty from @OrderItemQuantity OIQ
where OIQ.ItemId = #OrderItemList.ItemId
)
-- Check for errors
SET @ErrorNumber= @@ERROR
IF (@ErrorNumber <> 0 )
BEGIN
PRINT 'Error Occured 8: '
PRINT @@ERROR
ROLLBACK TRAN
RETURN
END
/********************************************************************************************************
* 9 update the price of the items that are on sale
********************************************************************************************************/
update #OrderItemList
set #OrderItemList.ChargePerItem = (
select TSP.ItemSaleCharge from #Tbl_SalePrice TSP
where TSP.ItemId = #OrderItemList.ItemId
and TSP.ItemSaleCharge IS NOT NULL
)
where #OrderItemList.ItemId in (
select TSP.ItemId from #Tbl_SalePrice TSP
where TSP.ItemSaleCharge IS NOT NULL
)
-- Check for errors
SET @ErrorNumber= @@ERROR
IF (@ErrorNumber <> 0 )
BEGIN
PRINT 'Error Occured 9: '
PRINT @@ERROR
ROLLBACK TRAN
RETURN
END
/********************************************************************************************************
* 10 calculate the total cost for each item * quantity
********************************************************************************************************/
update #OrderItemList
set ItemTotalCharge = (select (ChargePerItem * Quantity))
-- Check for errors
SET @ErrorNumber= @@ERROR
IF (@ErrorNumber <> 0 )
BEGIN
PRINT 'Error Occured in 10: '
PRINT @@ERROR
ROLLBACK TRAN
RETURN
END
/********************************************************************************************************
* 11 insert the order into the Order table.
********************************************************************************************************/
insert into
dbo.omtOrder(customerID,orderDate,firstName,surName,emailAddress,Address1,Address2,Address3,City,County,Country,Phone,Mobile,paymentID,statusID,statusDate,itemsCharge,deliveryCharge,totalCharge,orderNote,deliveryNote,deliveryDate,createDate,modifyDate)
select
@UserID,getdate(),U.firstName,U.surName,U.emailAddress,UBA.Address1,UBA.Address2,UBA.Address3,UBA.City,UBA.County,UBA.Country,UBA.Phone,UBA.Mobile,1,1,getdate(),
(Select sum(ItemTotalCharge) from #OrderItemList),
CONVERT(money,DF.deliveryCharge) ,(select(Select sum(ItemTotalCharge)
from #OrderItemList) + CONVERT(money, DF.deliveryCharge)),
@OrderInstructions, @DeliveryDirections, getdate(), getdate(),
getdate()
from dbo.ectUser U with (nolock) join dbo.ectUserBillAddress UBA with
(nolock)
on U.customerID = UBA.customerID
join dbo.ectCountry C with (nolock)
on UBA.Country = C.Country
join dbo.ectDeliveryFee DF with (nolock)
on C.countryID = DF.countryID
where U.customerID = 1
-- Check for errors
SET @ErrorNumber= @@ERROR
IF (@ErrorNumber <> 0 )
BEGIN
PRINT 'Error Occured in 11: '
PRINT @@ERROR
ROLLBACK TRAN
RETURN
END
/********************************************************************************************************
* 12 get the orderid for the order just placed
********************************************************************************************************/
select @OrderID = max(orderID) from dbo.omtOrder O where O.customerID
= @UserID
/********************************************************************************************************
* 13 insert the order items into the Order items Table.
********************************************************************************************************/
insert into
dbo.omtOrderItems(orderID,ItemId,itemName,customerID,categoryID,statusID,statusDate,quantity,charge,createDate,modifyDate)
select @OrderID, OIL.ItemId, EIF.featureValue, @UserID, CI.categoryId,
1, getdate(), OIL.Quantity, OIL.ItemTotalCharge, getdate(),getdate()
from #OrderItemList OIL join dbo.ectItemFeature EIF with (nolock)
on OIL.ItemId = EIF.ItemId
join dbo.ectCategoryItem CI with (nolock)
on OIL.ItemId = CI.ItemId
where EIF.featureId = 1 --1 is the item name
-- Check for errors
SET @ErrorNumber= @@ERROR
IF (@ErrorNumber <> 0 )
BEGIN
PRINT 'Error Occured in 13: '
PRINT @@ERROR
ROLLBACK TRAN
RETURN
END
/********************************************************************************************************
* 14 Drop the temp tables
********************************************************************************************************/
drop table #OrderItemList
drop table #Tbl_SalePrice
/********************************************************************************************************
* 15 Select the orderid for to return to the customer
********************************************************************************************************/
select @OrderIDOutput = cast(max(orderID) as int) from dbo.omtOrder O
where O.customerID = @UserID
COMMIT TRANSACTION
GO
----------------------------------------------------------------------------------------------------------------------------------------------------------
PLEASE NOTE @CartItems expects a string of ids like '1,2,3,4'
ALSO @CartItemQuantities expects a string of quantities like '4,4,5,5'
I understand that this sql can probably be refactored but i will be
interested in that after i fix the problem.
The stored procedure uses the following fnSplitter that i found that
was kindly shared on the internet:
----------------------------------------------------------------------------------------------------------------------------------------------------
ALTER Function [dbo].[fnSplitter] (@IDs Varchar(4000))
Returns @Tbl_IDs Table (rowNum int, value varchar(1000)) As
Begin
-- Append comma
Set @IDs = @IDs + ','
-- Indexes to keep the position of searching
Declare @Pos1 Int
Declare @pos2 Int
declare @rownum int
-- Start from first character
Set @Pos1=1
Set @Pos2=1
-- row number
Set @rownum=1
While @Pos1<Len(@IDs)
Begin
Set @Pos1 = CharIndex(',',@IDs,@Pos1)
Insert @Tbl_IDs Select @rownum,
cast(Substring(@IDs,@Pos2,@Pos1-@Pos2) as int)
-- Go to next non comma character
Set @Pos2=@Pos1+1
-- Search from the next charcater
Set @Pos1 = @Pos1+1
--increment the rownumber
Set @rownum= @rownum+1
End
Return
End
I Am am having problems with a stored Procedure that i wrote.
Basically whats happening is that the Stored procedure Runs fine when i
EXECUTE it in SQL Query analyzer.
But when i debug through the application in Visual Studio .NET 2003 the
application an exception when it executes the query.
I believe that the error is related to the itemQty filled in the table
variable @OrderItemQuantity because when i change the int given to the
itemQuantity the error message will display that number.
The error i am getting in . NET (and my browser) is the following:
--------------------------------------------------------------------------------------------------------------------------------------------------------
Syntax error converting the varchar value ''1'' to a column of data
type int.
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Syntax error
converting the varchar value ''1'' to a column of data type int.
Source Error:
Line 109: catch(Exception oException)
Line 110: {
Line 111: throw oException;
Line 112: }
Line 113: finally
---------------------------------------------------------------------------------------------------------------------------------------------------------
The stored Procedure is as follows:
---------------------------------------------------------------------------------------------------------------------------------------------------------
/********************************************************************************************************
* 0 variables from the code
********************************************************************************************************/
@UserID int,
@CartItems varchar (1000),
@CartItemQuantities varchar (1000),
@DeliveryDirections varchar (3000),
@OrderInstructions varchar (3000),
@OrderIDOutput int OUTPUT
AS
/********************************************************************************************************
* 1 sproc variables
********************************************************************************************************/
declare @SQL varchar(5000)
declare @OrderID varchar(1000)
declare @ErrorNumber int
/********************************************************************************************************
* 2 create a temp table that matches the itemid to the quantity
********************************************************************************************************/
declare @OrderItemQuantity table (itemID int, itemQty int)
insert into @OrderItemQuantity
select cast(FS1.Value as int) as 'itemID', cast(FS2.Value as int) as
'itemQty'
from fnsplitter(@CartItems) FS1 join
fnsplitter(@CartItemQuantities) FS2
on FS1.rowNum = FS2.rowNum
/********************************************************************************************************
* 4 create a temp table for calculating the sale prices
********************************************************************************************************/
create table #Tbl_SalePrice (
ItemId int,
ChargePerItem money,
SaleType Varchar(50),
SaleValue Money,
ItemSaleCharge money)
/********************************************************************************************************
* 5 create a temp table for the order details
********************************************************************************************************/
create table #OrderItemList (
ItemId int,
ChargePerItem money,
Quantity int,
ItemTotalCharge money
)
BEGIN TRAN -- begin the tran--------------------
/********************************************************************************************************
* 6 popluate the temp table with the itemids & charge per item
********************************************************************************************************/
set @SQL = 'insert into #OrderItemList (ItemId, ChargePerItem) '
set @SQL = @SQL + 'select '
set @SQL = @SQL + '[IF].itemId, '
set @SQL = @SQL + 'cast(IF2.FeatureValue as money) '
set @SQL = @SQL + 'from dbo.ectItemFeature [IF] '
set @SQL = @SQL + 'join dbo.ectItemFeature IF2 '
set @SQL = @SQL + 'on [IF].itemId = IF2.itemId '
set @SQL = @SQL + 'where [IF].itemId in ('+@CartItems+') '
set @SQL = @SQL + 'and IF2.FeatureID = 9 '
set @SQL = @SQL + 'and [IF].FeatureID = 9 '
--set @SQL = @SQL + 'order by [IF].itemid '
--print @SQL
exec (@SQL)
-- Check for errors
SET @ErrorNumber= @@ERROR
IF (@ErrorNumber <> 0 )
BEGIN
PRINT 'Error Occured in 6: '
PRINT @@ERROR
ROLLBACK TRAN
RETURN
END
/********************************************************************************************************
* 7 Cursor to apply the sale where applicable
********************************************************************************************************/
--is there a sale??----------------------------------
--i wanted to create a function to apply the sale but couldnt
--do an exec(@SQL1) within a function!
declare @SQL1 varchar(5000)
--popluate the temp table with the itemids & charge per item
set @SQL1 = 'insert into #Tbl_SalePrice (ItemId, ChargePerItem) '
set @SQL1 = @SQL1 + 'select '
set @SQL1 = @SQL1 + '[IF].itemId, '
set @SQL1 = @SQL1 + 'cast(IF2.FeatureValue as money) '
set @SQL1 = @SQL1 + 'from dbo.ectItemFeature [IF] '
set @SQL1 = @SQL1 + 'join dbo.ectItemFeature IF2 '
set @SQL1 = @SQL1 + 'on [IF].itemId = IF2.itemId '
set @SQL1 = @SQL1 + 'where [IF].itemId in ('+@CartItems+') '
set @SQL1 = @SQL1 + 'and IF2.FeatureID = 9 '
set @SQL1 = @SQL1 + 'and [IF].FeatureID = 9 '
--set @SQL = @SQL + 'order by [IF].itemid '
--print @SQL
exec (@SQL1)
--check if theres a saletype and value against the items populate
tempTable
update #Tbl_SalePrice
set #Tbl_SalePrice.SaleType = (
select ST.saleType from dbo.ectSaleType ST
join dbo.ectSale S with (nolock)
on S.saleTypeID = ST.saleTypeID
join dbo.ectSaleItems SI with (nolock)
on S.saleID = SI .saleID
where SI.ItemId = #Tbl_SalePrice.ItemId
)
update #Tbl_SalePrice
set #Tbl_SalePrice.SaleValue = (
select S.saleAmountOff from dbo.ectSale S
join dbo.ectSaleItems SI with (nolock)
on S.saleID = SI .saleID
where SI.ItemId = #Tbl_SalePrice.ItemId
)
--run a cursor over the items to calculate the ItemSaleCharge
declare @ItemID int
declare SaleCalcCursor CURSOR for
select distinct(ItemId) from #Tbl_SalePrice
open SaleCalcCursor
fetch next from SaleCalcCursor
INTO @ItemID
WHILE @@FETCH_STATUS = 0
BEGIN
declare @SaleType varchar(50)
select @SaleType = SaleType From #Tbl_SalePrice WHERE ItemId =
@ItemID
if(@SaleType = 'MONEYOFF')
BEGIN
update #Tbl_SalePrice
set ItemSaleCharge = (ChargePeritem - SaleValue)
END
if(@SaleType = 'PERCENTOFF')
BEGIN
update #Tbl_SalePrice
set ItemSaleCharge = (ChargePeritem - ((ChargePeritem / 100) *
SaleValue))
END
--Can add the rest of the sale types here as the retailer wants
them
FETCH NEXT FROM SaleCalcCursor INTO @ItemID
END
close SaleCalcCursor
deallocate SaleCalcCursor
-- Check for errors
SET @ErrorNumber= @@ERROR
IF (@ErrorNumber <> 0 )
BEGIN
PRINT 'Error Occured in 7: '
PRINT @@ERROR
ROLLBACK TRAN
RETURN
END
/********************************************************************************************************
* 8 Populate the temp table with the quantities
********************************************************************************************************/
update #OrderItemList
set #OrderItemList.Quantity = (
select OIQ.itemQty from @OrderItemQuantity OIQ
where OIQ.ItemId = #OrderItemList.ItemId
)
-- Check for errors
SET @ErrorNumber= @@ERROR
IF (@ErrorNumber <> 0 )
BEGIN
PRINT 'Error Occured 8: '
PRINT @@ERROR
ROLLBACK TRAN
RETURN
END
/********************************************************************************************************
* 9 update the price of the items that are on sale
********************************************************************************************************/
update #OrderItemList
set #OrderItemList.ChargePerItem = (
select TSP.ItemSaleCharge from #Tbl_SalePrice TSP
where TSP.ItemId = #OrderItemList.ItemId
and TSP.ItemSaleCharge IS NOT NULL
)
where #OrderItemList.ItemId in (
select TSP.ItemId from #Tbl_SalePrice TSP
where TSP.ItemSaleCharge IS NOT NULL
)
-- Check for errors
SET @ErrorNumber= @@ERROR
IF (@ErrorNumber <> 0 )
BEGIN
PRINT 'Error Occured 9: '
PRINT @@ERROR
ROLLBACK TRAN
RETURN
END
/********************************************************************************************************
* 10 calculate the total cost for each item * quantity
********************************************************************************************************/
update #OrderItemList
set ItemTotalCharge = (select (ChargePerItem * Quantity))
-- Check for errors
SET @ErrorNumber= @@ERROR
IF (@ErrorNumber <> 0 )
BEGIN
PRINT 'Error Occured in 10: '
PRINT @@ERROR
ROLLBACK TRAN
RETURN
END
/********************************************************************************************************
* 11 insert the order into the Order table.
********************************************************************************************************/
insert into
dbo.omtOrder(customerID,orderDate,firstName,surName,emailAddress,Address1,Address2,Address3,City,County,Country,Phone,Mobile,paymentID,statusID,statusDate,itemsCharge,deliveryCharge,totalCharge,orderNote,deliveryNote,deliveryDate,createDate,modifyDate)
select
@UserID,getdate(),U.firstName,U.surName,U.emailAddress,UBA.Address1,UBA.Address2,UBA.Address3,UBA.City,UBA.County,UBA.Country,UBA.Phone,UBA.Mobile,1,1,getdate(),
(Select sum(ItemTotalCharge) from #OrderItemList),
CONVERT(money,DF.deliveryCharge) ,(select(Select sum(ItemTotalCharge)
from #OrderItemList) + CONVERT(money, DF.deliveryCharge)),
@OrderInstructions, @DeliveryDirections, getdate(), getdate(),
getdate()
from dbo.ectUser U with (nolock) join dbo.ectUserBillAddress UBA with
(nolock)
on U.customerID = UBA.customerID
join dbo.ectCountry C with (nolock)
on UBA.Country = C.Country
join dbo.ectDeliveryFee DF with (nolock)
on C.countryID = DF.countryID
where U.customerID = 1
-- Check for errors
SET @ErrorNumber= @@ERROR
IF (@ErrorNumber <> 0 )
BEGIN
PRINT 'Error Occured in 11: '
PRINT @@ERROR
ROLLBACK TRAN
RETURN
END
/********************************************************************************************************
* 12 get the orderid for the order just placed
********************************************************************************************************/
select @OrderID = max(orderID) from dbo.omtOrder O where O.customerID
= @UserID
/********************************************************************************************************
* 13 insert the order items into the Order items Table.
********************************************************************************************************/
insert into
dbo.omtOrderItems(orderID,ItemId,itemName,customerID,categoryID,statusID,statusDate,quantity,charge,createDate,modifyDate)
select @OrderID, OIL.ItemId, EIF.featureValue, @UserID, CI.categoryId,
1, getdate(), OIL.Quantity, OIL.ItemTotalCharge, getdate(),getdate()
from #OrderItemList OIL join dbo.ectItemFeature EIF with (nolock)
on OIL.ItemId = EIF.ItemId
join dbo.ectCategoryItem CI with (nolock)
on OIL.ItemId = CI.ItemId
where EIF.featureId = 1 --1 is the item name
-- Check for errors
SET @ErrorNumber= @@ERROR
IF (@ErrorNumber <> 0 )
BEGIN
PRINT 'Error Occured in 13: '
PRINT @@ERROR
ROLLBACK TRAN
RETURN
END
/********************************************************************************************************
* 14 Drop the temp tables
********************************************************************************************************/
drop table #OrderItemList
drop table #Tbl_SalePrice
/********************************************************************************************************
* 15 Select the orderid for to return to the customer
********************************************************************************************************/
select @OrderIDOutput = cast(max(orderID) as int) from dbo.omtOrder O
where O.customerID = @UserID
COMMIT TRANSACTION
GO
----------------------------------------------------------------------------------------------------------------------------------------------------------
PLEASE NOTE @CartItems expects a string of ids like '1,2,3,4'
ALSO @CartItemQuantities expects a string of quantities like '4,4,5,5'
I understand that this sql can probably be refactored but i will be
interested in that after i fix the problem.
The stored procedure uses the following fnSplitter that i found that
was kindly shared on the internet:
----------------------------------------------------------------------------------------------------------------------------------------------------
ALTER Function [dbo].[fnSplitter] (@IDs Varchar(4000))
Returns @Tbl_IDs Table (rowNum int, value varchar(1000)) As
Begin
-- Append comma
Set @IDs = @IDs + ','
-- Indexes to keep the position of searching
Declare @Pos1 Int
Declare @pos2 Int
declare @rownum int
-- Start from first character
Set @Pos1=1
Set @Pos2=1
-- row number
Set @rownum=1
While @Pos1<Len(@IDs)
Begin
Set @Pos1 = CharIndex(',',@IDs,@Pos1)
Insert @Tbl_IDs Select @rownum,
cast(Substring(@IDs,@Pos2,@Pos1-@Pos2) as int)
-- Go to next non comma character
Set @Pos2=@Pos1+1
-- Search from the next charcater
Set @Pos1 = @Pos1+1
--increment the rownumber
Set @rownum= @rownum+1
End
Return
End
