Parameter in Stored Procedures

[brxmas]

I'm trying run a Stored Procedure using Query Analyzer. The Stored Procedure has two parameters (@start_date_CY and @start_date_LY) they are datetime which I do a convert
SELECT CONVERT(varchar(8), @start_date_CY, 1). The following is my exec:

exec [brxmas].[mc_119employment2_sp] @start_date_CY, @start_date_LY

The output from Query Analyzer is as follows:

No column name
06/19/05

No column name
06/20/04

And, it has all the data for the timeframe. When I create a new Crystal Report (11.0) there is NO data and I can't see any fields from the 'Stored Procedure'.

Here is my Stored Procedure

CREATE PROCEDURE [bchristmas].[mc_119employment2_sp]

@start_date_CY smalldatetime = null,
@start_date_LY smalldatetime = null

AS

--Display input dates in mm/dd/yy format
SELECT CONVERT(varchar(8), @start_date_CY, 1)
SELECT CONVERT(varchar(8), @start_date_LY, 1)


SELECT
ad_num = mc_ad_insertion.ad_num
, source = mc_acct_master_view.source
, class_code_int = mc_ad_detail_view.class_code_int
, class_code_desc = mc_class.class_code_desc
, adv_type = mc_ad_detail_view.adv_type
, period_year = mc_period_dates.period_year
, prod_code = mc_ad_insertion.prod_code
, credit_amt = mc_ad_cost_view.credit_amt
, debit_amt = mc_ad_cost_view.debit_amt
, obj_code = mc_ad_cost_view.obj_code
, obj_code_sub = mc_ad_cost_view.obj_code_sub
, obj_code_desc = mc_obj_code_vals.obj_code_desc
, insertion_date = mc_ad_insertion.insertion_date
, effective_lines =
CASE
WHEN mc_ad_cost_view.obj_code_sub = '1'
THEN mc_ad_insertion.effective_lines
ELSE 0
END
INTO #temp5



-- ORDER BY "mc_ad_detail_view"."class_code_int"


FROM mc_acct_master_view
INNER JOIN ((mc_ad_insertion INNER JOIN mc_ad_cost_view
ON (mc_ad_insertion.ad_num = mc_ad_cost_view.ad_num) AND
(mc_ad_insertion.insertion_num = mc_ad_cost_view.trans_id) AND
(mc_ad_insertion.bus_unit_id = mc_ad_cost_view.bus_unit_id) AND
(mc_ad_insertion.pub_id = mc_ad_cost_view.pub_id) AND
(mc_ad_insertion.acct_num = mc_ad_cost_view.acct_num))
INNER JOIN mc_period_dates
ON mc_ad_insertion.insertion_date = mc_period_dates.calendar_date)
INNER JOIN mc_ad_detail_view
ON (mc_ad_insertion.bus_unit_id = mc_ad_detail_view.bus_unit_id) AND
(mc_ad_insertion.pub_id = mc_ad_detail_view.pub_id) AND
(mc_ad_insertion.acct_num = mc_ad_detail_view.acct_num) AND
(mc_ad_insertion.ad_num = mc_ad_detail_view.ad_num) AND
(mc_ad_insertion.entry_date_time = mc_ad_detail_view.entry_date_time)
ON (mc_acct_master_view.bus_unit_id = mc_ad_detail_view.bus_unit_id) AND
(mc_acct_master_view.pub_id = mc_ad_detail_view.pub_id) AND
(mc_acct_master_view.acct_num = mc_ad_detail_view.acct_num)
INNER JOIN mc_class
ON mc_ad_detail_view.class_code =mc_class.class_code
INNER JOIN mc_obj_code_vals
ON mc_ad_cost_view.obj_code = mc_obj_code_vals.obj_code

WHERE
mc_ad_detail_view.bus_unit_id = 'MMB' AND
mc_ad_detail_view.pub_id = 'MMB' AND
mc_ad_detail_view.adv_type IN ( 'CT', 'GT') and
mc_ad_detail_view.class_code_int IN ('510') AND
mc_ad_cost_view.obj_code NOT IN ('15', '19', '29', '50', '59', '485', '900', '905', '915') AND
mc_ad_insertion.prod_code NOT IN ('ELDY','MBEX', 'MBXC') AND
mc_ad_insertion.insertion_date <= @start_date_CY and
mc_ad_insertion.insertion_date >= @start_date_LY


