[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: [CF-Database] stored proc throwing error

Nope, but after hours of hair tearing I figured out the problem here...

@dynSQL is a string being SET to then be executed

I was trying to compile that string with declared integer variables. Apparently SQL doesn't like that (anyone got a slightly technical explanation for that?). So in the end I declared three extra varchar variables and set them with the values of the three integer vars, The final result sp looks like this:

CREATE PROCEDURE [dbo].[a_updateListItemsCategorised]
@i_listPosition int,
@i_listItemID int,
@i_parentID int,
@s_tablename varchar(50),
@s_childColumn varchar(50),
@s_parentColumn varchar(50)
AS
DECLARE @dynSQL varchar(2000)
/* pass the integers into varchar */
DECLARE @s_listItemID varchar(10)
DECLARE @s_listPosition varchar(10)
DECLARE @s_parentID varchar(10)
SET @s_listItemID = @i_listItemID
SET @s_listPosition = @i_listPosition
SET @s_parentID = @i_parentID

IF @i_listPosition = 1
BEGIN
SET @dynSQL = 'UPDATE ' + @s_tablename + ' SET listPosition = listPosition + 1
WHERE ' + @s_childColumn + ' = ' + @s_listItemID + '

UPDATE ' + @s_tablename + '
SET listPosition = listPosition -1
WHERE ' + @s_parentColumn + '=' + @s_parentID + ' AND
listPosition = ' + @s_listPosition + ' + 1 AND '
+ @s_childColumn + ' <> ' + @s_listItemID
EXEC (@dynSQL)
END

ELSE

BEGIN
SET @dynSQL = 'UPDATE ' + @s_tablename + '
SET listPosition = listPosition - 1
WHERE ' + @s_childColumn + ' = ' + @s_listItemID + '

UPDATE ' + @s_tablename + '
SET listPosition = listPosition +1
WHERE ' + @s_parentColumn + '=' + @s_parentID + ' AND
listPosition = ' + @s_listPosition + ' - 1 AND '
+ @s_childColumn + ' <> ' + @s_listItemID
EXEC (@dynSQL)
END
GO

For info, this is a really handy way of dynamically moving any content in a list by one position- if anyone wants I can bung you the files...

d

Nick de Voil wrote:

- see footer for list info


Should that " + 1" at the end of a couple of WHERE clauses be a " = 1" ?

Nick

----- Original Message ----- From: "Damian Watson" <damian@greenhouse-design.co.uk>
To: <database@list.cfdeveloper.co.uk>
Sent: Monday, October 04, 2004 5:36 PM
Subject: [CF-Database] stored proc throwing error




- see footer for list info


Hey all, I'm a bit stumped here...

I've got a dynamic sp which is throwing the following error:

[MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC SQL Server
Driver][SQL Server]Syntax error converting the varchar value 'UPDATE
c_questionOptions SET listPosition = listPosition + 1 WHERE
questionOptionID = ' to a column of data type int.

Here's the stored proc:

CREATE PROCEDURE [dbo].[a_updateListItemsCategorised]
   @i_listPosition        int,
   @i_listItemID        int,
   @i_parentID        int,
   @s_tablename        varchar(50),
   @s_childColumn        varchar(50),
   @s_parentColumn    varchar(50)
AS
   DECLARE    @dynSQL     varchar(2000)

IF @i_listPosition = 1

       BEGIN
           SET         @dynSQL     =     'UPDATE ' + @s_tablename +
                               ' SET listPosition = listPosition + 1
                               WHERE ' + @s_childColumn + ' = ' +
@i_listItemID +

' UPDATE ' + @s_tablename + '
SET listPosition = listPosition -1
WHERE ' + @s_parentColumn + '=' +
@i_parentID + ' AND
listPosition = ' + @i_listPosition + ' +


1'


           EXEC        (@dynSQL)
       END

ELSE

       BEGIN
           SET         @dynSQL     =     'UPDATE ' + @s_tablename + '
SET listPosition = listPosition - 1 WHERE ' + @s_childColumn + ' = ' +
@i_listItemID + ' UPDATE ' + @s_tablename + ' SET listPosition =
listPosition +1 WHERE ' + @s_parentColumn + '=' + @i_parentID + ' AND
listPosition = ' + @i_listPosition +  ' + 1'
           EXEC        (@dynSQL)
       END
GO

I've found where the problem lies as I've successfully passed dummy data
in statically...

WHERE ' + @s_childColumn + ' = ' + @i_listItemID +

The error is happening when it gets to @i_listItemID so: WHERE ' +
@s_childColumn + ' = 1' for instance works fine... but I'm going insane,
anyone shed any light?

Thanks for any help,
d
_______________________________________________
For info on all lists and to join/leave go to


http://list.cfdeveloper.co.uk/mailman/listinfo


--

CFDeveloper is hosted by CFMXhosting.co.uk and managed/supported by Russ


Michaels


Thanks also to the following for their support:- * www.fusetalk.com *


www.gradwell.com * www.proworkflow.com * www.helmguru.com * www.APYCOM.com *


_______________________________________________

These lists are syncronised with the CFDeveloper forum at


http://forum.cfdeveloper.co.uk/


Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/







_______________________________________________
For info on all lists and to join/leave go to http://list.cfdeveloper.co.uk/mailman/listinfo
--

CFDeveloper is hosted by CFMXhosting.co.uk and managed/supported by Russ Michaels
Thanks also to the following for their support:- * www.fusetalk.com * www.gradwell.com * www.proworkflow.com * www.helmguru.com * www.APYCOM.com * _______________________________________________

These lists are syncronised with the CFDeveloper forum at http://forum.cfdeveloper.co.uk/
Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/