Solved

How do I fix these syntax errors in my SQL statement?

Posted on 2008-10-20
29
317 Views
Last Modified: 2012-05-05
I get the following errors with the SQL statement below.  My intention is to use this as a datasource for a DataView control.  Can you help?  Thanks!

Msg 134, Level 15, State 1, Procedure GetInventory, Line 5
The variable name '@strAllocateSQL' has already been declared. Variable names must be unique within a query batch or stored procedure.
Msg 102, Level 15, State 1, Procedure GetInventory, Line 6
Incorrect syntax near 'R'.
Msg 102, Level 15, State 1, Procedure GetInventory, Line 25
Incorrect syntax near '{'.
Msg 105, Level 15, State 1, Procedure GetInventory, Line 34
Unclosed quotation mark after the character string ';
 
return
'.

create procedure GetInventory (@chkInventory int, @txtCustomerNumber varchar(max), @strGrade varchar(max), @strSecondaryRodSize varchar(max), @strRodSize varchar(max), @strAllocateSQL varchar(max) output)
as
declare @strAllocateSQL varchar(max)
 
set @strAllocateSQL = 'SELECT x.* FROM (SELECT b.HeatID,c.Name AS Customer,a.RodSize,a.HeatNumber,a.Grade,d.Name AS Vendor,
                  SUM(case when b.TrxType = 'R' then b.NoOfCoils else 0 end) As Received,
                  SUM(case when b.TrxType = 'S' then b.CoilsShipped else 0 end) As Shipped,
                  SUM(case when b.TrxType = 'A' then b.NoOfCoils else 0 end) As Allocated
                  ,SUM(case when b.TrxType = 'X' then b.NoOfCoils else 0 end) As Transferred
                  ,(Received-Shipped+Transferred) AS OnHand
                  ,(Received-Allocated+Transferred) AS Available
                  ,SUM(case when b.TrxType = 'R' then b.LbsRecd else 0 end) As Weight
                  ,case when Received>0 then cast(Weight/Received as int) else 0 end) AS AvgWt
                  from tblHeat AS a LEFT JOIN tblHeatTransaction AS b ON
                          a.ID = b.HeatID INNER JOIN
                          tblCustomer AS c ON a.CustomerNumber = c.CustomerNumber) as a INNER JOIN
                          tblVendor AS d ON a.VendorNumber = d.VendorNumber
 
                  if( @chkInventory = 1 ) then
                  {
                      set @strAllocateSQL = @strAllocateSQL + 'WHERE b.CustomerNumber = cast(@txtCustomerNumber as int)
                         ' AND a.Grade = @strGrade'
                                    
                        if( @strSecondaryRodSize ='')
                        {
                              @strAllocateSQL = @strAllocateSQL + ' AND a.RodSize = @strRodSize'
                        }
                        else
                          {
                        @strAllocateSQL = @strAllocateSQL+ ' AND (a.RodSize =@strRodSize' OR a.RodSize = @strSecondaryRodSize)'
                        }
                   }
 
                   @strAllocateSQL =@strAllocateSQL+ 'GROUP BY b.HeatID,c.Name,a.RodSize,a.HeatNumber,a.Grade,d.Name) AS x WHERE x.OnHand>0 ';
 
return

Open in new window

0
Comment
Question by:jvalescu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 8
  • 5
  • +2
29 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22758491
there are indeed several errors:
create procedure GetInventory (@chkInventory int, @txtCustomerNumber varchar(max), @strGrade varchar(max), @strSecondaryRodSize varchar(max), @strRodSize varchar(max), @strAllocateSQL varchar(max) output)
as
--declare @strAllocateSQL varchar(max) no need, as the parameter name is already declared.
 