CREATE NONCLUSTERED INDEX indx_temp5 ON #temp5 (class_code_int)

SELECT
ad_num
, source
, class_code_int
, class_code_desc
, adv_type
, period_year
, prod_code
, credit_amt
, debit_amt
, obj_code
, obj_code_sub
, obj_code_desc
, insertion_date
, effective_lines

FROM #temp5

I would appreciate it if someone can tell me what I'm doing wrong. Thanks in advance for your help.

 

[gmmastros]

Right at the begining, I see some things that are odd.

CREATE PROCEDURE [bchristmas].[mc_119employment2_sp]

@start_date_CY smalldatetime = null,
@start_date_LY smalldatetime = null

AS

--Display input dates in mm/dd/yy format
[red]SELECT CONVERT(varchar(8), @start_date_CY, 1)[/red]
[red]SELECT CONVERT(varchar(8), @start_date_LY, 1)[/red]

By doing this, you are selecting the data (i.e. returning it from the stored procedure). I suspect that you would rather use the data within the stored procedure. If my assumption is correct, then you should do the following

CREATE PROCEDURE [bchristmas].[mc_119employment2_sp]

@start_date_CY smalldatetime = null,
@start_date_LY smalldatetime = null

AS
[red]SET NOCOUNT ON[/red]
--Display input dates in mm/dd/yy format

Declare @CYDate VarChar(8)
Declare @LYDate VarChar(8)

[red]Set @CYDate = CONVERT(varchar(8), @start_date_CY, 1)[/red]
[red]Set @CLDate = CONVERT(varchar(8), @start_date_LY, 1)[/red]

Of course, it only makes sense to do this if you are actually going to use the values. Like this...

    mc_ad_insertion.insertion_date <= @CYDate and
    mc_ad_insertion.insertion_date >= @CLDate

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[brxmas]

Thanks for the help, I appreciate the quick response.

 

[gmmastros]

Did it actually help?

I'm curious to know what the datatype for your insertion_date field is. If it's a varchar field, then the where clause is probably not working properly.

If this did not solve your problem, then please run this and post the results.

Select Data_Type
From Information_Schema.Columns
Where Table_Name = 'mc_ad_insertion'
And column_name = 'insertion_date'



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[brxmas]

Thanks again.

I ran your Select statement in QA"
Select Data_Type
From Information_Schema.Columns
Where Table_Name = 'mc_ad_insertion'
And column_name = 'insertion_date'

results smalldatetime

I need it to be a date mm/dd/yy. When I put the Stored Procedure in my Crystal report the parameters are datetime 2005-06-19 00:00:00.

Thanks.

 

[brxmas]

Thanks for all your help. I figured it out.

parameters
@start_date_CY as varchar(8),
@start_date_LY as varchar(8)

changed table field (mc_ad_insertion.insertion_date)
mc_ad_insertion.insertion_date <=CONVERT (char(8),@start_date_CY,1)
and
mc_ad_insertion.insertion_date >= CONVERT (char(8),@start_date_LY,1)

 

[gmmastros]

I think you are getting confused between how SQL Server stores date/time and how you view date/time.

It is far more efficient to let SQL Server use the date/time format.

The real issue here seems to be with Crystal Reports. Since the Insertion Date is a field returned from the query, it will be in SQL Server's native format (which is yyyy-mm-dd hh:mm:ss).

Since this is a date field, crystal reports should be able to format the data any way you want it to.

If you really want the database to do the formatting, you should change....

SELECT
    ad_num
,    source
,    class_code_int
,    class_code_desc
,    adv_type
,    period_year
,     prod_code  
,    credit_amt     
,    debit_amt         
,    obj_code     
,    obj_code_sub     
,    obj_code_desc     
,    [red]Convert(VarChar(8), insertion_date, 1) As Insertion_Date[/red]
,    effective_lines         

FROM #temp5

Looking at your stored procedure, I'm confused about something. Why do you select the data in to a temp table, create a non-clustered index on it, and then return the data? It seems like you are causing SQL Server to do more work for no good reason.

This is how I would have done this...

CREATE PROCEDURE [bchristmas].[mc_119employment2_sp]

@start_date_CY smalldatetime = null,
@start_date_LY smalldatetime = null

AS

SELECT
    ad_num = mc_ad_insertion.ad_num
,    source = mc_acct_master_view.source 
,    class_code_int =  mc_ad_detail_view.class_code_int
,    class_code_desc =  mc_class.class_code_desc
,    adv_type = mc_ad_detail_view.adv_type
,     period_year = mc_period_dates.period_year
,     prod_code =  mc_ad_insertion.prod_code         
,    credit_amt = mc_ad_cost_view.credit_amt
,    debit_amt = mc_ad_cost_view.debit_amt
,    obj_code = mc_ad_cost_view.obj_code      
,    obj_code_sub = mc_ad_cost_view.obj_code_sub
,    obj_code_desc = mc_obj_code_vals.obj_code_desc
,    insertion_date = Convert(VarChar(10), mc_ad_insertion.insertion_date, 101)
,     effective_lines =
         CASE  
           WHEN  mc_ad_cost_view.obj_code_sub = '1' 
             THEN mc_ad_insertion.effective_lines
           ELSE 0
     END
FROM mc_acct_master_view 
    INNER JOIN ((mc_ad_insertion INNER JOIN mc_ad_cost_view 
        ON (mc_ad_insertion.ad_num = mc_ad_cost_view.ad_num) AND 
        (mc_ad_insertion.insertion_num = mc_ad_cost_view.trans_id) AND 
        (mc_ad_insertion.bus_unit_id = mc_ad_cost_view.bus_unit_id) AND 
        (mc_ad_insertion.pub_id = mc_ad_cost_view.pub_id) AND 
        (mc_ad_insertion.acct_num = mc_ad_cost_view.acct_num)) 
     INNER JOIN mc_period_dates 
        ON mc_ad_insertion.insertion_date = mc_period_dates.calendar_date) 
    INNER JOIN mc_ad_detail_view 
        ON (mc_ad_insertion.bus_unit_id = mc_ad_detail_view.bus_unit_id) AND 
        (mc_ad_insertion.pub_id = mc_ad_detail_view.pub_id) AND 
        (mc_ad_insertion.acct_num = mc_ad_detail_view.acct_num) AND 
        (mc_ad_insertion.ad_num = mc_ad_detail_view.ad_num) AND 
        (mc_ad_insertion.entry_date_time = mc_ad_detail_view.entry_date_time)
        ON (mc_acct_master_view.bus_unit_id = mc_ad_detail_view.bus_unit_id) AND 
        (mc_acct_master_view.pub_id = mc_ad_detail_view.pub_id) AND 
        (mc_acct_master_view.acct_num = mc_ad_detail_view.acct_num) 
    INNER JOIN mc_class
        ON mc_ad_detail_view.class_code =mc_class.class_code 
    INNER JOIN mc_obj_code_vals 
        ON mc_ad_cost_view.obj_code = mc_obj_code_vals.obj_code

WHERE      
    mc_ad_detail_view.bus_unit_id = 'MMB' AND 
    mc_ad_detail_view.pub_id = 'MMB' AND
    mc_ad_detail_view.adv_type IN ( 'CT', 'GT') and
    mc_ad_detail_view.class_code_int IN ('510') AND
    mc_ad_cost_view.obj_code  NOT IN ('15', '19', '29', '50', '59', '485', '900', '905', '915') AND
    mc_ad_insertion.prod_code NOT IN ('ELDY','MBEX', 'MBXC') AND
    mc_ad_insertion.insertion_date [red]Between @start_date_LY And @start_date_CY[/red]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[brxmas]

This is the first Stored Procedure I've written. I will take your advice and change the code. Thanks for your expertise.