We help IT Professionals succeed at work.

store procedure

lulu50
lulu50 used Ask the Experts™
on
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      
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Since the first variable is a varchar, you need to pass it a varchar

EXEC [obi.dbo.OBI_GrpKeyLOB_Change_Hist_Pro] '3','7/1/2011','7/1/2011'
lulu50Web application

Author

Commented:
lludden

thank you for helping me

it still says this:

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'
Commented:
use this
Exec [obi.dbo.OBI_GrpKeyLOB_Change_Hist_Pro] '3','7/1/2011','7/1/2011'
not this
Exc [obi.dbo.OBI_GrpKeyLOB_Change_Hist_Pro] '3','7/1/2011','7/1/2011'
lulu50Web application

Author

Commented:
yes, it was the problem with exec

the next part of my question is how to say if the date does not exist just don't query it?
It looks like the SP was originally written using varchar fields instead of datetime.  

You can try just passing NULL (no quotes) if you don't want to include dates.
lulu50Web application

Author

Commented:
     in the store procedure below if I put like @groupsuffix I don't get any records but if
     I do this LIKE '3%')
I get a lots of records.

what I am doing wrong?


               declare @groupsuffix as varchar(8)
      set  @groupsuffix = '3'              


            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'
lulu50Web application

Author

Commented:
Thanks