-- inside a string, a single quote must be written as 2 single quotes: 
set @strAllocateSQL = 'SELECT x.* FROM (SELECT b.HeatID,c.Name AS Customer,a.RodSize,a.HeatNumber,a.Grade,d.Name AS Vendor,
                  SUM(case when b.TrxType = ''R'' then b.NoOfCoils else 0 end) As Received,
                  SUM(case when b.TrxType = ''S'' then b.CoilsShipped else 0 end) As Shipped,
                  SUM(case when b.TrxType = ''A'' then b.NoOfCoils else 0 end) As Allocated
                  ,SUM(case when b.TrxType = ''X'' then b.NoOfCoils else 0 end) As Transferred
                  ,(Received-Shipped+Transferred) AS OnHand
                  ,(Received-Allocated+Transferred) AS Available
                  ,SUM(case when b.TrxType = ''R'' then b.LbsRecd else 0 end) As Weight
                  ,case when Received>0 then cast(Weight/Received as int) else 0 end) AS AvgWt
                  from tblHeat AS a LEFT JOIN tblHeatTransaction AS b ON
                          a.ID = b.HeatID INNER JOIN
                          tblCustomer AS c ON a.CustomerNumber = c.CustomerNumber) as a INNER JOIN
                          tblVendor AS d ON a.VendorNumber = d.VendorNumber
                  where 1 = 1
                  ' 
                  if( @chkInventory = 1 ) -- then tsql has no THEN and no { 
                  begin
                      set @strAllocateSQL = @strAllocateSQL + ' AND b.CustomerNumber = ' + cast(@txtCustomerNumber as int) 
                      set @strAllocateSQL = @strAllocateSQL + ' AND a.Grade = ''' + @strGrade + ''' '
                                    
                      if( @strSecondaryRodSize =''
                         SET @strAllocateSQL = @strAllocateSQL + ' AND a.RodSize = ''' + @strRodSize + ''' '
                      else                          
                         SET @strAllocateSQL = @strAllocateSQL+ ' AND (a.RodSize = ''' + @strRodSize + ''' OR a.RodSize = ''' +  @strSecondaryRodSize + ''' )'
                        
                   end
 
                   set @strAllocateSQL =@strAllocateSQL + ' GROUP BY b.HeatID,c.Name,a.RodSize,a.HeatNumber,a.Grade,d.Name) AS x WHERE x.OnHand>0 '
 
return

Open in new window

0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22758501
Msg 134, Level 15, State 1, Procedure GetInventory, Line 5
The variable name '@strAllocateSQL' has already been declared. Variable names must be unique within a query batch or stored procedure.

@strAllocateSQL is a parameter name.  If you want to use the parameter, don't DECLARE it again.  If you want to DECLARE a separate variable, use a name other than the parameter name.

On your If ... Else ... blocks, use BEGIN and END, not { ... }

Give that a try ... and let me know!
0
 
LVL 8

Expert Comment

by:mikainz
ID: 22758513
you do not have to declare the variable @strallocatesql again, if you have it in the parameters>
 Msg 134, Level 15, State 1, Procedure GetInventory, Line 5
The variable name '@strAllocateSQL' has already been declared. Variable names must be unique within a query batch or stored procedure.
delete the line
declare @strAllocateSQL varchar(max)

 
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22758522
OK, Angel ... you beat me & found the 3rd error at the same time!  Congrats!
0
 
LVL 16

Expert Comment

by:brad2575
ID: 22758529
this should do it.
create procedure GetInventory (@chkInventory int, @txtCustomerNumber varchar(max), @strGrade varchar(max), @strSecondaryRodSize varchar(max), @strRodSize varchar(max), @strAllocateSQL varchar(max) output)
as
 
 
set @strAllocateSQL = 'SELECT x.* FROM (SELECT b.HeatID,c.Name AS Customer,a.RodSize,a.HeatNumber,a.Grade,d.Name AS Vendor,
                  SUM(case when b.TrxType = ''R'' then b.NoOfCoils else 0 end) As Received,
                  SUM(case when b.TrxType = ''S'' then b.CoilsShipped else 0 end) As Shipped,
                  SUM(case when b.TrxType = ''A'' then b.NoOfCoils else 0 end) As Allocated
                  ,SUM(case when b.TrxType = ''X'' then b.NoOfCoils else 0 end) As Transferred
                  ,(Received-Shipped+Transferred) AS OnHand
                  ,(Received-Allocated+Transferred) AS Available
                  ,SUM(case when b.TrxType = ''R'' then b.LbsRecd else 0 end) As Weight
                  ,case when Received>0 then cast(Weight/Received as int) else 0 end) AS AvgWt
                  from tblHeat AS a LEFT JOIN tblHeatTransaction AS b ON
                          a.ID = b.HeatID INNER JOIN
                          tblCustomer AS c ON a.CustomerNumber = c.CustomerNumber) as a INNER JOIN
                          tblVendor AS d ON a.VendorNumber = d.VendorNumber'
 
                  if( @chkInventory = 1 ) then
                  {
                      set @strAllocateSQL = @strAllocateSQL + 'WHERE b.CustomerNumber = cast(@txtCustomerNumber as int)
                          AND a.Grade = @strGrade'
                                    
                        if( @strSecondaryRodSize ='')
                        {
                              @strAllocateSQL = @strAllocateSQL + ' AND a.RodSize = @strRodSize'
                        }
                        else
                          {
                        @strAllocateSQL = @strAllocateSQL+ ' AND (a.RodSize =''' + @strRodSize + ''' OR a.RodSize = @strSecondaryRodSize)'
                        }
                   }
 
                   @strAllocateSQL =@strAllocateSQL+ 'GROUP BY b.HeatID,c.Name,a.RodSize,a.HeatNumber,a.Grade,d.Name) AS x WHERE x.OnHand>0 ';
 
return

Open in new window

0
 
LVL 16

Expert Comment

by:brad2575
ID: 22758540
oops did not see/update the if statements
create procedure GetInventory (@chkInventory int, @txtCustomerNumber varchar(max), @strGrade varchar(max), @strSecondaryRodSize varchar(max), @strRodSize varchar(max), @strAllocateSQL varchar(max) output)
as
 
 
set @strAllocateSQL = 'SELECT x.* FROM (SELECT b.HeatID,c.Name AS Customer,a.RodSize,a.HeatNumber,a.Grade,d.Name AS Vendor,
                  SUM(case when b.TrxType = ''R'' then b.NoOfCoils else 0 end) As Received,
                  SUM(case when b.TrxType = ''S'' then b.CoilsShipped else 0 end) As Shipped,
                  SUM(case when b.TrxType = ''A'' then b.NoOfCoils else 0 end) As Allocated
                  ,SUM(case when b.TrxType = ''X'' then b.NoOfCoils else 0 end) As Transferred
                  ,(Received-Shipped+Transferred) AS OnHand
                  ,(Received-Allocated+Transferred) AS Available
                  ,SUM(case when b.TrxType = ''R'' then b.LbsRecd else 0 end) As Weight
                  ,case when Received>0 then cast(Weight/Received as int) else 0 end) AS AvgWt
                  from tblHeat AS a LEFT JOIN tblHeatTransaction AS b ON
                          a.ID = b.HeatID INNER JOIN
                          tblCustomer AS c ON a.CustomerNumber = c.CustomerNumber) as a INNER JOIN
                          tblVendor AS d ON a.VendorNumber = d.VendorNumber'
 
                  if  @chkInventory = 1  
                  Begin
                      set @strAllocateSQL = @strAllocateSQL + 'WHERE b.CustomerNumber = cast(@txtCustomerNumber as int)
                          AND a.Grade = @strGrade'
                                    
                        if  @strSecondaryRodSize =''
                              @strAllocateSQL = @strAllocateSQL + ' AND a.RodSize = ''' + @strRodSize + ''''
                        Else                        
							@strAllocateSQL = @strAllocateSQL+ ' AND (a.RodSize =''' + @strRodSize + ''' OR a.RodSize = @strSecondaryRodSize)'
                        
                   END
 
                   @strAllocateSQL =@strAllocateSQL+ 'GROUP BY b.HeatID,c.Name,a.RodSize,a.HeatNumber,a.Grade,d.Name) AS x WHERE x.OnHand>0 ';
 
return

Open in new window

0
 

Author Comment

by:jvalescu
ID: 22758618
Brad,

    Tried your statement and get this error:

Msg 102, Level 15, State 1, Procedure GetInventory, Line 25
Incorrect syntax near '@strAllocateSQL'.

That line is :

   @strAllocateSQL = @strAllocateSQL + ' AND a.RodSize = ''' + @strRodSize + ''''
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22758702
you need SET at the start of all your variable assignments.  See AngelIII's code.
0
 

Author Comment

by:jvalescu
ID: 22758754
I put Angel's code in like below and am getting:

Msg 156, Level 15, State 1, Procedure GetInventory, Line 25
Incorrect syntax near the keyword 'SET'.
Msg 156, Level 15, State 1, Procedure GetInventory, Line 26
Incorrect syntax near the keyword 'else'.


Line 25:    SET @strAllocateSQL = @strAllocateSQL + ' AND a.RodSize = ''' + @strRodSize + ''' '
Line 26:                           else                          
 if( @chkInventory = 1 ) -- then tsql has no THEN and no { 
                  begin
                      set @strAllocateSQL = @strAllocateSQL + ' AND b.CustomerNumber = ' + cast(@txtCustomerNumber as int) 
                      set @strAllocateSQL = @strAllocateSQL + ' AND a.Grade = ''' + @strGrade + ''' '
                                    
                      if( @strSecondaryRodSize =''
                         SET @strAllocateSQL = @strAllocateSQL + ' AND a.RodSize = ''' + @strRodSize + ''' '
                      else                          
                         SET @strAllocateSQL = @strAllocateSQL+ ' AND (a.RodSize = ''' + @strRodSize + ''' OR a.RodSize = ''' +  @strSecondaryRodSize + ''' )'
                        
                   end
 
                   set @strAllocateSQL =@strAllocateSQL + ' GROUP BY b.HeatID,c.Name,a.RodSize,a.HeatNumber,a.Grade,d.Name) AS x WHERE x.OnHand>0 '

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22758801
sorry:

if( @strSecondaryRodSize =''


must be :

if( @strSecondaryRodSize ='')
0
 

Author Comment

by:jvalescu
ID: 22759218
Almost there.  The procedure compiles, but when I run it, I get the error at the bottom like this:
( I also changed the line in the code snippet to accept null for SecondaryRodSize)


USE [OrderEntry_prodSQL]
GO

DECLARE      @return_value int,
            @strAllocateSQL varchar(max)

EXEC      @return_value = [dbo].[GetInventory]
            @chkInventory = 1,
            @txtCustomerNumber = N'10',
            @strGrade = N'1022',
            @strSecondaryRodSize = NULL,
            @strRodSize = N'7/32',
            @strAllocateSQL = @strAllocateSQL OUTPUT

SELECT      @strAllocateSQL as N'@strAllocateSQL'

SELECT      'Return Value' = @return_value

GO

I get this error:

Msg 245, Level 16, State 1, Procedure GetInventory, Line 21
Conversion failed when converting the varchar value 'SELECT x.* FROM (SELECT b.HeatID,c.Name AS Customer,a.RodSize,a.HeatNumber,a.Grade,d.Name AS Vendor,
                  SUM(case when b.TrxType = 'R' then b.NoOfCoils else 0 end) As Received,
                  SUM(case when b.TrxType = 'S' then b.CoilsShipped else 0 end) As Shipped,
                  SUM(case when b.TrxType = 'A' then b.NoOfCoils else 0 end) As Allocated
                  ,SUM(case when b.TrxType = 'X' then b.NoOfCoils else 0 end) As Transferred
                  ,(Received-Shipped+Transferred) AS OnHand
                  ,(Received-Allocated+Transferred) AS Available
                  ,SUM(case when b.TrxType = 'R' then b.LbsRecd else 0 end) As Weight
                  ,case when Received>0 then cast(Weight/Received as int) else 0 end) AS AvgWt
                  from tblHeat AS a LEFT JOIN tblHeatTransaction AS b ON
                          a.ID = b.HeatID INNER JOIN
                          tblCustomer AS c ON a.CustomerNumber = c.CustomerNumber) as a INNER JOIN
                          tblVendor AS d ON a.VendorNumber = d.VendorNumber AND b.CustomerNumber = ' to data type int.

alter procedure GetInventory (@chkInventory int, @txtCustomerNumber varchar(max), @strGrade varchar(max), @strSecondaryRodSize varchar(max), @strRodSize varchar(max), @strAllocateSQL varchar(max) output)
as
 
 
set @strAllocateSQL = 'SELECT x.* FROM (SELECT b.HeatID,c.Name AS Customer,a.RodSize,a.HeatNumber,a.Grade,d.Name AS Vendor,
                  SUM(case when b.TrxType = ''R'' then b.NoOfCoils else 0 end) As Received,
                  SUM(case when b.TrxType = ''S'' then b.CoilsShipped else 0 end) As Shipped,
                  SUM(case when b.TrxType = ''A'' then b.NoOfCoils else 0 end) As Allocated
                  ,SUM(case when b.TrxType = ''X'' then b.NoOfCoils else 0 end) As Transferred
                  ,(Received-Shipped+Transferred) AS OnHand
                  ,(Received-Allocated+Transferred) AS Available
                  ,SUM(case when b.TrxType = ''R'' then b.LbsRecd else 0 end) As Weight
                  ,case when Received>0 then cast(Weight/Received as int) else 0 end) AS AvgWt
                  from tblHeat AS a LEFT JOIN tblHeatTransaction AS b ON
                          a.ID = b.HeatID INNER JOIN
                          tblCustomer AS c ON a.CustomerNumber = c.CustomerNumber) as a INNER JOIN
                          tblVendor AS d ON a.VendorNumber = d.VendorNumber'
 
                 if( @chkInventory = 1 ) -- then tsql has no THEN and no { 
                  begin
                      set @strAllocateSQL = @strAllocateSQL + ' AND b.CustomerNumber = ' + cast(@txtCustomerNumber as int) 
                      set @strAllocateSQL = @strAllocateSQL + ' AND a.Grade = ''' + @strGrade + ''' '
                                    
                      
					  if( @strSecondaryRodSize = null)
                         SET @strAllocateSQL = @strAllocateSQL + ' AND a.RodSize = ''' + @strRodSize + ''' '
                      else                          
                         SET @strAllocateSQL = @strAllocateSQL+ ' AND (a.RodSize = ''' + @strRodSize + ''' OR a.RodSize = ''' +  @strSecondaryRodSize + ''' )'
                        
                   end
 
                   set @strAllocateSQL =@strAllocateSQL + ' GROUP BY b.HeatID,c.Name,a.RodSize,a.HeatNumber,a.Grade,d.Name) AS x WHERE x.OnHand>0 '
 
 
return

Open in new window

0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22759258
You're CASTing your CustomerNumber as an integer ... you don't want to do that when assembling a string.  

Line 21 as posted above.
0
 

Author Comment

by:jvalescu
ID: 22759335
Not quite following you, but the CustomerNumber field is an int in the table.
0
 
LVL 16

Expert Comment

by:brad2575
ID: 22759465
change this line:

set @strAllocateSQL = @strAllocateSQL + ' AND b.CustomerNumber = ' + cast(@txtCustomerNumber as int)

to this:

set @strAllocateSQL = @strAllocateSQL + ' AND b.CustomerNumber = cast( ' + @txtCustomerNumber + 'as int) '


if you try to cast it outside the string it tries to add an int to a string and it doesnt like that.

If you do it this way it casts it correctly.
0
 

Author Comment

by:jvalescu
ID: 22759768
That fixed that error.  When I run the query with these parameters:

USE [OrderEntry_prodSQL]
GO

DECLARE      @return_value int,
            @strAllocateSQL varchar(max)

EXEC      @return_value = [dbo].[GetInventory]
            @chkInventory = 1,
            @txtCustomerNumber = N'10',
            @strGrade = N'1022',
            @strSecondaryRodSize = NULL,
            @strRodSize = N'7/32',
            @strAllocateSQL = @strAllocateSQL OUTPUT

SELECT      @strAllocateSQL as N'@strAllocateSQL'

SELECT      'Return Value' = @return_value

GO


My results are these:

strAllocateSQL    null

Return Value        0

I was expecting columns:
b.HeatID,Customer,a.RodSize,a.HeatNumber,a.Grade,Vendor,Received,Shipped,Allocated,Transferred,OnHand,
Available
0
 
LVL 16

Expert Comment

by:brad2575
ID: 22759808
you would have to say
EXEC(@strAllocateSQL )

and the SP would just return the recordset just as if you did a select statement inline for your code.



0
 

Author Comment

by:jvalescu
ID: 22759992
I added the EXEC at the bottom, but am still getting the same results.  I know I must be putting something in wrong.  I've included the SQL procedure as it stands.

USE [OrderEntry_prodSQL]
GO

DECLARE      @return_value int,
            @strAllocateSQL varchar(max)

EXEC      @return_value = [dbo].[GetInventory]
            @chkInventory = 1,
            @txtCustomerNumber = N'10',
            @strGrade = N'1022',
            @strSecondaryRodSize = NULL,
            @strRodSize = N'7/32',
            @strAllocateSQL = @strAllocateSQL OUTPUT

SELECT      @strAllocateSQL as N'@strAllocateSQL'

SELECT      'Return Value' = @return_value

GO

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[GetInventory] (@chkInventory int, @txtCustomerNumber varchar(max), @strGrade varchar(max), @strSecondaryRodSize varchar(max), @strRodSize varchar(max),@strAllocateSQL varchar(max) output)
as
 
 
 
set @strAllocateSQL = 'SELECT x.* FROM (SELECT b.HeatID,c.Name AS Customer,a.RodSize,a.HeatNumber,a.Grade,d.Name AS Vendor,
                  SUM(case when b.TrxType = ''R'' then b.NoOfCoils else 0 end) As Received,
                  SUM(case when b.TrxType = ''S'' then b.CoilsShipped else 0 end) As Shipped,
                  SUM(case when b.TrxType = ''A'' then b.NoOfCoils else 0 end) As Allocated
                  ,SUM(case when b.TrxType = ''X'' then b.NoOfCoils else 0 end) As Transferred
                  ,(Received-Shipped+Transferred) AS OnHand
                  ,(Received-Allocated+Transferred) AS Available
                  ,SUM(case when b.TrxType = ''R'' then b.LbsRecd else 0 end) As Weight
                  ,case when Received>0 then cast(Weight/Received as int) else 0 end) AS AvgWt
                  from tblHeat AS a LEFT JOIN tblHeatTransaction AS b ON
                          a.ID = b.HeatID INNER JOIN
                          tblCustomer AS c ON a.CustomerNumber = c.CustomerNumber) as a INNER JOIN
                          tblVendor AS d ON a.VendorNumber = d.VendorNumber'
 
                 if( @chkInventory = 1 ) -- then tsql has no THEN and no { 
                  begin
                      
				      set @strAllocateSQL = @strAllocateSQL + ' AND b.CustomerNumber = cast( ' + @txtCustomerNumber + 'as int) '
                      set @strAllocateSQL = @strAllocateSQL + ' AND a.Grade = ''' + @strGrade + ''' '
                                    
                      
					  if( @strSecondaryRodSize = null)
                         SET @strAllocateSQL = @strAllocateSQL + ' AND a.RodSize = ''' + @strRodSize + ''' '
                      else                          
                         SET @strAllocateSQL = @strAllocateSQL+ ' AND (a.RodSize = ''' + @strRodSize + ''' OR a.RodSize = ''' +  @strSecondaryRodSize + ''' )'
                        
                   end
 
                   set @strAllocateSQL =@strAllocateSQL + ' GROUP BY b.HeatID,c.Name,a.RodSize,a.HeatNumber,a.Grade,d.Name) AS x WHERE x.OnHand>0 '
 
 EXEC(@strAllocateSQL )
return

Open in new window

0
 
LVL 16

Assisted Solution

by:brad2575
brad2575 earned 200 total points
ID: 22760073
ok try this, I removed the output paramater, you are just automatically returning the executed recordset of the string variable.

Unless you wanted the sql string returned instead of a recordset?
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[zzzGetInventory] (
	@chkInventory int, @txtCustomerNumber varchar(max), @strGrade varchar(max), @strSecondaryRodSize varchar(max), @strRodSize varchar(max)
)
as
 
BEGIN
 
	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
	SET NOCOUNT ON;	
 
	DECLARE @strAllocateSQL as varchar(MAX)
 
	
 
	set @strAllocateSQL = 'SELECT x.* FROM (SELECT b.HeatID,c.Name AS Customer,a.RodSize,a.HeatNumber,a.Grade,d.Name AS Vendor,
                  SUM(case when b.TrxType = ''R'' then b.NoOfCoils else 0 end) As Received,
                  SUM(case when b.TrxType = ''S'' then b.CoilsShipped else 0 end) As Shipped,
                  SUM(case when b.TrxType = ''A'' then b.NoOfCoils else 0 end) As Allocated
                  ,SUM(case when b.TrxType = ''X'' then b.NoOfCoils else 0 end) As Transferred
                  ,(Received-Shipped+Transferred) AS OnHand
                  ,(Received-Allocated+Transferred) AS Available
                  ,SUM(case when b.TrxType = ''R'' then b.LbsRecd else 0 end) As Weight
                  ,case when Received>0 then cast(Weight/Received as int) else 0 end) AS AvgWt
                  from tblHeat AS a LEFT JOIN tblHeatTransaction AS b ON
                          a.ID = b.HeatID INNER JOIN
                          tblCustomer AS c ON a.CustomerNumber = c.CustomerNumber) as a INNER JOIN
                          tblVendor AS d ON a.VendorNumber = d.VendorNumber'
 
                 if( @chkInventory = 1 ) -- then tsql has no THEN and no { 
                  begin
                      
				      set @strAllocateSQL = @strAllocateSQL + ' AND b.CustomerNumber = cast( ' + @txtCustomerNumber + 'as int) '
                      set @strAllocateSQL = @strAllocateSQL + ' AND a.Grade = ''' + @strGrade + ''' '
                                    
                      
					  if( @strSecondaryRodSize = null)
                         SET @strAllocateSQL = @strAllocateSQL + ' AND a.RodSize = ''' + @strRodSize + ''' '
                      else                          
                         SET @strAllocateSQL = @strAllocateSQL+ ' AND (a.RodSize = ''' + @strRodSize + ''' OR a.RodSize = ''' +  @strSecondaryRodSize + ''' )'
                        
                   end
 
                   set @strAllocateSQL =@strAllocateSQL + ' GROUP BY b.HeatID,c.Name,a.RodSize,a.HeatNumber,a.Grade,d.Name) AS x WHERE x.OnHand>0 '
 
	EXEC(@strAllocateSQL )
 
END

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22760498
2 things 2 change:


if( @strSecondaryRodSize IS null)
 
the = NULL will not work
 
this this should be a better result:
 
 
DECLARE      @return_value int,
            @strAllocateSQL varchar(max)
 
EXEC      @return_value = [dbo].[GetInventory]
            @chkInventory = 1,
            @txtCustomerNumber = N'10',
            @strGrade = N'1022',
            @strSecondaryRodSize = NULL,
            @strRodSize = N'7/32',
            @strAllocateSQL = @strAllocateSQL OUTPUT
 
SELECT      @strAllocateSQL SQL, @return_value return_value
-- to actually run the SQL statement:
EXEC (@strAllocateSQL)

Open in new window

0
 

Author Comment

by:jvalescu
ID: 22761085
We are on the cusp of licking this.  I entered the SQL code in the snippet below and ran it:

USE [OrderEntry_prodSQL]
GO

DECLARE      @return_value int

EXEC      @return_value = [dbo].[zzzGetInventory]
            @chkInventory = 1,
            @txtCustomerNumber = N'10',
            @strGrade = N'1022',
            @strSecondaryRodSize = NULL,
            @strRodSize = N'7/32'

SELECT      'Return Value' = @return_value

GO

It returned this error:

Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'from'.

(1 row(s) affected)


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[zzzGetInventory] (
	@chkInventory int, @txtCustomerNumber varchar(max), @strGrade varchar(max), @strSecondaryRodSize varchar(max), @strRodSize varchar(max)
)
as
 
BEGIN
 
	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
	SET NOCOUNT ON;	
 
	DECLARE      @return_value int,
            @strAllocateSQL varchar(max)
 
 
	
 
	set @strAllocateSQL = 'SELECT x.* FROM (SELECT b.HeatID,c.Name AS Customer,a.RodSize,a.HeatNumber,a.Grade,d.Name AS Vendor,
                  SUM(case when b.TrxType = ''R'' then b.NoOfCoils else 0 end) As Received,
                  SUM(case when b.TrxType = ''S'' then b.CoilsShipped else 0 end) As Shipped,
                  SUM(case when b.TrxType = ''A'' then b.NoOfCoils else 0 end) As Allocated
                  ,SUM(case when b.TrxType = ''X'' then b.NoOfCoils else 0 end) As Transferred
                  ,(Received-Shipped+Transferred) AS OnHand
                  ,(Received-Allocated+Transferred) AS Available
                  ,SUM(case when b.TrxType = ''R'' then b.LbsRecd else 0 end) As Weight
                  ,case when Received>0 then cast(Weight/Received as int) else 0 end) AS AvgWt
                  from tblHeat AS a LEFT JOIN tblHeatTransaction AS b ON
                          a.ID = b.HeatID INNER JOIN
                          tblCustomer AS c ON a.CustomerNumber = c.CustomerNumber) as a INNER JOIN
                          tblVendor AS d ON a.VendorNumber = d.VendorNumber'
 
                 if( @chkInventory = 1 ) -- then tsql has no THEN and no { 
                  begin
                      
				      set @strAllocateSQL = @strAllocateSQL + ' AND b.CustomerNumber = cast( ' + @txtCustomerNumber + 'as int) '
                      set @strAllocateSQL = @strAllocateSQL + ' AND a.Grade = ''' + @strGrade + ''' '
                                    
                      
					  if( @strSecondaryRodSize IS null)
 
                         SET @strAllocateSQL = @strAllocateSQL + ' AND a.RodSize = ''' + @strRodSize + ''' '
                      else                          
                         SET @strAllocateSQL = @strAllocateSQL+ ' AND (a.RodSize = ''' + @strRodSize + ''' OR a.RodSize = ''' +  @strSecondaryRodSize + ''' )'
                        
                   end
 
                   set @strAllocateSQL =@strAllocateSQL + ' GROUP BY b.HeatID,c.Name,a.RodSize,a.HeatNumber,a.Grade,d.Name) AS x WHERE x.OnHand>0 '
 
    SELECT      @strAllocateSQL SQL, @return_value return_value
 
	EXEC(@strAllocateSQL )
 
END

Open in new window

0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 300 total points
ID: 22761155
I had something little detail more:
create procedure [dbo].[zzzGetInventory] (
	@chkInventory int, @txtCustomerNumber varchar(max), @strGrade varchar(max), @strSecondaryRodSize varchar(max), @strRodSize varchar(max)
)
as
 
BEGIN
 
	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
	SET NOCOUNT ON;	
 
	DECLARE      @return_value int,
            @strAllocateSQL varchar(max)
 
 
	
 
	set @strAllocateSQL = 'SELECT x.* FROM (SELECT b.HeatID,c.Name AS Customer,a.RodSize,a.HeatNumber,a.Grade,d.Name AS Vendor,
                  SUM(case when b.TrxType = ''R'' then b.NoOfCoils else 0 end) As Received,
                  SUM(case when b.TrxType = ''S'' then b.CoilsShipped else 0 end) As Shipped,
                  SUM(case when b.TrxType = ''A'' then b.NoOfCoils else 0 end) As Allocated
                  ,SUM(case when b.TrxType = ''X'' then b.NoOfCoils else 0 end) As Transferred
                  ,(Received-Shipped+Transferred) AS OnHand
                  ,(Received-Allocated+Transferred) AS Available
                  ,SUM(case when b.TrxType = ''R'' then b.LbsRecd else 0 end) As Weight
                  ,case when Received>0 then cast(Weight/Received as int) else 0 end) AS AvgWt
                  from tblHeat AS a LEFT JOIN tblHeatTransaction AS b ON
                          a.ID = b.HeatID INNER JOIN
                          tblCustomer AS c ON a.CustomerNumber = c.CustomerNumber) as a INNER JOIN
                          tblVendor AS d ON a.VendorNumber = d.VendorNumber
                   WHERE 1 = 1 '
 
                 if( @chkInventory = 1 )  
                  begin
                     set @strAllocateSQL = @strAllocateSQL + ' AND b.CustomerNumber = cast( ' + @txtCustomerNumber + 'as int) '
                     set @strAllocateSQL = @strAllocateSQL + ' AND a.Grade = ''' + @strGrade + ''' '
                                    
                      
			if( @strSecondaryRodSize IS null)
 
                         SET @strAllocateSQL = @strAllocateSQL + ' AND a.RodSize = ''' + @strRodSize + ''' '
                      else                          
                         SET @strAllocateSQL = @strAllocateSQL+ ' AND (a.RodSize = ''' + @strRodSize + ''' OR a.RodSize = ''' +  @strSecondaryRodSize + ''' )'
                        
                   end
 
                   set @strAllocateSQL =@strAllocateSQL + ' GROUP BY b.HeatID,c.Name,a.RodSize,a.HeatNumber,a.Grade,d.Name) AS x WHERE x.OnHand>0 '
 
    SELECT      @strAllocateSQL SQL, @return_value return_value
 
	EXEC(@strAllocateSQL )
 
END

Open in new window

0
 

Author Comment

by:jvalescu
ID: 22761611
Replaced with your latest code and got the same error:

Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'from'.

(1 row(s) affected)



USE [OrderEntry_prodSQL]
GO

DECLARE      @return_value int

EXEC      @return_value = [dbo].[zzzGetInventory]
            @chkInventory = 1,
            @txtCustomerNumber = N'10',
            @strGrade = N'1022',
            @strSecondaryRodSize = NULL,
            @strRodSize = N'7/32'

SELECT      'Return Value' = @return_value

GO

alter procedure [dbo].[zzzGetInventory] (
	@chkInventory int, @txtCustomerNumber varchar(max), @strGrade varchar(max), @strSecondaryRodSize varchar(max), @strRodSize varchar(max)
)
as
 
BEGIN
 
	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
	SET NOCOUNT ON;	
 
	DECLARE      @return_value int,
            @strAllocateSQL varchar(max)
 
 
	
 
	set @strAllocateSQL = 'SELECT x.* FROM (SELECT b.HeatID,c.Name AS Customer,a.RodSize,a.HeatNumber,a.Grade,d.Name AS Vendor,
                  SUM(case when b.TrxType = ''R'' then b.NoOfCoils else 0 end) As Received,
                  SUM(case when b.TrxType = ''S'' then b.CoilsShipped else 0 end) As Shipped,
                  SUM(case when b.TrxType = ''A'' then b.NoOfCoils else 0 end) As Allocated
                  ,SUM(case when b.TrxType = ''X'' then b.NoOfCoils else 0 end) As Transferred
                  ,(Received-Shipped+Transferred) AS OnHand
                  ,(Received-Allocated+Transferred) AS Available
                  ,SUM(case when b.TrxType = ''R'' then b.LbsRecd else 0 end) As Weight
                  ,case when Received>0 then cast(Weight/Received as int) else 0 end) AS AvgWt
                  from tblHeat AS a LEFT JOIN tblHeatTransaction AS b ON
                          a.ID = b.HeatID INNER JOIN
                          tblCustomer AS c ON a.CustomerNumber = c.CustomerNumber) as a INNER JOIN
                          tblVendor AS d ON a.VendorNumber = d.VendorNumber
                   WHERE 1 = 1 '
 
                 if( @chkInventory = 1 )  
                  begin
                     set @strAllocateSQL = @strAllocateSQL + ' AND b.CustomerNumber = cast( ' + @txtCustomerNumber + 'as int) '
                     set @strAllocateSQL = @strAllocateSQL + ' AND a.Grade = ''' + @strGrade + ''' '
                                    
                      
			if( @strSecondaryRodSize IS null)
 
                         SET @strAllocateSQL = @strAllocateSQL + ' AND a.RodSize = ''' + @strRodSize + ''' '
                      else                          
                         SET @strAllocateSQL = @strAllocateSQL+ ' AND (a.RodSize = ''' + @strRodSize + ''' OR a.RodSize = ''' +  @strSecondaryRodSize + ''' )'
                        
                   end
 
                   set @strAllocateSQL =@strAllocateSQL + ' GROUP BY b.HeatID,c.Name,a.RodSize,a.HeatNumber,a.Grade,d.Name) AS x WHERE x.OnHand>0 '
 
    SELECT      @strAllocateSQL SQL, @return_value return_value
 
	EXEC(@strAllocateSQL )
 
END

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22761769
remove the " ) as a " here:
 tblCustomer AS c ON a.CustomerNumber = c.CustomerNumber) as a INNER JOIN

ie should be:
 tblCustomer AS c ON a.CustomerNumber = c.CustomerNumber  INNER JOIN
0
 

Author Comment

by:jvalescu
ID: 22761998
Same thing.

Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'from'.

(1 row(s) affected)
alter procedure [dbo].[zzzGetInventory] (
	@chkInventory int, @txtCustomerNumber varchar(max), @strGrade varchar(max), @strSecondaryRodSize varchar(max), @strRodSize varchar(max)
)
as
 
BEGIN
 
	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
	SET NOCOUNT ON;	
 
	DECLARE      @return_value int,
            @strAllocateSQL varchar(max)
 
 
	
 
	set @strAllocateSQL = 'SELECT x.* FROM (SELECT b.HeatID,c.Name AS Customer,a.RodSize,a.HeatNumber,a.Grade,d.Name AS Vendor,
                  SUM(case when b.TrxType = ''R'' then b.NoOfCoils else 0 end) As Received,
                  SUM(case when b.TrxType = ''S'' then b.CoilsShipped else 0 end) As Shipped,
                  SUM(case when b.TrxType = ''A'' then b.NoOfCoils else 0 end) As Allocated
                  ,SUM(case when b.TrxType = ''X'' then b.NoOfCoils else 0 end) As Transferred
                  ,(Received-Shipped+Transferred) AS OnHand
                  ,(Received-Allocated+Transferred) AS Available
                  ,SUM(case when b.TrxType = ''R'' then b.LbsRecd else 0 end) As Weight
                  ,case when Received>0 then cast(Weight/Received as int) else 0 end) AS AvgWt
                  from tblHeat AS a LEFT JOIN tblHeatTransaction AS b ON
                          a.ID = b.HeatID INNER JOIN
                          tblCustomer AS c ON a.CustomerNumber = c.CustomerNumber  INNER JOIN
                          tblVendor AS d ON a.VendorNumber = d.VendorNumber
                   WHERE 1 = 1 '
 
                 if( @chkInventory = 1 )  
                  begin
                     set @strAllocateSQL = @strAllocateSQL + ' AND b.CustomerNumber = cast( ' + @txtCustomerNumber + 'as int) '
                     set @strAllocateSQL = @strAllocateSQL + ' AND a.Grade = ''' + @strGrade + ''' '
                                    
                      
			if( @strSecondaryRodSize IS null)
 
                         SET @strAllocateSQL = @strAllocateSQL + ' AND a.RodSize = ''' + @strRodSize + ''' '
                      else                          
                         SET @strAllocateSQL = @strAllocateSQL+ ' AND (a.RodSize = ''' + @strRodSize + ''' OR a.RodSize = ''' +  @strSecondaryRodSize + ''' )'
                        
                   end
 
                   set @strAllocateSQL =@strAllocateSQL + ' GROUP BY b.HeatID,c.Name,a.RodSize,a.HeatNumber,a.Grade,d.Name) AS x WHERE x.OnHand>0 '
 
    SELECT      @strAllocateSQL SQL, @return_value return_value
 
	EXEC(@strAllocateSQL )
 
END

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22762048
I see another one:

,case when Received>0 then cast(Weight/Received as int) else 0 end) AS AvgWt

is either missing the SUM(  for example before the CASE, or the ) at the end is too much
0
 

Author Comment

by:jvalescu
ID: 22762345
Ok, I removed the other ")" from that line and ran it, and got this result:

Msg 207, Level 16, State 1, Line 6
Invalid column name 'Received'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'Shipped'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'Transferred'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Received'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Allocated'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Transferred'.
Msg 207, Level 16, State 1, Line 9
Invalid column name 'Received'.
Msg 207, Level 16, State 1, Line 9
Invalid column name 'Weight'.
Msg 207, Level 16, State 1, Line 9
Invalid column name 'Received'.

(1 row(s) affected)

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[zzzGetInventory] (
	@chkInventory int, @txtCustomerNumber varchar(max), @strGrade varchar(max), @strSecondaryRodSize varchar(max), @strRodSize varchar(max)
)
as
 
BEGIN
 
	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
	SET NOCOUNT ON;	
 
	DECLARE      @return_value int,
            @strAllocateSQL varchar(max)
 
 
	
 
	set @strAllocateSQL = 'SELECT x.* FROM (SELECT b.HeatID,c.Name AS Customer,a.RodSize,a.HeatNumber,a.Grade,d.Name AS Vendor,
                  SUM(case when b.TrxType = ''R'' then b.NoOfCoils else 0 end) As Received,
                  SUM(case when b.TrxType = ''S'' then b.CoilsShipped else 0 end) As Shipped,
                  SUM(case when b.TrxType = ''A'' then b.NoOfCoils else 0 end) As Allocated
                  ,SUM(case when b.TrxType = ''X'' then b.NoOfCoils else 0 end) As Transferred
                  ,(Received-Shipped+Transferred) AS OnHand
                  ,(Received-Allocated+Transferred) AS Available
                  ,SUM(case when b.TrxType = ''R'' then b.LbsRecd else 0 end) As Weight
                  ,case when Received>0 then cast(Weight/Received as int) else 0 end AS AvgWt
                  from tblHeat AS a LEFT JOIN tblHeatTransaction AS b ON
                          a.ID = b.HeatID INNER JOIN
                          tblCustomer AS c ON a.CustomerNumber = c.CustomerNumber  INNER JOIN
                          tblVendor AS d ON a.VendorNumber = d.VendorNumber
                   WHERE 1 = 1 '
 
                 if( @chkInventory = 1 )  
                  begin
                     set @strAllocateSQL = @strAllocateSQL + ' AND b.CustomerNumber = cast( ' + @txtCustomerNumber + 'as int) '
                     set @strAllocateSQL = @strAllocateSQL + ' AND a.Grade = ''' + @strGrade + ''' '
                                    
                      
			if( @strSecondaryRodSize IS null)
 
                         SET @strAllocateSQL = @strAllocateSQL + ' AND a.RodSize = ''' + @strRodSize + ''' '
                      else                          
                         SET @strAllocateSQL = @strAllocateSQL+ ' AND (a.RodSize = ''' + @strRodSize + ''' OR a.RodSize = ''' +  @strSecondaryRodSize + ''' )'
                        
                   end
 
                   set @strAllocateSQL =@strAllocateSQL + ' GROUP BY b.HeatID,c.Name,a.RodSize,a.HeatNumber,a.Grade,d.Name) AS x WHERE x.OnHand>0 '
 
    SELECT      @strAllocateSQL SQL, @return_value return_value
 
	EXEC(@strAllocateSQL )
 
END

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22762402
I think it get's time for you to do some debugging yourself, don't you think so?

change:
EXEC(@strAllocateSQL )

into
PRINT @strAllocateSQL

and look at the generated sql statement yourself...
resp copy/paste it to a new query window, and see/correct the problems, and as you correct them there, correct them in the original code.


0
 

Author Comment

by:jvalescu
ID: 22762713
I agree, Angel and I would not be asking these questions if I could figure it out myself.  I've looked at it so much that it really needs a keener eye, hence the expert opinions.  I don't understand why it is reporting the column names as invalid.

BTW:  I've enclosed the SQL statement from the PRINT.

PS:  I'll award you your points next post.  Thanks!

SELECT x.* FROM (SELECT b.HeatID,c.Name AS Customer,a.RodSize,a.HeatNumber,a.Grade,d.Name AS Vendor,                    SUM(case when b.TrxType = 'R' then b.NoOfCoils else 0 end) As Received,                    SUM(case when b.TrxType = 'S' then b.CoilsShipped else 0 end) As Shipped,                    SUM(case when b.TrxType = 'A' then b.NoOfCoils else 0 end) As Allocated                    ,SUM(case when b.TrxType = 'X' then b.NoOfCoils else 0 end) As Transferred                    ,(Received-Shipped+Transferred) AS OnHand                    ,(Received-Allocated+Transferred) AS Available                    ,SUM(case when b.TrxType = 'R' then b.LbsRecd else 0 end) As Weight                    ,case when Received>0 then cast(Weight/Received as int) else 0 end AS AvgWt                    from tblHeat AS a LEFT JOIN tblHeatTransaction AS b ON                            a.ID = b.HeatID INNER JOIN                            tblCustomer AS c ON a.CustomerNumber = c.CustomerNumber  INNER JOIN                            tblVendor AS d ON a.VendorNumber = d.VendorNumber                     WHERE 1 = 1  AND b.CustomerNumber = cast( 10as int)  AND a.Grade = '1022'  AND a.RodSize = '7/32'  GROUP BY b.HeatID,c.Name,a.RodSize,a.HeatNumber,a.Grade,d.Name) AS x WHERE x.OnHand>0 

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22765008
I see the problem: actually, you cannot reuse the column alias name directly on the same level of the query.

so, you either need to repeat the expression, or put another subquery..
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question