Sproc error in having cluase

That's because @input_days is an int.  

Try this:

CREATE PROCEDURE csspmt_cf.sproc_homePage
(@input_css 	VARCHAR(25),
@input_days 	int,
@input_ranges 	int,
@step_range 	int,
@pend_sort 	varchar(50),
@prog_sort 	varchar(50),
@held_sort 	varchar(50),
@due_sort 	varchar(50),
@furture_sort 	varchar(50),
@aging_sort 	varchar(50),
@follow_sort 	varchar(50),
@comp_sort 	varchar(50)
 
)
 
 AS
 
declare
 @SQL_pending 	varchar (1000),
 @SQL_prog		varchar (1000),
 @SQL_held	 	varchar (1000),
 @SQL_heldOld	 	varchar (1000),
 @SQL_heldOutput 	varchar (1000),
 @SQL_due	 	varchar (1000),
 @SQL_furture	 	varchar (5000),
 @SQL_aging	 	varchar (1000),
 @SQL_follow	 	varchar (1000),
 @SQL_comp		varchar (1000)
 
 
 
--AgingNonOrders
set @SQL_AGING = '
SELECT PROJECT_ACTIVITY.PROJECT_ID, PMT_TABS.TAB_ID,TAB_NAME, TAB_TYPE, CUST_NAME, PROJECT_DESC,TAB_TYPE,
DATEDIFF(DAY,MIN(TAB_RECD_DT),getDate())- DATEDIFF(ww,MIN(TAB_RECD_DT),getDate())*2 as aging
FROM PMT_USERS INNER JOIN PROJECT_ACTIVITY 
ON PROJECT_ACTIVITY.PROJECT_OWNER =  PMT_USERS.PMT_USER_ID
INNER JOIN  PMT_CUSTOMERS
ON PMT_CUSTOMERS.PROJECT_ID = PROJECT_ACTIVITY.PROJECT_ID
INNER JOIN PMT_TABS
ON PMT_TABS.PROJECT_ID = PROJECT_ACTIVITY.PROJECT_ID
join OPTIONS_ACT_TYPE
on OPTIONS_ACT_TYPE.type = PMT_TABS.TAB_TYPE
WHERE order_req = '  char(39) 'n'  char(39) '
AND
((TAB_OWNER = ' char(39) @input_css char(39) ' and (OWNER_STATUS ='  char(39)  'Sent to Remote Support'  char(39) ' 
					  	OR OWNER_STATUS = '  char(39) 'Open / In Progress'  char(39) '))
OR
(REMOTE_OWNER ='  char(39)  @input_css  char(39) ' and REMOTE_STATUS = '  char(39) 'Open / In Progress'  char(39) '))
AND OVERALL_STATUS <> '  char(39) 'Canceled'  char(39) ' AND OVERALL_STATUS <> '  char(39) 'ON HOLD'  char(39) ' AND OVERALL_STATUS <> '  char(39) 'Returned to Sales'  char(39) ' 
GROUP BY PROJECT_ACTIVITY.PROJECT_ID, PMT_TABS.TAB_ID,TAB_NAME, TAB_TYPE, CUST_NAME, PROJECT_DESC
 
 
HAVING  DATEDIFF(DAY,MIN(TAB_RECD_DT), getDate() ) - DATEDIFF(ww,MIN(TAB_RECD_DT), getDate() ) * 2  >= '   ltrim(str(@input_days))  '
 
ORDER BY   '  @aging_sort
 
EXEC (@SQL_AGING)
 
GO

Select allOpen in new window

Hello Wirt,


try this

Regards,

Aneesh

CREATE PROCEDURE csspmt_cf.sproc_homePage
(@input_css       VARCHAR(25),
@input_days       int,
@input_ranges       int,
@step_range       int,
@pend_sort       varchar(50),
@prog_sort       varchar(50),
@held_sort       varchar(50),
@due_sort       varchar(50),
@furture_sort       varchar(50),
@aging_sort       varchar(50),
@follow_sort       varchar(50),
@comp_sort       varchar(50)
 
)
 
 AS
 
declare
 @SQL_pending       varchar (1000),
 @SQL_prog            varchar (1000),
 @SQL_held             varchar (1000),
 @SQL_heldOld             varchar (1000),
 @SQL_heldOutput       varchar (1000),
 @SQL_due             varchar (1000),
 @SQL_furture             varchar (5000),
 @SQL_aging             varchar (1000),
 @SQL_follow             varchar (1000),
 @SQL_comp            varchar (1000)
 
 
 
--AgingNonOrders
set @SQL_AGING = '
SELECT PROJECT_ACTIVITY.PROJECT_ID, PMT_TABS.TAB_ID,TAB_NAME, TAB_TYPE, CUST_NAME, PROJECT_DESC,TAB_TYPE,
DATEDIFF(DAY,MIN(TAB_RECD_DT),getDate())- DATEDIFF(ww,MIN(TAB_RECD_DT),getDate())*2 as aging
FROM PMT_USERS INNER JOIN PROJECT_ACTIVITY 
ON PROJECT_ACTIVITY.PROJECT_OWNER =  PMT_USERS.PMT_USER_ID
INNER JOIN  PMT_CUSTOMERS
ON PMT_CUSTOMERS.PROJECT_ID = PROJECT_ACTIVITY.PROJECT_ID
INNER JOIN PMT_TABS
ON PMT_TABS.PROJECT_ID = PROJECT_ACTIVITY.PROJECT_ID
join OPTIONS_ACT_TYPE
on OPTIONS_ACT_TYPE.type = PMT_TABS.TAB_TYPE
WHERE order_req = '+ char(39)+'n'+ char(39)+'
AND
((TAB_OWNER = '+char(39)+@input_css+char(39)+' and (OWNER_STATUS ='+ char(39)+ 'Sent to Remote Support'+ char(39)+' 
                                      OR OWNER_STATUS = '+ char(39)+'Open / In Progress'+ char(39)+'))
OR
(REMOTE_OWNER ='+ char(39)+ @input_css+ char(39)+' and REMOTE_STATUS = '+ char(39)+'Open / In Progress'+ char(39)+'))
AND OVERALL_STATUS <> '+ char(39)+'Canceled'+ char(39)+' AND OVERALL_STATUS <> '+ char(39)+'ON HOLD'+ char(39)+' AND OVERALL_STATUS <> '+ char(39)+'Returned to Sales'+ char(39)+' 
GROUP BY PROJECT_ACTIVITY.PROJECT_ID, PMT_TABS.TAB_ID,TAB_NAME, TAB_TYPE, CUST_NAME, PROJECT_DESC
 
 
HAVING  DATEDIFF(DAY,MIN(TAB_RECD_DT), getDate() ) - DATEDIFF(ww,MIN(TAB_RECD_DT), getDate() ) * 2  >= ' + CAST(@input_days as varchar) +'
 
ORDER BY   '+ @aging_sort
 
EXEC (@SQL_AGING)
 
GO

Select allOpen in new window

You need to convert int to varchar to append it to sql string.

Also, if you print out your string, you'll often get clues to your problems as shown with Print.  Take out when it is correct.

Private Sub DeletetempTable()
 Dim SqlText As String
 
 Check if temp table exists in sysobjects,if present then drop it
 SqlText = "if object_id('tempdb..#temptablename') is not null
drop table #temptablename"
          
                
                DBHandle.BeginTrans
                DBHandle.Execute (SqlText)
                DBHandle.CommitTrans
              
End Sub
 
 
 
 
 
CREATE PROCEDURE csspmt_cf.sproc_homePage
(@input_css     VARCHAR(25),
@input_days     int,
@input_ranges   int,
@step_range     int,
@pend_sort      varchar(50),
@prog_sort      varchar(50),
@held_sort      varchar(50),
@due_sort       varchar(50),
@furture_sort   varchar(50),
@aging_sort     varchar(50),
@follow_sort    varchar(50),
@comp_sort      varchar(50)
 
)
 
 AS
 
declare
 @SQL_pending   varchar (1000),
 @SQL_prog              varchar (1000),
 @SQL_held              varchar (1000),
 @SQL_heldOld           varchar (1000),
 @SQL_heldOutput        varchar (1000),
 @SQL_due               varchar (1000),
 @SQL_furture           varchar (5000),
 @SQL_aging             varchar (1000),
 @SQL_follow            varchar (1000),
 @SQL_comp              varchar (1000)
 
 
 
