Create a query in SQL server like the parameter query in ACCESS 2

[velveeta]

I'm sorry, I'm transitioning from doing most of my SQL queries in ACCESS. I am now solely in SQL server, without another application to fall back on.

I have this query (below), and in Access I could use a parameter to turn these lines in my following SQL query

and gifteffdat>'2005-03-31 00:00:00.000' and gifteffdat<'2005-05-01 00:00:00.000'

into these lines:

and gifteffdat Between [From What Date?] to [To What Date?)

Here is my query:

select giftacctno as Fund,
nameid as ID#,
gifteffdat as 'Date',
from gifts_full, name_full, tender, gift_types
where giftid=nameid
and nametype='a'
and giftacctno like 'F%'
and gifteffdat>'2005-03-31 00:00:00.000' and gifteffdat<'2005-05-01 00:00:00.000'
and CAST(round(giftnonded, 0) as varchar(20)) >'0.00'
and giftrest not in ('1018', '1019', '1440', '1169', '4507', '1870', '1875', '2227', '3376')
and gifttender=tender.table_code
and gifttype=gift_types.table_code
order by Date

I know I need to use a Create Procedure, but I need help.

Thanks!

 

[JayKusch]

You are very close ... if I am reading the direction you are trying to go - lol.

I would still use the < and > operands in place of the BETWEEN clause.

To make your code a stored procedure that you can pass a FromDate and ToDate into, you would do the following ...

CREATE Procedure usp_Get_Data
  @FromDate DateTime,
  @ToDate   DateTime

  AS 
select giftacctno as Fund,
       nameid     as ID#,
       gifteffdat as 'Date',
from   gifts_full, 
       name_full, 
       tender, 
       gift_types
where  giftid=nameid
and    nametype='a'
and    giftacctno like 'F%'
and    gifteffdat>[b]@FromDate[/b]
and    gifteffdat<= [b]@ToDate[/b]
and    CAST(round(giftnonded, 0) as varchar(20)) >'0.00'
and    giftrest not in ('1018', '1019', '1440', '1169', '4507', '1870', '1875', '2227', '3376')
and    gifttender=tender.table_code
and    gifttype=gift_types.table_code
order by gifteffdat

I am assuming these are all Tables??
gifts_full,
name_full,
tender,
gift_types


Thanks

J. Kusch

 

[ESquared]

velveeta,

In order to do what you are asking to do, there has to be some sort of GUI which can pop up and ask for these values. You say that you have transitioned to using SQL Server exclusively. However, there's no real "front end" in SQL Serve with user forms and such that could be used to ask for these things.

So first, I have to ask, what are you using for your front-end? Where is the query result going to be displayed? Only once you answer that can anyone give you ideas about how to parameterize queries.

I will toss in, though, that you might take a look at stored procedures, as JayKusch has suggested.

For what it's worth, I cannot figure out why you are converting a number to characters before doing a greater-than comparison... this seems like a definite error. (and CAST(round(giftnonded, 0) as varchar(20)) >'0.00')

Also, you may be interested to know that you can leave off the '00:00:00.000' in your dates, it's not necessary.

-------------------------------------
Only the unvirtuous can be dutiful, for the virtuous already perform duty's requirements by inclination and for pleasure. Where there is no pain, no disinclination, there is no duty.
- Erik E

 

[velveeta]

They do have Crystal Reports hooked into the back-end where I work.

I'm assuming I will need to create the pop-up there.

SQL runs on the back-end of a commercial product, and queries can't be run there.

 

[ESquared]

I haven't used CR very much, but I believe all you need is to create a stored procedure much like has been suggested. If you cannot do that and need to run a client query, then you should head on over to the Crystal Reports forum and those folks can help you!