I have been having a heck of a time with trying to pass a list into a
stored procedure and have it parsed out. The problem I am having is
getting CF to pass the list into the stored procedure. Can someone point
me in the right direction?
Thanks.
Error I am seeing:
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax
near '|'.
SQL = "EXEC [DevAcmePlus].[dbo].uspCrossSegmentEmailRemove @Str =
876|1075"
Here is the code on my page:
<cfset Keys = ListChangeDelims(Keys, "|", ",")>
<cfquery name="deDupeSegments" dbtype="odbc" datasource="#Client.DS#"
username="#Client.UID#" password="#Client.PWD#">
EXEC #Client.DBPrefix#.uspCrossSegmentEmailRemove
@Str = #Keys#
</cfquery>
And here is the SP I am trying to pass the list to:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE uspCrossSegmentEmailRemove
AS
-- select top 10 * from segment order by segmentkey desc
declare @tmpSegments table (PrimID INT Identity(1,1), SegmentKey INT)
declare @tmpLog table (SegmentKey int, segName varchar(255), num int)
declare @SegmentKey INT
declare @Num INT
declare @SegName VarChar(64)
DECLARE @TmpTbl TABLE (SegKey INT)
DECLARE @Str CHAR(255)
DECLARE @sTmp VARCHAR(15)
DECLARE @Idx INT
SET @Str = @Str
WHILE Len(@Str) > 0
BEGIN
SET @Idx = CharIndex('|', @Str)
IF @Idx > 0
BEGIN
SET @sTmp = LEFT(@Str, @Idx-1)
SET @Str = SubString(@Str, @Idx+1, 255)
END
ELSE
BEGIN
SET @sTmp = @Str
SET @Str = ''
END
insert into @tmpSegments (SegmentKey) Values (@sTmp)
END
declare curSegments cursor
for select SegmentKey
from @tmpSegments
order By PrimID
open curSegments
-- get all the first segment
fetch next from curSegments into @SegmentKey
-- get all the bad email addresses into the temp table
truncate table tmpEmails
while @@Fetch_status = 0
begin
SELECT @SegName = Name FROM Segment WHERE SegmentKey = @SegmentKey
-- Delete Any Emails from the queue that we already have
DELETE FROM EmailQueue
--SELECT @Num = COUNT(*) FROM EmailQueue
WHERE EmailQueueKey IN
(SELECT EmailQueueKey
FROM EmailQueue
INNER JOIN tmpEmails
ON EmailQueue.Address = tmpEmails.
Address
WHERE SegmentKey = @SegmentKey
AND Status = 201)
SET @Num = @@rowcount
insert into @tmpLog (SegmentKey, SegName, Num)
values (@SegmentKey, @SegName, @Num)
-- Add every email from this segment into the tmp List
INSERT INTO tmpEmails (address)
SELECT DISTINCT left(Address,128)
FROM EmailQueue
WHERE SegmentKey = @SegmentKey
AND Status = 201
-- union (
-- SELECT DISTINCT left(Address,128)
-- FROM CustomerHistory
-- WHERE SegmentKey = @SegmentKey
-- )
fetch next from curSegments into @SegmentKey
end
close curSegments
deallocate curSegments
select * from @tmpLog
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