--AgingNonOrders
set @SQL_AGING = '
SELECT PROJECT_ACTIVITY.PROJECT_ID, PMT_TABS.TAB_ID,TAB_NAME, TAB_TYPE, CUST_NAME, PROJECT_DESC,TAB_TYPE,
DATEDIFF(DAY,MIN(TAB_RECD_DT),getDate())- DATEDIFF(ww,MIN(TAB_RECD_DT),getDate())*2 as aging
FROM PMT_USERS INNER JOIN PROJECT_ACTIVITY 
ON PROJECT_ACTIVITY.PROJECT_OWNER =  PMT_USERS.PMT_USER_ID
INNER JOIN  PMT_CUSTOMERS
ON PMT_CUSTOMERS.PROJECT_ID = PROJECT_ACTIVITY.PROJECT_ID
INNER JOIN PMT_TABS
ON PMT_TABS.PROJECT_ID = PROJECT_ACTIVITY.PROJECT_ID
join OPTIONS_ACT_TYPE
on OPTIONS_ACT_TYPE.type = PMT_TABS.TAB_TYPE
WHERE order_req = '+ char(39)+'n'+ char(39)+'
AND
((TAB_OWNER = '+char(39)+@input_css+char(39)+' and (OWNER_STATUS ='+ char(39)+ 'Sent to Remote Support'+ char(39)+' 
                                                OR OWNER_STATUS = '+ char(39)+'Open / In Progress'+ char(39)+'))
OR
(REMOTE_OWNER ='+ char(39)+ @input_css+ char(39)+' and REMOTE_STATUS = '+ char(39)+'Open / In Progress'+ char(39)+'))
AND OVERALL_STATUS <> '+ char(39)+'Canceled'+ char(39)+' AND OVERALL_STATUS <> '+ char(39)+'ON HOLD'+ char(39)+' AND OVERALL_STATUS <> '+ char(39)+'Returned to Sales'+ char(39)+' 
GROUP BY PROJECT_ACTIVITY.PROJECT_ID, PMT_TABS.TAB_ID,TAB_NAME, TAB_TYPE, CUST_NAME, PROJECT_DESC
 
 
HAVING  DATEDIFF(DAY,MIN(TAB_RECD_DT), getDate() ) - DATEDIFF(ww,MIN(TAB_RECD_DT), getDate() ) * 2  >= ' + CONVERT(varchar,@input_days) +'
 
ORDER BY   '+ @aging_sort
 
PRINT @SQL_AGING
EXEC (@SQL_AGING)
 
GO

Select allOpen in new window

All these make it not crash but now the having clause logic isnt working...?

Well that's a different issue since your question was related to the syntax error.

But generally a HAVING is used to apply criteria to an aggregate.  

example:

having sum(SomeField)>1

You aren't doing anything on an aggregate so a HAVING is unnecessary and it should be in a WHERE clause.

please send the output of the print command

Temporarily comment out the EXEC line and the bottom and put in the PRINT command as I suggested.

As Brandon has pointed out, your Having clause should be part of your WHERE clause.  The way I debug my SQL syntax is to paste the output of the PRINT command into a new query window in SQLServer Management Studio.

forgive me, this is my first sprcoc... How do i see the printed output?

Also i cant put the Having into the where here is my example:

SELECT PROJECT_ACTIVITY.PROJECT_ID, PMT_TABS.TAB_ID,TAB_NAME, TAB_TYPE, CUST_NAME, PROJECT_DESC,TAB_TYPE,
DATEDIFF(DAY,MIN(TAB_RECD_DT),getDate())- DATEDIFF(ww,MIN(TAB_RECD_DT),getDate())*2 as aging
FROM PMT_USERS INNER JOIN PROJECT_ACTIVITY
ON PROJECT_ACTIVITY.PROJECT_OWNER = PMT_USERS.PMT_USER_ID
INNER JOIN PMT_CUSTOMERS
ON PMT_CUSTOMERS.PROJECT_ID = PROJECT_ACTIVITY.PROJECT_ID
INNER JOIN PMT_TABS
ON PMT_TABS.PROJECT_ID = PROJECT_ACTIVITY.PROJECT_ID
join OPTIONS_ACT_TYPE
on OPTIONS_ACT_TYPE.type = PMT_TABS.TAB_TYPE
WHERE
DATEDIFF(DAY,MIN(TAB_RECD_DT), getDate() ) - DATEDIFF(ww,MIN(TAB_RECD_DT), getDate() ) * 2 >= 231
AND order_req = 'n'
AND
((TAB_OWNER = 'jw8101' and (OWNER_STATUS ='Sent to Remote Support'
OR OWNER_STATUS = 'Open / In Progress'))
OR
(REMOTE_OWNER ='jw8101' and REMOTE_STATUS = 'Open / In Progress'))
AND OVERALL_STATUS <> 'Canceled' AND OVERALL_STATUS <> 'ON HOLD' AND OVERALL_STATUS <> 'Returned to Sales'
GROUP BY PROJECT_ACTIVITY.PROJECT_ID, PMT_TABS.TAB_ID,TAB_NAME, TAB_TYPE, CUST_NAME, PROJECT_DESC

 
 
