Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

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

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
jvalescu
Asked:
jvalescu
  • 11
  • 8
  • 5
  • +2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Daniel WilsonCommented:
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
 
mikainzCommented:
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
Technology Partners: 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!

 
Daniel WilsonCommented:
OK, Angel ... you beat me & found the 3rd error at the same time!  Congrats!
0
 
brad2575Commented:
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
 
brad2575Commented:
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
 
jvalescuAuthor Commented:
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
 
Daniel WilsonCommented:
you need SET at the start of all your variable assignments.  See AngelIII's code.
0
 
jvalescuAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry:

if( @strSecondaryRodSize =''


must be :

if( @strSecondaryRodSize ='')
0
 
jvalescuAuthor Commented:
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
 
Daniel WilsonCommented:
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
 
jvalescuAuthor Commented:
Not quite following you, but the CustomerNumber field is an int in the table.
0
 
brad2575Commented:
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
 
jvalescuAuthor Commented:
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
 
brad2575Commented:
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
 
jvalescuAuthor Commented:
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
 
brad2575Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
jvalescuAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
jvalescuAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
jvalescuAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
jvalescuAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
jvalescuAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 11
  • 8
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now