Incorrect syntax error. need some qlue

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Incorrect syntax error. need some qlue

Author

Topic

regata
Starting Member

4 Posts

Posted - 2012-04-26 : 07:36:51

Hi everyone
this code is perfectly working on sql express 2012
but in sql server 2008 r2 it throws an error

actually it does for all my queries when i ported them to sql 2008

Incorrect syntax near '='.
Incorrect syntax near '='.
Incorrect syntax near '='.
Incorrect syntax near '='.
Incorrect syntax near '='.
Incorrect syntax near '='.

can anybody help me out in here. is there a difference btwn tsqls in this versions

thnx

declare @cuser INT
declare @ukind int

set @cuser = :cuser
set @ukind = :ukind

SELECT
SS.DATE AS [date],
SUM(CASE WHEN (SS.CUSTOMER > 0) AND (SS.ITEM > 0) THEN SS.TOTAL ELSE 0 END) AS C1,
SUM(CASE WHEN (SS.CUSTOMER = 1) AND (SS.ITEM > 0) THEN SS.TOTAL ELSE 0 END) AS C2,
SUM(CASE WHEN (SS.CUSTOMER > 1) AND (SS.ITEM > 0) THEN SS.TOTAL ELSE 0 END) AS C3,
SUM(CASE WHEN (SS.CUSTOMER > 1) AND (SS.ITEM = 0) THEN SS.TOTAL ELSE 0 END) AS C4,
SUM(CASE WHEN (SS.CUSTOMER > 1) AND (SS.ITEM > 0) THEN SS.DISCOUNT ELSE 0 END) AS C5,
SUM(CASE WHEN SS.CUSTOMER = 1 THEN SS.DISCOUNT ELSE 0 END) AS C6,
SUM(CASE WHEN (SS.CUSTOMER = 0) AND (SS.ITEM = 0) THEN SS.TOTAL ELSE 0 END) AS C7,
SUM(CASE WHEN SS.CUSTOMER = -1 THEN SS.TOTAL ELSE 0 END) AS C8,
SUM(CASE WHEN (SS.CUSTOMER = 0) AND (SS.ITEM > 0) THEN SS.TOTAL ELSE 0 END) AS C9,
SUM(CASE WHEN SS.ITEM=-1 THEN SS.TOTAL ELSE 0 END) AS C10

FROM

(

SELECT
SD.DATE AS [DATE],
SD.CUSTOMER,
S.ITEM,
S.DISCOUNT,
[TOTAL] =
CASE WHEN S.EK = 1 THEN
(
round((S.QUANTITY * I.WEIGHT) * (((SELECT TOP 1 IIF(@ukind = 1,P.PRICE,P.PRICE1) FROM PRICES P WHERE (S.ITEM = P.ITEM) AND (P.DATE <=SD.DATE) ORDER BY P.DATE DESC) / I.WEIGHT) + 5),0)
)
WHEN S.EK = 2 THEN
(
CASE
WHEN (S.ITEM = 7) THEN ROUND((S.QUANTITY * I.WEIGHT) * ((SELECT TOP 1 IIF(@ukind = 1,P.PRICE,P.PRICE1) FROM PRICES P WHERE ( 2 = P.ITEM) AND (P.DATE <=SD.DATE) ORDER BY P.DATE DESC) - 1),0)
WHEN (S.ITEM = 11) THEN ROUND((S.QUANTITY * I.WEIGHT) * ((SELECT TOP 1 IIF(@ukind = 1,P.PRICE,P.PRICE1) FROM PRICES P WHERE ( 8 = P.ITEM) AND (P.DATE <=SD.DATE) ORDER BY P.DATE DESC) - 1),0)
WHEN (S.ITEM = 15) THEN ROUND((S.QUANTITY * I.WEIGHT) * ((SELECT TOP 1 IIF(@ukind = 1,P.PRICE,P.PRICE1) FROM PRICES P WHERE (12 = P.ITEM) AND (P.DATE <=SD.DATE) ORDER BY P.DATE DESC) - 1),0)
ELSE
round((S.QUANTITY * I.WEIGHT) * (((SELECT TOP 1 IIF(@ukind = 1,P.PRICE,P.PRICE1) FROM PRICES P WHERE (S.ITEM = P.ITEM) AND (P.DATE <=SD.DATE) ORDER BY P.DATE DESC) / I.WEIGHT) + 4),0)
END
)
ELSE
(S.QUANTITY * (SELECT TOP 1 IIF(@ukind = 1,P.PRICE,P.PRICE1) FROM PRICES P WHERE (S.ITEM = P.ITEM) AND (P.DATE <=SD.DATE) ORDER BY P.DATE DESC))
END

FROM
DOCS AS SD
JOIN STOCK AS S ON SD.ID = S.DOC
JOIN ITEMS AS I ON I.ID = S.ITEM

WHERE SD.USR = @cuser

UNION ALL

SELECT
SI.DATE AS [DATE],
-1 AS CUTOMER,
SI.ITEM,
0 AS DISCOUNT,
(SI.QUANTITY * (SELECT TOP 1 IIF(@ukind = 1,P.PRICE,P.PRICE1) FROM PRICES P WHERE (SI.ITEM = P.ITEM) AND (P.DATE <=SI.DATE) ORDER BY P.DATE DESC )) AS [TOTAL]
FROM
STOCKIN SI
WHERE SI.KIND <> 2 AND SI.USR = @cuser

UNION ALL

SELECT
K.DATE AS [DATE],
K.CUSTOMER,
K.REASON AS ITEM,
0 AS DISCOUNT,
K.AMOUNT AS [TOTAL]
FROM
KASA K

WHERE K.USR = @cuser

UNION ALL
SELECT
GK.DATE AS [DATE],
0 AS CUSTOMER,
-1 AS ITEM,
0 AS DISCOUNT,
GK.AMOUNT AS [TOTAL]
FROM
GKASA GK

WHERE GK.USR = @cuser

) AS SS

WHERE month(getdate()) = month(SS.DATE) and year(getdate()) = year(SS.DATE)
GROUP BY SS.DATE;

Nurlan

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-26 : 07:43:29

I think a bunch of colons (:) have gotten into your code during porting. If you double-click on the error message in SSMS, it will take you to the line where you have the error. For example, the first error is on line 4:

set @cuser = :cuser

Remove the colon and the error goes away.

set @cuser = cuser

regata
Starting Member

4 Posts

Posted - 2012-04-26 : 22:54:47

ok tnx sunitabeck i.v never used any sql manager, most advanced sql manager was notepad. so anyway i downloaded SSMS tnx for that.
but now another question came up. How do i use or pass data to parametrs because that (the semicln ':' ) was the only way i know.
by the way. i use (sql server+delphi)

Nurlan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-26 : 23:06:02

remove the :

just simply


set @cuser = cuser

KH
[spoiler]Time is always against us[/spoiler]

regata
Starting Member

4 Posts

Posted - 2012-04-26 : 23:26:15

quote:
Originally posted by khtan

remove the :

just simply

set @cuser = cuser

KH
[spoiler]Time is always against us[/spoiler]

ok now i'm realy confused
so i did remove the semis and this happen

<< MS Sql Server mgmt Studio >>
Msg 207, Level 16, State 1, Line 4
Invalid column name 'cuser'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'ukind'.

ok let me clear the situation. i am trying to pas data from Delphi to query as parameter and parameter supposed to init variables in this case @cuser with param value. it was working on my pc
(Win 7, Delphi 2010, Sql server Express 2012) and when i ported to another pc
(Win 2008 Server, Sql Server 2008 r2) it starts to throw errors

Nurlan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources