April 22, 2005 at 11:40 am
I am trying to update a SP and I am getting a strange error. I have tried several things without success. Any help I can get would be appriecated!!
I am trying to post some statistics and sometime the data returns a zero, so I was trying to make my query alitte smarter by checking for zero first. Here is the SP.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
--/****** Object: Stored Procedure sp_Segmentation_Stats ******/
--/****** This is used watch DS4 Segementation Statistics ****/
ALTER PROCEDURE sp_Segmentation_Stats AS
declare @query varchar(4000)
/* NIBS cust survey segmentation percentages */
declare @total decimal(9,2) , @hits decimal(9,2)
set @total = (select count(*) from nibs_customer_survey with (nolock))
set @hits = (select count(*) from nibs_customer_survey with (nolock) where minor_segment_code is not null)
/* NIBS Workorders segmentation percentages */
declare @total5 decimal(9,2) , @hits5 decimal(9,2)
set @total5 = (select count(*) from NIBS_WorkOrders with (nolock))
set @hits5 = (select count(*) from NIBS_WorkOrders with (nolock) where minor_segment_code is not null)
/* ECID hit percentages */
declare @total2 decimal(9,2) , @hits2 decimal(9,2)
set @total2 = (select count(*) from ECID_Customer_Identifier with (nolock))
set @hits2 = (select count(*) from ECID_Customer_Identifier with (nolock) where ECID <> '')
/************** IMS ********************/
/* ECID IMS hit percentages */
declare @total3 decimal(9,2) , @hits3 decimal(9,2)
set @total3 = (select count(*) from IMS_ISSUES with (nolock))
set @hits3 = (select count(*) from IMS_ISSUES with (nolock) where ECID <> '')
/* IMS issues segmentation percentages */
declare @total4 decimal(9,2) , @hits4 decimal(9,2)
set @total4 = (select count(*) from IMS_ISSUES with (nolock) )
set @hits4 = (select count(*) from IMS_ISSUES with (nolock) where minor_segment_code is not null)
begin
set @query = " "
begin
if @total4 <> 0
set @query = " select Source='IMS Issues Segmentation Percentages',@total4 as GTotal, @hits4 as GHits, (@hits4 / @total4) as Hit_Percent "
else
set @query = " select Source='IMS Issues Segmentation Percentages',@total4 as GTotal, @hits4 as GHits, Hit_Percent = 0 "
end
begin
set @query = @query + " union "
if @total3 <> 0
set @query = @query + " select Source='ECID IMS hit Percentages',@total3 as GTotal, @hits3 as GHits, (@hits3 / @total3) as Hit_Percent "
else
set @query = @query + " select Source='ECID DS4 hit Percentages',@total2 as GTotal, @hits2 as GHits, Hit_Percent = 0 "
end
begin
set @query = @query + " union "
if @total2 <> 0
set @query = @query + " select Source='ECID DS4 hit Percentages',@total2 as GTotal, @hits2 as GHits, (@hits2 / @total2) as Hit_Percent "
else
set @query = @query + " select Source='ECID DS4 hit Percentages',@total2 as GTotal, @hits2 as GHits, Hit_Percent = 0 "
end
begin
set @query = @query + " union "
if @total <> 0
set @query = @query + " select Source='NIBS Cust Surv Seg Percentages', @total as GTotal, @hits as GHits, (@hits / @total) as Hit_Percent "
else
set @query = @query + " select Source='NIBS Cust Surv Seg Percentages', @total as GTotal, @hits as GHits, Hit_Percent = 0 "
end
begin
set @query = @query + " union "
if @total5 <> 0
set @query = @query + " select Source='NIBS Work Order Percentages', @total as GTotal, @hits5 as GHits, (@hits5 / @total5) as Hit_Percent "
else
set @query = @query + " select Source='NIBS Work Order Percentages', @total as GTotal, @hits5 as GHits, Hit_Percent = 0 "
end
set @query = @query + " order by hit_percent "
end
EXEC @QUERY
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
===============================================
here is the error
===============================================
Server: Msg 207, Level 16, State 3, Procedure sp_Segmentation_Stats, Line 28
Invalid column name ' '.
Server: Msg 207, Level 16, State 1, Procedure sp_Segmentation_Stats, Line 31
Invalid column name ' select Source='IMS Issues Segmentation Percentages',@total4 as GTotal, @hits4 as GHits, (@hits4 / @total4) as Hit_Percent '.
Server: Msg 207, Level 16, State 1, Procedure sp_Segmentation_Stats, Line 33
Invalid column name ' select Source='IMS Issues Segmentation Percentages',@total4 as GTotal, @hits4 as GHits, Hit_Percent = 0 '.
Server: Msg 207, Level 16, State 1, Procedure sp_Segmentation_Stats, Line 36
Invalid column name ' union '.
Server: Msg 207, Level 16, State 1, Procedure sp_Segmentation_Stats, Line 38
Invalid column name ' select Source='ECID IMS hit Percentages',@total3 as GTotal, @hits3 as GHits, (@hits3 / @total3) as Hit_Percent '.
Server: Msg 207, Level 16, State 1, Procedure sp_Segmentation_Stats, Line 40
Invalid column name ' select Source='ECID DS4 hit Percentages',@total2 as GTotal, @hits2 as GHits, Hit_Percent = 0 '.
Server: Msg 207, Level 16, State 1, Procedure sp_Segmentation_Stats, Line 43
Invalid column name ' union '.
Server: Msg 207, Level 16, State 1, Procedure sp_Segmentation_Stats, Line 45
Invalid column name ' select Source='ECID DS4 hit Percentages',@total2 as GTotal, @hits2 as GHits, (@hits2 / @total2) as Hit_Percent '.
Server: Msg 207, Level 16, State 1, Procedure sp_Segmentation_Stats, Line 47
Invalid column name ' select Source='ECID DS4 hit Percentages',@total2 as GTotal, @hits2 as GHits, Hit_Percent = 0 '.
Server: Msg 207, Level 16, State 1, Procedure sp_Segmentation_Stats, Line 50
Invalid column name ' union '.
Server: Msg 207, Level 16, State 1, Procedure sp_Segmentation_Stats, Line 52
Invalid column name ' select Source='NIBS Cust Surv Seg Percentages', @total as GTotal, @hits as GHits, (@hits / @total) as Hit_Percent '.
Server: Msg 207, Level 16, State 1, Procedure sp_Segmentation_Stats, Line 54
Invalid column name ' select Source='NIBS Cust Surv Seg Percentages', @total as GTotal, @hits as GHits, Hit_Percent = 0 '.
Server: Msg 207, Level 16, State 1, Procedure sp_Segmentation_Stats, Line 57
Invalid column name ' union '.
Server: Msg 207, Level 16, State 1, Procedure sp_Segmentation_Stats, Line 59
Invalid column name ' select Source='NIBS Work Order Percentages', @total as GTotal, @hits5 as GHits, (@hits5 / @total5) as Hit_Percent '.
Server: Msg 207, Level 16, State 1, Procedure sp_Segmentation_Stats, Line 61
Invalid column name ' select Source='NIBS Work Order Percentages', @total as GTotal, @hits5 as GHits, Hit_Percent = 0 '.
Server: Msg 207, Level 16, State 3, Procedure sp_Segmentation_Stats, Line 63
Invalid column name ' order by hit_percent '.
===============================
line 28 is the set @query = " " line.
I dont understand why it thinks everything is a column name. Help please
April 22, 2005 at 12:16 pm
SQL strings are delimited by single quotes, not double quotes.
[Edit] Forgot to add, use "Set quoted_identifier off". Read BOL on the quoted_identifier option.
April 22, 2005 at 12:20 pm
that much I know. how do you sugest I create the query in the begin / end loop?
April 22, 2005 at 12:29 pm
I tried that in the many versions of this SP and when I do the SET .....OFF and the SP loads fine, but I get this error message.
Server: Msg 203, Level 16, State 2, Procedure sp_Segmentation_Stats, Line 65
The name ' select Source='IMS Issues Segmentation Percentages',@total4 as GTotal, @hits4 as GHits, (@hits4 / @total4) as Hit_Percent union select Source='ECID IMS hit Percentages',@total3 as GTotal, @hits3 as GHits, (@hits3 / @total3) as Hit_Percent union select Source='ECID DS4 hit Percentages',@total2 as GTotal, @hits2 as GHits, (@hits2 / @total2) as Hit_Percent union select Source=...
April 25, 2005 at 8:34 am
set @query = ' select Source=''IMS Issues Segmentation Percentages'',@total4 as GTotal, @hits4 as GHits, (@hits4 / @total4) as Hit_Percent '
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply