Parameters in stored procedures

[tgtfranz]

This may already be out there but the search is down right now and I am anxious to find out what I am missing. I am sure that it is something small.

I have a sproc that I am wanting to create an input parameter for but I can not get it to accept a LIKE where clause when executing. Currently my conditions need to be
exact (1997-12-05) and I want to be able to enter something like (1997%)

What am I doing wrong.

Below is an example of my code.


CREATE PROCEDURE dbo.sp_CreatePayTypeDescPayPeriod
@EmployeeNumber INT
,@EnterCheckDate datetime
AS

SELECT DISTINCT
pc.emp_no
, pcpa.pay_type
, pc.check_date
, SUM(pcpa.hours) AS Hours
, SUM(pcpa.cmw_gross) AS Gross

FROM
pay_checks AS pc
INNER JOIN
pay_checks_pay_assoc AS pcpa
ON
pc.pay_checks = pcpa.pay_checks
WHERE
pc.emp_no = @EmployeeNumber
AND
pc.check_date LIKE @EnterCheckDate

 

[AlexCuse]

LIKE is used for string comparison, not date time comparison.

What is the exact comparison you want to do? Within a few days, same month, etc...


I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[bborissov]

How about:

YEAR( pc.check_date) = YEAR(@EnterCheckDate)

and this is only because I shot in the dark. What you trying to do?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[tgtfranz]

I really am not comparing anything. I want this sproc to simply be something that I will eventually pass into Access for users to query a system by whatever date or date string they provide. Hope that helps. If not let me know.

 

[AlexCuse]

It doesn't help. Why are you using LIKE (and '=') if your intention is not to compare anything? Because both actions will result in comparisons.

If you want the dates to match exactly use this:

pc.check_date [b]=[/b] @EnterCheckDate

Otherwise, we need more information.

I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[tgtfranz]

Sorry, let me try to clarify:

I am using the = because there is a required emp no for each search. This is always a known value.

Then I want to query for that emp no and all the records related to that employee for a certain date. Sometimes the whole date is not known, so I would like to use the LIKE functionality if I can.

 

[AlexCuse]

Well, as stated above, you can't.

You say you want to use LIKE, can you please explain what you mean by that?

For all I know you could want to select all records for the given emp no that are dated exactly 3 days prior to supplied date or 27 days after (with nothing in between).

Or you could want dates within a range.

Or you could want dates only in the past, or only in the future.

Or you could want dates whose date component (disregarding time) equals that of the parameter.

If you cannot define what it is that you want to do, then there is nothing anyone can do to help you.

Hope this helps,

Alex

I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[bborissov]

You could use LIKE, but as Alex said if you don't know WHAT you want it is hared to write the query

DECLATE @WhatIWant varchar(20)
SET @WhatIWant = '1997'
SELECT *
       FROM MyTable
       WHERE CONVERT(varchar(8),DateField, 112) LIKE @WhatIWant+'%'

But that exactly the same as:

DECLATE @WhatIWant int
SET @WhatIWant = 1997
SELECT *
       FROM MyTable
       WHERE YEAR(DateField) = @WhatIWant

only slower

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[tgtfranz]

I agree and knew that was going to be an issue. Only thing that is certain is the employee number. I could need records on this specific employee on anything from now to the last 30 years. So I can not really use the SET command to define my variable.

I know that Access has a way to pass in parameters when running a query and I was able to get it to run the way that I wanted. I prefer to do all the processing of the data on the server, so I ventured off hoping to be able to do this in a sproc.

Thanks bborissov, I will try playing around with one of the queries that you listed. It sparked something.

 

[tgtfranz]

I finally got it to return the data that I want using this code:

EXEC dbo.sp_CreatePayTypeDescPayPeriod '5336', '%1997%'

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CreatePayTypeDescPayPeriod]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_CreatePayTypeDescPayPeriod]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.sp_CreatePayTypeDescPayPeriod


@EmployeeNumber INT
,@EnterCheckDate varchar(12)
AS

SELECT DISTINCT
pc.emp_no
, pcpa.pay_type
, pc.check_date
, SUM(pcpa.hours) AS Hours
, SUM(pcpa.cmw_gross) AS Gross

FROM
pay_checks AS pc
INNER JOIN
pay_checks_pay_assoc AS pcpa
ON
pc.pay_checks = pcpa.pay_checks
WHERE
pc.emp_no = @EmployeeNumber
AND
pc.check_date LIKE @EnterCheckDate

GROUP BY
pc.emp_no
,pcpa.pay_type
,pc.check_date
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Thanks for all your help.