Help with Converting Access query to SQL Stored procedure Please!

  • Thread starter Thread starter Renee
  • Start date Start date
R

Renee

Can anyone tell me if converting this Access query to a stored procedure is
possible and if so can you PLEASE show me how??? I would appreciate any
help you can give me!

SELECT Orders.Username, Sum(Orders.Quantity) AS Total
FROM Orders
WHERE (((Orders.Processed)=False) AND ((Orders.INumber) Like "%myvalue%")
AND ((Format(Orders.RDate,"Short Date"))=Format(Now(),"Short Date")))
GROUP BY Orders.Username
HAVING (((Orders.Username)=[@varUsername]));
 
What's your exact problem with this query? Do you have trouble with
something in particular or if it is simply that you have never wrote a
stored procedure and called it from Access before?
 
I am converting an access database to SQL server and editing the active
server pages to use SQL instead of access. The access database will still
be in the loop but as an adp

Everything has been ok until I got to this one and simply didn't know what
to do with it. I need to turn
it into an MSSQL stored procedure but am unsure as to how. Was hoping
someone could show me with this one and then I'd understand what to do with
this type of query.

Sorry for my ignorance... Can you help?

Sylvain Lafontaine said:
What's your exact problem with this query? Do you have trouble with
something in particular or if it is simply that you have never wrote a
stored procedure and called it from Access before?

Renee said:
Can anyone tell me if converting this Access query to a stored procedure
is
possible and if so can you PLEASE show me how??? I would appreciate any
help you can give me!

SELECT Orders.Username, Sum(Orders.Quantity) AS Total
FROM Orders
WHERE (((Orders.Processed)=False) AND ((Orders.INumber) Like "%myvalue%")
AND ((Format(Orders.RDate,"Short Date"))=Format(Now(),"Short Date")))
GROUP BY Orders.Username
HAVING (((Orders.Username)=[@varUsername]));
Click to expand...
Click to expand...
 
You may try something like that:

