Syntax error in SPROC

Question

Syntax error in SPROC

dean gross

SSCrazy

Points: 2632
More actions
August 14, 2007 at 6:21 am

#373156

I'm trying to create a sproc with the following t-sql:
SET
@sql =
'SELECT dbo.Programs.Program
, LEFT(loc.Work_Location, 1) + '
0' AS DivNum
, CASE WHEN LEFT(loc.Work_Location, 1)+ '
0' = 80 THEN 'Buildings' ELSE d.[Division Name] END AS Division
, JM.Job_Number AS Job
, Cust.Customer_Name AS Customer
, Emp.Employee_First_and_Last_Name AS BDRep
, SUM(BudDet.Budgeted_Cost_Amount) AS [Budgeted GPS]
, dbo.Calendar.FP AS FinPer
, dbo.Calendar.FY AS FinYear
, '
New' AS GPSType
and I get the following error
Msg 102, Level 15, State 1, Procedure srptGrossProfitSalesByLibrary, Line 22
Incorrect syntax near '0'.
What am I doing wrong?
TIA
Dean

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

bledu SSChampion Points: 12335 More actions · Answer 1

you are using dynamic sql hence you need to enclose your zero with another set of quotes since you want to concatenate and not add it to the left most character. i.e.

...+ ''0'' + ...

Everything you can imagine is real.

bledu SSChampion Points: 12335 More actions · Answer 2

you also need to enclose your values in another pair of quotes

''Building''

Everything you can imagine is real.

dean gross SSCrazy Points: 2632 More actions · Answer 3

dean gross

SSCrazy

Points: 2632

August 14, 2007 at 7:57 am

#726778

thank you

EdVassie SSC Guru Points: 60476 More actions · Answer 4

If you do not want SQL to spend time doing a data type conversion for every row in the query, you should make the 80 into a character string ...= ''80'' ...

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Vinit Srivastava SSC-Addicted Points: 431 More actions · Answer 5

Hi chwck this code . I think this help you

declare @sql varchar(600)

SET @sql = 'SELECT dbo.Programs.Program,JM.Job_Number AS Job,Cust.Customer_Name AS Customer,Emp.Employee_First_and_Last_Name AS BDRep,

SUM(BudDet.Budgeted_Cost_Amount) AS [Budgeted GPS], dbo.Calendar.FP AS FinPer, dbo.Calendar.FY AS FinYear ,New AS GPSType,

LEFT(loc.Work_Location, 1) + 0 AS DivNum ,CASE WHEN LEFT(loc.Work_Location, 1)+ 0 = 80 THEN Buildings ELSE d.[Division Name] END AS Division'

print @sql

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

There is nothing in the code that needs dynamic SQL. Rewrite the code to take variables. Normally, the only time you need dynamic SQL is for variable table or column names and I'm not seeing any of that in this code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)