ERROR:

Msg 147, Level 15, State 1, Line 12
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Oh, sorry.  
You'd only see the output of the PRINT if you are running the sproc from SQL Management Studio.

Going back to your original query, you ARE using an aggregate and you will want the HAVING clause because you are using the MIN.  However, in order to use the having clause, it must also be in the GROUP BY clause.

Do you have access to SQL Server Management studio?  

Since we don't have your table structures, we have to make our best guesses at correct syntax.  Try the query below and get it working.  THen translate it back into your code.

SELECT PROJECT_ACTIVITY.PROJECT_ID, PMT_TABS.TAB_ID,TAB_NAME, TAB_TYPE, CUST_NAME, PROJECT_DESC,TAB_TYPE,
DATEDIFF(DAY,MIN(TAB_RECD_DT),getDate())- DATEDIFF(ww,MIN(TAB_RECD_DT),getDate())*2 as aging
FROM PMT_USERS INNER JOIN PROJECT_ACTIVITY
ON PROJECT_ACTIVITY.PROJECT_OWNER = PMT_USERS.PMT_USER_ID
INNER JOIN PMT_CUSTOMERS
ON PMT_CUSTOMERS.PROJECT_ID = PROJECT_ACTIVITY.PROJECT_ID
INNER JOIN PMT_TABS
ON PMT_TABS.PROJECT_ID = PROJECT_ACTIVITY.PROJECT_ID
join OPTIONS_ACT_TYPE
on OPTIONS_ACT_TYPE.type = PMT_TABS.TAB_TYPE
WHERE
AND order_req = 'n'
AND
((TAB_OWNER = 'jw8101' and (OWNER_STATUS ='Sent to Remote Support'
OR OWNER_STATUS = 'Open / In Progress'))
OR
(REMOTE_OWNER ='jw8101' and REMOTE_STATUS = 'Open / In Progress'))
AND OVERALL_STATUS <> 'Canceled' AND OVERALL_STATUS <> 'ON HOLD' AND OVERALL_STATUS <> 'Returned to Sales'
GROUP BY PROJECT_ACTIVITY.PROJECT_ID, PMT_TABS.TAB_ID,TAB_NAME, TAB_TYPE, CUST_NAME, PROJECT_DESC ,
DATEDIFF(DAY,MIN(TAB_RECD_DT), getDate() ) - DATEDIFF(ww,MIN(TAB_RECD_DT), getDate() ) * 2 
HAVING DATEDIFF(DAY,MIN(TAB_RECD_DT), getDate() ) - DATEDIFF(ww,MIN(TAB_RECD_DT), getDate() ) * 2 >= 231

Select allOpen in new window

ERROR:

Msg 144, Level 15, State 1, Line 21
Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

Also, i can get the SQL to work outside the sproc like this

