Solved

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

Posted on 2008-10-20
29
304 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
  • 11
  • 8
  • 5
  • +2
29 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 32

Expert Comment

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

Expert Comment

by:brad2575
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
you need SET at the start of all your variable assignments.  See AngelIII's code.
0
 

Author Comment

by:jvalescu
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
sorry:

if( @strSecondaryRodSize =''


must be :

if( @strSecondaryRodSize ='')
0
 

Author Comment

by:jvalescu
Comment Utility
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
Comment Utility
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
Comment Utility
Not quite following you, but the CustomerNumber field is an int in the table.
0
 
LVL 16

Expert Comment

by:brad2575
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:jvalescu
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 300 total points
Comment Utility
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
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL - Output to columns equally. 5 45
Stored Procedure error 45 39
sql calculate reminders 11 56
SQL Server Degrading on Write 13 60
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now