how can I resolve this error message coming from the line of code used to
build up a whereclause ?
error message
--
syntax error converting the varchar value ' and IDUser >= 1 ' to a column of
data type int
sproc
--
...
IF @.ClientID <> -1 BEGIN SET @.WhereClause = @.WhereClause + ' and IDuser = '
+ @.ClientID END
IF @.ClientID = -1 BEGIN SET @.WhereClause = @.WhereClause + ' and IDuser >= 1
' END
...SQL Server thinks that you want to mathematical add two numbers together
because there is a INT in the addition. To Keep sure to concat the two
strings together you have to cast the INT value to a character type like
varchar(10), so the whole expression should be:
> IF @.ClientID <> -1 BEGIN SET @.WhereClause = @.WhereClause + ' and IDuser =
> ' + CAST(@.ClientID AS VARCHAR(50)) END
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"TJS" <nospam@.here.com> schrieb im Newsbeitrag
news:e9a6rJuaFHA.584@.TK2MSFTNGP15.phx.gbl...
> how can I resolve this error message coming from the line of code used to
> build up a whereclause ?
> error message
> --
> syntax error converting the varchar value ' and IDUser >= 1 ' to a column
> of data type int
> sproc
> --
> ...
> IF @.ClientID <> -1 BEGIN SET @.WhereClause = @.WhereClause + ' and IDuser =
> ' + @.ClientID END
> IF @.ClientID = -1 BEGIN SET @.WhereClause = @.WhereClause + ' and IDuser >=
> 1 ' END
> ...
>|||thanks for replying, I tried your suggestion but I still get the same error
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:e5hlJguaFHA.3572@.TK2MSFTNGP12.phx.gbl...
> SQL Server thinks that you want to mathematical add two numbers together
> because there is a INT in the addition. To Keep sure to concat the two
> strings together you have to cast the INT value to a character type like
> varchar(10), so the whole expression should be:
>
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "TJS" <nospam@.here.com> schrieb im Newsbeitrag
> news:e9a6rJuaFHA.584@.TK2MSFTNGP15.phx.gbl...
>|||Can you send in he whole statements and the exact error to see where the
Parser throws the error ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"TJS" <nospam@.here.com> schrieb im Newsbeitrag
news:OKz0PDvaFHA.2768@.tk2msftngp13.phx.gbl...
> thanks for replying, I tried your suggestion but I still get the same
> error
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:e5hlJguaFHA.3572@.TK2MSFTNGP12.phx.gbl...
>|||Could it be that @.WhereClause is declared as integer?
Ral Moloye|||here is current version, the purpose of the sproc is to return a list if
matching record id's
based on filter parameters provided.
========================================
============================
Alter Procedure "ListID"
(
@.admin nvarchar(10),
@.YearID int,
@.ClientID int,
@.ProjectID int
)
As
DECLARE @.List varchar(100)
DECLARE @.WhereClause nvarchar (1000)
DECLARE @.CastString1 varchar (50)
DECLARE @.CastString2 varchar (50)
SET @.WhereClause=''
SET @.CastString1 = ' and IDuser >= ' + CAST(@.ClientID AS VARCHAR(50))
SET @.CastString2 = ' and IDuser = ' + CAST(@.ClientID AS VARCHAR(50))
-- Define the selection criteria for client depending on whether or not
user is an admin
-- ----
---
IF @.admin = 'true' and @.ClientID = -1 BEGIN SET @.WhereClause =
@.WhereClause + @.CastString1 END
IF @.admin = 'true' and @.ClientID <> -1 BEGIN SET @.WhereClause =
@.WhereClause + @.CastString2 END
IF @.admin = 'false' BEGIN SET @.WhereClause = @.WhereClause +
@.CastString2 END
-- Define the selection criteria for selections of project and year
-- ----
---
IF @.ProjectID <> -1 BEGIN SET @.WhereClause = @.WhereClause + ' and IDProject
= ' + @.ProjectID END
IF @.YearID <> -1 BEGIN SET @.WhereClause = @.WhereClause + ' and
year(issueDate) = ' + @.YearID END
-- retrieve selection
-- ----
---
SELECT @.List = COALESCE(@.List + ', ', '') + CAST(InvoiceID AS varchar(5))
FROM invoices, Projects
WHERE Projects.ProjectID = Invoices.IDProject + @.WhereClause
-- return results
-- ----
---
SELECT @.List|||Everywhere where a INt takes place (doesntmatter if you declared it in your
procedure or you get it from the database) and you want to add it to the
string, SQL Server will try to do a methematical addition to the strings
which wont work, so you will have to replace that in your whole code (e.g.
For YearID, projectId ...)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"TJS" <nospam@.here.com> schrieb im Newsbeitrag
news:uVOaKl4aFHA.3840@.tk2msftngp13.phx.gbl...
> here is current version, the purpose of the sproc is to return a list if
> matching record id's
> based on filter parameters provided.
> ========================================
============================
> Alter Procedure "ListID"
> (
> @.admin nvarchar(10),
> @.YearID int,
> @.ClientID int,
> @.ProjectID int
> )
> As
> DECLARE @.List varchar(100)
> DECLARE @.WhereClause nvarchar (1000)
> DECLARE @.CastString1 varchar (50)
> DECLARE @.CastString2 varchar (50)
> SET @.WhereClause=''
> SET @.CastString1 = ' and IDuser >= ' + CAST(@.ClientID AS VARCHAR(50))
> SET @.CastString2 = ' and IDuser = ' + CAST(@.ClientID AS VARCHAR(50))
> -- Define the selection criteria for client depending on whether or not
> user is an admin
> -- ----
---
> IF @.admin = 'true' and @.ClientID = -1 BEGIN SET @.WhereClause =
> @.WhereClause + @.CastString1 END
> IF @.admin = 'true' and @.ClientID <> -1 BEGIN SET @.WhereClause =
> @.WhereClause + @.CastString2 END
> IF @.admin = 'false' BEGIN SET @.WhereClause = @.WhereClause +
> @.CastString2 END
> -- Define the selection criteria for selections of project and year
> -- ----
---
> IF @.ProjectID <> -1 BEGIN SET @.WhereClause = @.WhereClause + ' and
> IDProject = ' + @.ProjectID END
> IF @.YearID <> -1 BEGIN SET @.WhereClause = @.WhereClause + ' and
> year(issueDate) = ' + @.YearID END
> -- retrieve selection
> -- ----
---
> SELECT @.List = COALESCE(@.List + ', ', '') + CAST(InvoiceID AS varchar(5))
> FROM invoices, Projects
> WHERE Projects.ProjectID = Invoices.IDProject + @.WhereClause
> -- return results
> -- ----
---
> SELECT @.List
>