SELECT PROJECT_ACTIVITY.PROJECT_ID, PMT_TABS.TAB_ID,TAB_NAME, TAB_TYPE, CUST_NAME, PROJECT_DESC,TAB_TYPE,
DATEDIFF(DAY,MIN(TAB_RECD_DT),getDate())- DATEDIFF(ww,MIN(TAB_RECD_DT),getDate())*2 as aging
FROM PMT_USERS INNER JOIN PROJECT_ACTIVITY
ON PROJECT_ACTIVITY.PROJECT_OWNER = PMT_USERS.PMT_USER_ID
INNER JOIN PMT_CUSTOMERS
ON PMT_CUSTOMERS.PROJECT_ID = PROJECT_ACTIVITY.PROJECT_ID
INNER JOIN PMT_TABS
ON PMT_TABS.PROJECT_ID = PROJECT_ACTIVITY.PROJECT_ID
join OPTIONS_ACT_TYPE
on OPTIONS_ACT_TYPE.type = PMT_TABS.TAB_TYPE
WHERE
order_req = 'n'
AND
((TAB_OWNER = 'jw8101' and (OWNER_STATUS ='Sent to Remote Support'OR OWNER_STATUS = 'Open / In Progress'))
OR
(REMOTE_OWNER ='jw8101' and REMOTE_STATUS = 'Open / In Progress'))
AND OVERALL_STATUS <> 'Canceled' AND OVERALL_STATUS <> 'ON HOLD' AND OVERALL_STATUS <> 'Returned to Sales'
GROUP BY PROJECT_ACTIVITY.PROJECT_ID, PMT_TABS.TAB_ID,TAB_NAME, TAB_TYPE, CUST_NAME, PROJECT_DESC
HAVING DATEDIFF(DAY,MIN(TAB_RECD_DT), getDate() ) - DATEDIFF(ww,MIN(TAB_RECD_DT), getDate() ) * 2 >= 231
 
which is the same as the sproc but for some reason the having clause is being skipped in the sproc....?

Have you made any changes to the sproc or is it the same as in your original post?
If changed, please send current script of sproc that does not work.

Just the having clasue

--AgingNonOrders
set @SQL_AGING = '
SELECT
 PROJECT_ACTIVITY.PROJECT_ID,
 PMT_TABS.TAB_ID,
 TAB_NAME,
 TAB_TYPE,
 CUST_NAME,
 PROJECT_DESC,
 TAB_TYPE,
 DATEDIFF(DAY,MIN(TAB_RECD_DT),
 getDate())- DATEDIFF(ww,MIN(TAB_RECD_DT),
 getDate())*2 as aging
FROM
 PMT_USERS INNER JOIN PROJECT_ACTIVITY
 ON PROJECT_ACTIVITY.PROJECT_OWNER =  PMT_USERS.PMT_USER_ID
 INNER JOIN  PMT_CUSTOMERS
 ON PMT_CUSTOMERS.PROJECT_ID = PROJECT_ACTIVITY.PROJECT_ID
 INNER JOIN PMT_TABS
 ON PMT_TABS.PROJECT_ID = PROJECT_ACTIVITY.PROJECT_ID
 join OPTIONS_ACT_TYPE
 on OPTIONS_ACT_TYPE.type = PMT_TABS.TAB_TYPE
WHERE
  ((TAB_OWNER = '+char(39)+@input_css+char(39)+' and (OWNER_STATUS ='+ char(39)+ 'Sent to Remote Support'+ char(39)+' OR OWNER_STATUS = '+ char(39)+'Open / In Progress'+ char(39)+'))
OR (REMOTE_OWNER ='+ char(39)+ @input_css+ char(39)+' and REMOTE_STATUS = '+ char(39)+'Open / In Progress'+ char(39)+'))
AND  order_req = '+ char(39)+'n'+ char(39)+'
AND  OVERALL_STATUS <> '+ char(39)+'Canceled'+ char(39)+' AND OVERALL_STATUS <> '+ char(39)+'ON HOLD'+ char(39)+' AND OVERALL_STATUS <> '+ char(39)+'Returned to Sales'+ char(39)+'

GROUP BY PROJECT_ACTIVITY.PROJECT_ID, PMT_TABS.TAB_ID,TAB_NAME, TAB_TYPE, CUST_NAME, PROJECT_DESC
--THIS HAS CHANGED
HAVING  DATEDIFF(DAY,MIN(TAB_RECD_DT), getDate() ) - DATEDIFF(ww,MIN(TAB_RECD_DT), getDate() ) * 2 >= '  +  ltrim(str(@input_days)) +  '
 
ORDER BY   '+ @aging_sort
 

Even when i do this it doesnt work
HAVING  DATEDIFF(DAY,MIN(TAB_RECD_DT), getDate() ) - DATEDIFF(ww,MIN(TAB_RECD_DT), getDate() ) * 2 >= 231'  
 

I FREAKING LOVE YOU!!!!!!!!!!! Thank you so much i've beeing working on this for about 12 hrs

Happy to assist you.
I've been stung by this same problem so when I didn't see your ORDER BY clause, the light bulb went on.  I've come to the point that whenever I set up a variable that will be used to create a SQL string, I just automatically set it to Varchar(8000) the maximum size allowed.  Then in most cases, I don't have to worry about this.