I am using Silverlight and Linq-to-SQL to communicate with the database.
I have a stored procedure which receives 2 parameters (PFOID and Quantity) and Userid and returns a product name.
If we send multiple values like multiple pfoid's and quantity's it will return multiple product names shown as below
The stored procedure looks like this..
ALTER PROCEDURE [PFO].[PFOValidateUpdateData]
@PfoIDs xml, -- list of PFO ID's
@UserID uniqueidentifier --The Identity of the User making the call.
AS
BEGIN
-- SET DEFAULT BEHAVIOR
SET NOCOUNT ON -- Performance: stops rows affected messages
SET DEADLOCK_PRIORITY LOW -- This SP to be the Deadlock victim
-- Initialise Lock-Timeout and Deadlock vars for Insert
DECLARE @iLockTimeoutRetries as int
DECLARE @iDeadLockRetries as int
DECLARE @dtLockTimeoutSleepInterval as datetime
DECLARE @dtDeadlockSleepInterval as datetime
DECLARE @iErrorNumber as int
SET @iLockTimeoutRetries = 0
SET @iDeadLockRetries = 0
SET @dtLockTimeoutSleepInterval = sCommon.fnLockTimeoutSleepInterval()
SET @dtDeadlockSleepInterval= sCommon.fnDeadlockSleepInterval()
SET @iErrorNumber = 0
-- procedure specific
DECLARE @idoc as int
DECLARE @IsBrightstarUser as bit
RETRY:
BEGIN TRY
--Create Temp table to store stores!
CREATE TABLE [#PFOList]
(
[PFOId] nvarchar(50),
[Quantity] INT
)
--Create Temp table to store User stores!
CREATE TABLE [#UserStoreList]
(
[StoreID_XRef] nvarchar(50)
)
print CONVERT(varchar(1000), @PfoIDs)
--Create Document
EXEC sp_xml_preparedocument @idoc OUTPUT, @PfoIDs
-- Append to new list of Store records
INSERT INTO [#PFOList] (
[PFOId],
[Quantity]
)
SELECT [PFOID],[Quantity]
FROM OPENXML (@idoc, 'ArrayOfString/string',2)
WITH( [PFOID] nvarchar(50),[Quantity] [INT]) Stores
--WHERE [PFOId] Is Not NULL
-- Clean UP
exec sp_xml_removedocument @iDoc
-- are we dealing with a brightstar user?
SET @IsBrightstarUser = CASE WHEN exists
(SELECT *
FROM dbo.aspnet_UsersInRoles AS uir inner join
dbo.aspnet_Roles AS roles ON uir.RoleId = roles.roleid
WHERE roles.rolename = 'Brightstar Employee' and uir.userid = @userid)
THEN 1 ELSE 0 END
--Get User Storelist
INSERT INTO [#UserStoreList] (
[StoreID_XRef]
)
SELECT s.StoreId_XRef
FROM PFO.UserStoreLink us(nolock)
INNER JOIN PFO.Store s(nolock)
ON us.StoreId=s.StoreId
where UserId=@UserID
--Select * from [#PFOList]
--SELECT @IsBrightstarUser AS ISBrightstaruser
--SELECT * from [#UserStoreList]
--If BrightstarCustomer Update all the Quantities.
IF @IsBrightstarUser=1
BEGIN
UPDATE
PFO.PFO
SET
IsBrightstarReviewComplete = 1
,[ModifyingUsersID] = @UserID
,[ModifiedDate] = getdate()
,[PlannedQty] = pfol.[Quantity]
,[BrightstarReviewedQty]=pfol.[Quantity]
FROM
PFO.PFO as pfo
INNER JOIN [#UserStoreList] as stores on pfo.StoreId_XRef=stores.StoreID_XRef
INNER JOIN [#PFOList] as pfol on pfo.PFOId = pfol.PFOId
WHERE @IsBrightstarUser = 1
END
ELSE BEGIN
--Update Non Contrained Orders
UPDATE
PFO.PFO
SET
[ModifyingUsersID] = @UserID
,[ModifiedDate] = getdate()
,[PlannedQty] = pfol.[Quantity]
FROM
PFO.PFO (nolock) as pfo
INNER JOIN [#UserStoreList] as stores on pfo.StoreId_XRef=stores.StoreID_XRef
INNER JOIN [#PFOList] as pfol on pfo.PFOId = pfol.PFOId
WHERE pfo.IsBrightstarReviewComplete=1 AND IsConstraint=0
--SELECT * from PFO.PFO (nolock) where PFOId='04676723-2afb-49ff-9fa1-0131cabb407c'
--Update Contrained Orders
--Get Existing quantities for the User
CREATE TABLE #ExistingProductQuantity
(
[PfoID] nvarchar(100)
,[Product] nvarchar(255)
,[PlannedQty] INT
,[BrightstarReviewedQty] INT
)
CREATE TABLE #CustProductQuantity
(
[Product] nvarchar(255)
,[IsUpdatable] BIT
)
INSERT INTO #ExistingProductQuantity
( [PfoID],[Product],[PlannedQty],[BrightstarReviewedQty])
SELECT PFOId,InventoryId,PlannedQty,BrightstarReviewedQty
FROM PFO.PFO as pfo
INNER JOIN [#UserStoreList] as stores on pfo.StoreId_XRef=stores.StoreID_XRef
WHERE pfo.IsBrightstarReviewComplete=1 AND IsConstraint=1
UPDATE
#ExistingProductQuantity
SET [PlannedQty]=pfol.[Quantity]
FROM #ExistingProductQuantity eoq
INNER JOIN [#PFOList] as pfol on eoq.PFOId = pfol.PFOId
INSERT INTO #CustProductQuantity
( [Product],[IsUpdatable] )
SELECT
[Product],
CASE WHEN SUM(PlannedQty)<=SUM(BrightstarReviewedQty) THEN 1 ELSE 0 END
FROM #ExistingProductQuantity
GROUP BY [Product]
--SELECT * from #ExistingProductQuantity
--SELECT * from #CustProductQuantity
--Update the products that can be updatable
UPDATE
PFO.PFO
SET
[ModifyingUsersID] = @UserID
,[ModifiedDate] = getdate()
,[PlannedQty] = pfol.[Quantity]
FROM
PFO.PFO as pfo
INNER JOIN #UserStoreList as stores on pfo.StoreId_XRef=stores.StoreID_XRef
INNER JOIN #PFOList as pfol on pfo.PFOId = pfol.PFOId
INNER JOIN #CustProductQuantity as pr on pr.Product=pfo.InventoryId
WHERE pfo.IsBrightstarReviewComplete=1 AND pr.IsUpdatable=1 AND IsConstraint=1
--Return the products that are not updatabele
select [Product]
FROM #CustProductQuantity
where [IsUpdatable]=0
END
END TRY
BEGIN CATCH
-- Get the ErrorNumber
Set @iErrorNumber = ERROR_NUMBER()
--Handle Deadlock situation (Deletes, Inserts & Updates)
IF @iErrorNumber = 1205
BEGIN
-- If we have not made enough attempts to break the lock
IF @iDeadLockRetries < sCommon.fnMaxDeadlockRetries()
BEGIN
-- Increment the Attempt count
SET @iDeadLockRetries = @iDeadLockRetries + 1
-- Pause to allow the deadlock contention to clear
WAITFOR DELAY @dtDeadlockSleepInterval
GOTO RETRY
END
END
-- Handle Lock Timeout situation (Deletes, Inserts & Updates)
IF @iErrorNumber = 1222
BEGIN
-- If we have not made enough attempts to break the Deadlock
IF @iLockTimeoutRetries < sCommon.fnMaxLockTimeoutRetries()
BEGIN
-- Increment the Attempt count
SET @iLockTimeoutRetries = @iLockTimeoutRetries + 1
-- Pause to allow the lock contention to clear
WAITFOR DELAY @dtLockTimeoutSleepInterval
GOTO RETRY
END
END
exec Common.RethrowError
END CATCH
END
The result is as follows..
Product
6435LVWK-360-CD819E3
NSCHI535C1097I360-4C
NSCHU485C1819I360-0C
Return Value
0
My Linq-to-SQL connection is like this
[global::System.Data.Linq.Mapping.FunctionAttribute(Name="PFO.PFOValidateUpdateData")]
public int PFOValidateUpdateData([global::System.Data.Linq.Mapping.ParameterAttribute(Name = "PfoIDs", DbType = "Xml")] System.Xml.Linq.XElement pfoIDs, [global::System.Data.Linq.Mapping.ParameterAttribute(Name = "UserID", DbType = "UniqueIdentifier")] System.Nullable<System.Guid> userID)
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), pfoIDs, userID);
return ((int)(result.ReturnValue));
}
I am trying to retrieve all the data from the stored procedure but the when I debugging it the return value is "o"..
I would be grateful to you if you could help me retrieve all the data returned by the stored procedure... thank you very much...
