Solved

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

Posted on 2008-10-20
29
308 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]
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
 
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 142

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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 142

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 142

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 142

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 142

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 142

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 142

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

862 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

29 Experts available now in Live!

Get 1:1 Help Now