Re: [CF-Database] stored proc throwing error

["Nick de Voil" <nick@devoil.com>]

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/
>
>