Using "AS" in Stored Procedure.

  • Thread starter Thread starter Henry Craven
  • Start date Start date
H

Henry Craven

I have a Union Query that works as a View, and is the Source for an MS
Access
ADP Report. However, I need to pass in some variable parameters but
because it
uses "as" it won't work.

Is there any way to delimit/work around the "As" or some way to pass in
the parameters ?

I can use UDF in a View, but can't see how to pass in the values when
calling the View

Some of the SQL Statement follows.

TIA

--
Henry Craven {SBS-MVP}
Melbourne Australia

SELECT TOP 100 PERCENT
dbo.Vw_Format_AllStaff.GivenName + ' ' + dbo.Vw_Format_AllStaff.Surname
AS StaffName,
0 AS ListItems,
0 AS ListComm,
1 AS SellItems,
dbo.vwSellingCommission.SellingConsultantCommission AS SellComm,
dbo.vwSellingCommission.GrossCommission AS GrossComm,
dbo.tblCampaignVoucher.DateSettlement,
dbo.Vw_Format_AllStaff.StaffId,
0 AS ListItemsFY,
0 AS ListCommFY,
0 AS SellItemsFY,
0 AS SellCommFY,
0 AS GrossCommFY,
0 AS ListItemsCY,
0 AS ListCommCY,
0 AS SellItemsCY,
0 AS SellCommCY,
0 AS GrossCommCY
FROM dbo.Vw_Format_AllStaff
INNER JOIN dbo.vwSellingCommission
ON dbo.Vw_Format_AllStaff.StaffId =
dbo.vwSellingCommission.SellingConsultantId
INNER JOIN dbo.tblCampaignVoucher
ON dbo.vwSellingCommission.CampaignId =
dbo.tblCampaignVoucher.CampaignId

WHERE dbo.tblCampaignVoucher.DateSettlement >=

/*
This works...
dbo.fn_1stDayOfMonth(Getdate())

but need to pass in a Variable month
@varInput
dbo.fn_1stDayOfMonth(@varInput)
*/

UNION

SELECT TOP 100 PERCENT
dbo.Vw_Format_AllStaff.GivenName + ' ' + dbo.Vw_Format_AllStaff.Surname
AS StaffName,
1 AS ListItems,
dbo.vwListingCommission.ListingConsultantCommission AS ListComm,
0 AS SellItems,
0 AS SellComm,
dbo.vwListingCommission.GrossCommission AS GrossComm,
dbo.tblCampaignVoucher.DateSettlement,
dbo.Vw_Format_AllStaff.StaffId,
0 AS ListItemsFY,
0 AS ListCommFY,
0 AS SellItemsFY,
0 AS SellCommFY,
0 AS GrossCommFY,
0 AS ListItemsCY,
0 AS ListCommCY,
0 AS SellItemsCY,
0 AS SellCommCY,
0 AS GrossCommCY
FROM dbo.Vw_Format_AllStaff
INNER JOIN dbo.vwListingCommission ON dbo.Vw_Format_AllStaff.StaffId =
dbo.vwListingCommission.ListingConsultantId
INNER JOIN dbo.tblCampaignVoucher ON dbo.vwListingCommission.CampaignId
= dbo.tblCampaignVoucher.CampaignId
WHERE dbo.tblCampaignVoucher.DateSettlement >=

/*
This works...
dbo.fn_1stDayOfMonth(Getdate())

but need to pass in a Variable month
@varInput
dbo.fn_1stDayOfMonth(@varInput)
*/
UNION........etc
 
Thanks but passing the params to the SP is not a Problem.

SPs balk at the

.... AS StaffName,
0 AS ListItems,
0 AS ListComm,

etc...

In the View, which will accept a UDF, I can't / don't know how to
call it with a Var input param in the UDF.
 
I don't get it, Henry, what's the problem with SPs and 'AS'? There's an SP
in the 'NorthwindCS' sample database that uses that syntax, and I tested
adding a "0 As MyAlias" - see below. The SP runs without any problem. What
problem are you experiencing?

SELECT dbo.Employees.Country, dbo.Employees.LastName,
dbo.Employees.FirstName, dbo.Orders.ShippedDate, dbo.Orders.OrderID,
dbo.[Order Subtotals].Subtotal AS SaleAmount, 0 AS
MyAlias
FROM dbo.Employees INNER JOIN
dbo.Orders INNER JOIN
dbo.[Order Subtotals] ON dbo.Orders.OrderID =
dbo.[Order Subtotals].OrderID ON dbo.Employees.EmployeeID =
dbo.Orders.EmployeeID
WHERE (dbo.Orders.ShippedDate BETWEEN @Beginning_Date AND @Ending_Date)

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Cold light of morn, and given that you say it works I pasted
the SQL statement into a new SP, and lo and behold the
syntax check passes OK.

Have no idea why I was getting all the "AS" errors last night
( same Copy and Paste... ) so tanks for the Time Brendan
and Alejandro and sorry for the waste of bandwidth folks.

Saves me passing in one seriously ugly SQL string.

--
Henry Craven {SBS-MVP}
Melbourne Australia

Brendan Reynolds said:
I don't get it, Henry, what's the problem with SPs and 'AS'? There's an SP
in the 'NorthwindCS' sample database that uses that syntax, and I tested
adding a "0 As MyAlias" - see below. The SP runs without any problem. What
problem are you experiencing?

SELECT dbo.Employees.Country, dbo.Employees.LastName,
dbo.Employees.FirstName, dbo.Orders.ShippedDate, dbo.Orders.OrderID,
dbo.[Order Subtotals].Subtotal AS SaleAmount, 0 AS
MyAlias
FROM dbo.Employees INNER JOIN
dbo.Orders INNER JOIN
dbo.[Order Subtotals] ON dbo.Orders.OrderID =
dbo.[Order Subtotals].OrderID ON dbo.Employees.EmployeeID =
dbo.Orders.EmployeeID
WHERE (dbo.Orders.ShippedDate BETWEEN @Beginning_Date AND @Ending_Date)

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
Click to expand...
 
Right...

That's got -you- on the list too..

( Everyone on the list owes me a beer next year )
;-)
 
Back
Top