troubleshooting Question

store procedure

Avatar of lulu50
lulu50Flag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2008SQL
7 Comments2 Solutions374 ViewsLast Modified:
Hi,

It's been a long time since I have worked with a store procedure

I created a store procedure I need to exc this store procedure for debugging.

what am i doing wrong?

Exc [obi.dbo.OBI_GrpKeyLOB_Change_Hist_Pro] 3,'7/1/2011','7/1/2011'


If I highlight "Exc [obi.dbo.OBI_GrpKeyLOB_Change_Hist_Pro]  3,'7/1/2011','7/1/2011' " 
 
I get an error that says
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '3'.
 
Exc [obi.dbo.OBI_GrpKeyLOB_Change_Hist_Pro]  3,'7/1/2011','7/1/2011'
should I do this
Exc [obi.dbo.OBI_GrpKeyLOB_Change_Hist_Pro]  (3,'7/1/2011','7/1/2011' )
I am not sure how to run it.


How can I say if the date is not available just don't query it?


GO
/****** Object:  StoredProcedure [dbo].[OBI_GrpKeyLOB_Change_Hist_Pro]    Script Date: 08/08/2011 08:08:03 ******/

/* Exc [obi.dbo.OBI_GrpKeyLOB_Change_Hist_Pro] 3,'7/1/2011','7/1/2011'  */

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[OBI_GrpKeyLOB_Change_Hist_Pro]
   @groupsuffix varchar(8),
   @GetFromDate datetime,  
   @GetToDate datetime  
AS
                     
IF (@GetFromDate <>'') and (@GetToDate <>'')
BEGIN
            SELECT distinct(groupsuffix) ,STUFF((SELECT distinct', ' + t2.ServiceKey
            FROM dbo.OBI_GrpKeyLOB_Change_Hist t2
            WHERE t1.groupsuffix=t2.groupsuffix and t1.Created_Date=t2.Created_Date
            ORDER BY 1
            FOR XML PATH(''), TYPE).value('.','varchar(max)') ,1,2, '') AS Contacinated,
            Status_Flag, created_date
            FROM dbo.OBI_GrpKeyLOB_Change_Hist t1
            
            where ([groupsuffix] LIKE '@groupsuffix%') and Status_Flag = 'C'
             and (Created_Date >= Cast(@GetFromDate as datetime) and
          Created_Date <= Cast(@GetToDate as datetime))
       
 END
 
 ELSE
 BEGIN
               SELECT distinct(groupsuffix) ,STUFF((SELECT distinct', ' + t2.ServiceKey
            FROM dbo.OBI_GrpKeyLOB_Change_Hist t2
            WHERE t1.groupsuffix=t2.groupsuffix and t1.Created_Date=t2.Created_Date
            ORDER BY 1
            FOR XML PATH(''), TYPE).value('.','varchar(max)') ,1,2, '') AS Contacinated,
            Status_Flag, created_date
            FROM dbo.OBI_GrpKeyLOB_Change_Hist t1            
            where ([groupsuffix] LIKE '@groupsuffix%') and Status_Flag = 'C'
  END      
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 7 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros