[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
RE: [CF-Database] stored proc throwing error
Because u cannot concatenate an integer to a string.
> -----Original Message-----
> From: database-bounces@list.cfdeveloper.co.uk
> [mailto:database-bounces@list.cfdeveloper.co.uk] On Behalf Of
> Damian Watson
> Sent: 04 October 2004 19:43
> To: database programming
> Subject: Re: [CF-Database] stored proc throwing error
>
> >- see footer for list info
> 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/
> >
> >
> >
>
> _______________________________________________
> 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/
>