CREATE PROCEDURE dbo.MyProcedure
(
@varUsername varchar (50)
)
AS
SELECT Orders.Username, Sum(Orders.Quantity) AS Total
FROM Orders
WHERE (Orders.Processed = 0 AND Orders.INumber Like '%myvalue%'
and Convert (varchar (10), Orders.RDate, 1) = Convert (varchar (10),
getDate (), 1)

GROUP BY Orders.Username
HAVING Orders.Username = @varUsername
GO

In SQL, there are no True or False constant, you must use 1 and 0 instead.
Many controls in VB will use -1 instead of 1 as the value of True, so it is
often more convenient to write: <> 0 instead of: = 1 or: = -1 when you want
to test for the value of True.

The equivalent of Format () is Convert (...). The constant 1 used in the
example above is one of the many available for different date formats. The
fonction Cast () is also available and is similar to the Convert () function
but with a different syntaxe.

Also, in SQL, single quote ' are used instead of double quote " for the
string delimiter.

I have also put the dbo. prefix before the name of the procedure, as ADP
will make trouble for other users if you don't set it; but this is the
subject of a whole new discussion.


Finally, it is probably overkill to put the clause Orders.Username =
@varUsername after the Having instruction in this particular example. You
should put it in the Where clause for more efficiency.


S. L.

Renee said:
I am converting an access database to SQL server and editing the active
server pages to use SQL instead of access. The access database will still
be in the loop but as an adp

Everything has been ok until I got to this one and simply didn't know what
to do with it. I need to turn
it into an MSSQL stored procedure but am unsure as to how. Was hoping
someone could show me with this one and then I'd understand what to do
with
this type of query.

Sorry for my ignorance... Can you help?

Sylvain Lafontaine said:
What's your exact problem with this query? Do you have trouble with
something in particular or if it is simply that you have never wrote a
stored procedure and called it from Access before?

Renee said:
Can anyone tell me if converting this Access query to a stored
procedure
is
possible and if so can you PLEASE show me how??? I would appreciate
any
help you can give me!

SELECT Orders.Username, Sum(Orders.Quantity) AS Total
FROM Orders
WHERE (((Orders.Processed)=False) AND ((Orders.INumber) Like "%myvalue%")
AND ((Format(Orders.RDate,"Short Date"))=Format(Now(),"Short Date")))
GROUP BY Orders.Username
HAVING (((Orders.Username)=[@varUsername]));
Click to expand...
Click to expand...
Click to expand...
 
Sorry, but in my previous example, I have forgotten to write a final right
parenthesis at the end of the Where clause.

S. L.

Sylvain Lafontaine said:
You may try something like that:

CREATE PROCEDURE dbo.MyProcedure
(
@varUsername varchar (50)
)
AS
SELECT Orders.Username, Sum(Orders.Quantity) AS Total
FROM Orders
WHERE (Orders.Processed = 0 AND Orders.INumber Like '%myvalue%'
and Convert (varchar (10), Orders.RDate, 1) = Convert (varchar (10),
getDate (), 1)

GROUP BY Orders.Username
HAVING Orders.Username = @varUsername
GO

In SQL, there are no True or False constant, you must use 1 and 0 instead.
Many controls in VB will use -1 instead of 1 as the value of True, so it
is often more convenient to write: <> 0 instead of: = 1 or: = -1 when you
want to test for the value of True.

The equivalent of Format () is Convert (...). The constant 1 used in the
example above is one of the many available for different date formats.
The fonction Cast () is also available and is similar to the Convert ()
function but with a different syntaxe.

Also, in SQL, single quote ' are used instead of double quote " for the
string delimiter.

I have also put the dbo. prefix before the name of the procedure, as ADP
will make trouble for other users if you don't set it; but this is the
subject of a whole new discussion.


Finally, it is probably overkill to put the clause Orders.Username =
@varUsername after the Having instruction in this particular example. You
should put it in the Where clause for more efficiency.


S. L.

Renee said:
I am converting an access database to SQL server and editing the active
server pages to use SQL instead of access. The access database will
still
be in the loop but as an adp

Everything has been ok until I got to this one and simply didn't know
what
to do with it. I need to turn
it into an MSSQL stored procedure but am unsure as to how. Was hoping
someone could show me with this one and then I'd understand what to do
with
this type of query.

Sorry for my ignorance... Can you help?

Sylvain Lafontaine said:
What's your exact problem with this query? Do you have trouble with
something in particular or if it is simply that you have never wrote a
stored procedure and called it from Access before?

Can anyone tell me if converting this Access query to a stored
procedure
is
possible and if so can you PLEASE show me how??? I would appreciate
any
help you can give me!

SELECT Orders.Username, Sum(Orders.Quantity) AS Total
FROM Orders
WHERE (((Orders.Processed)=False) AND ((Orders.INumber) Like "%myvalue%")
AND ((Format(Orders.RDate,"Short Date"))=Format(Now(),"Short Date")))
GROUP BY Orders.Username
HAVING (((Orders.Username)=[@varUsername]));
Click to expand...
Click to expand...
Click to expand...
 
You are an absolute God send! thank you so very much for taking the time to
respond. I have bought several books and looked everywhere for an example I
could use. This has taught me more than all of that... I see the errors of
my ways now. I changed the rest to look as the example you have given me
and all is well. Bless you!


Sylvain Lafontaine said:
Sorry, but in my previous example, I have forgotten to write a final right
parenthesis at the end of the Where clause.

S. L.

Sylvain Lafontaine said:
You may try something like that:

CREATE PROCEDURE dbo.MyProcedure
(
@varUsername varchar (50)
)
AS
SELECT Orders.Username, Sum(Orders.Quantity) AS Total
FROM Orders
WHERE (Orders.Processed = 0 AND Orders.INumber Like '%myvalue%'
and Convert (varchar (10), Orders.RDate, 1) = Convert (varchar (10),
getDate (), 1)

GROUP BY Orders.Username
HAVING Orders.Username = @varUsername
GO

In SQL, there are no True or False constant, you must use 1 and 0 instead.
Many controls in VB will use -1 instead of 1 as the value of True, so it
is often more convenient to write: <> 0 instead of: = 1 or: = -1 when you
want to test for the value of True.

The equivalent of Format () is Convert (...). The constant 1 used in the
example above is one of the many available for different date formats.
The fonction Cast () is also available and is similar to the Convert ()
function but with a different syntaxe.

Also, in SQL, single quote ' are used instead of double quote " for the
string delimiter.

I have also put the dbo. prefix before the name of the procedure, as ADP
will make trouble for other users if you don't set it; but this is the
subject of a whole new discussion.


Finally, it is probably overkill to put the clause Orders.Username =
@varUsername after the Having instruction in this particular example. You
should put it in the Where clause for more efficiency.


S. L.

Renee said:
I am converting an access database to SQL server and editing the active
server pages to use SQL instead of access. The access database will
still
be in the loop but as an adp

Everything has been ok until I got to this one and simply didn't know
what
to do with it. I need to turn
it into an MSSQL stored procedure but am unsure as to how. Was hoping
someone could show me with this one and then I'd understand what to do
with
this type of query.

Sorry for my ignorance... Can you help?

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message What's your exact problem with this query? Do you have trouble with
something in particular or if it is simply that you have never wrote a
stored procedure and called it from Access before?

Can anyone tell me if converting this Access query to a stored
procedure
is
possible and if so can you PLEASE show me how??? I would appreciate
any
help you can give me!

SELECT Orders.Username, Sum(Orders.Quantity) AS Total
FROM Orders
WHERE (((Orders.Processed)=False) AND ((Orders.INumber) Like
"%myvalue%")
AND ((Format(Orders.RDate,"Short Date"))=Format(Now(),"Short Date")))
GROUP BY Orders.Username
HAVING (((Orders.Username)=[@varUsername]));
Click to expand...
Click to expand...
Click to expand...
 
-----Original Message-----
Can anyone tell me if converting this Access query to a stored procedure is
possible and if so can you PLEASE show me how??? I would appreciate any
help you can give me!

SELECT Orders.Username, Sum(Orders.Quantity) AS Total
FROM Orders
WHERE (((Orders.Processed)=False) AND ((Orders.INumber) Like "%myvalue%")
AND ((Format(Orders.RDate,"Short Date"))=Format(Now (),"Short Date")))
GROUP BY Orders.Username
HAVING (((Orders.Username)=[@varUsername]));

--
Renee


.
Click to expand...
 
Back
Top