Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Converting SQL from Access application into SQL Server Stored Procedure

Posted on 2005-04-12
6
Medium Priority
?
378 Views
Last Modified: 2012-08-14
The following where clause works in my Access application:

" WHERE tblCustomers.DateLost <= CDate(DateAdd(""yyyy"",-3,""6/30/" & Forms!frmCustomRpt.lstCustYrs.Value & """)) "

Now I am trying to modify this statement so that I can place it in a SQL Server Stored Procedure:
Let's assume I assign the value of Forms!frmCustomRpt.lstCustYrs.Value to a parameter named RptYear.

CREATE PROCEDURE dbo.FallACSQuery
@RptYear int  
AS
SELECT Null AS Title, tblCustomers.FirstName AS [First Name], tblCustomers.MiddleInitial AS [MI],
tblCustomers.LastName AS [Last Name], tblCustomers.SecondNameFirst AS [Second Name First],
tblCustomers.SecondNameMid AS [Second Name Mid], tblCustomers.SecondNameLast AS [Second Name Last],
Null AS Relation, tblCustomers.Address1 AS [Address 1], tblCustomers.Address2 AS [Address 2],
tblCustomers.Address3 AS [Address 3], tblCustomers.Address4 AS [Address 4], tblCustomers.City,
tblCustomers.State, tblCustomers.Zip, tblCustomers.SSN, Null AS [DDA Number], Null AS [Check/Cert Number],
Right(tblCustomers.OfficeNumber,3) & ' ' & tblCustomers.CustomerNumber AS [Account Number],
tblProducts.PropertyType AS [Property Type], Null AS [Property Status],
tblProducts.CUSIP, tblProducts.SecurityName AS [Security Name], Null AS [Sub-Issue]
FROM tblStatesAll INNER JOIN (tblCustomers INNER JOIN tblProducts ON tblCustomers.CustomerNumber = tblProducts.CustomerNumber)
ON tblStatesAll.StateFS = tblCustomers.State
WHERE tblCustomers.DateLost <= CDate(DateAdd('yyyy',-3,'6/30/' &  @RptYear))
ORDER BY tblCustomers.State

When I click on Check Syntax from within the Stored Procedure that I am in the process of creating, I get an error message as follows:

Error 1023. Invalid parameter 1 specified for dateadd.

Do you know how I may fix this offending Where clause ?
0
Comment
Question by:zimmer9
  • 2
  • 2
  • 2
6 Comments
 
LVL 36

Expert Comment

by:SidFishes
ID: 13768750
cdate is not a sqlserver function

you need to use cast or convert

0
 
LVL 36

Expert Comment

by:SidFishes
ID: 13768796
and your string building isn't going to work i don't think

maybe

DECLARE @var varchar(50)
Set @var = '6/30/' + @rptYear



SELECT blah...

WHERE tblCustomers.DateLost <= DateAdd(year ,-3, @var)

if that doesn't work, you may need to build dynamic sql stmts...

0
 
LVL 1

Expert Comment

by:bcopping
ID: 13770017
Ciuld try:

CREATE PROCEDURE dbo.FallACSQuery
@RptYear int  
AS

DECLARE @Date AS DATETIME
SET @Date = CAST((@RptYear - 3) AS VARCHAR) + '-06-30'

SELECT Null AS Title, tblCustomers.FirstName AS [First Name], tblCustomers.MiddleInitial AS [MI],
tblCustomers.LastName AS [Last Name], tblCustomers.SecondNameFirst AS [Second Name First],
tblCustomers.SecondNameMid AS [Second Name Mid], tblCustomers.SecondNameLast AS [Second Name Last],
Null AS Relation, tblCustomers.Address1 AS [Address 1], tblCustomers.Address2 AS [Address 2],
tblCustomers.Address3 AS [Address 3], tblCustomers.Address4 AS [Address 4], tblCustomers.City,
tblCustomers.State, tblCustomers.Zip, tblCustomers.SSN, Null AS [DDA Number], Null AS [Check/Cert Number],
Right(tblCustomers.OfficeNumber,3) & ' ' & tblCustomers.CustomerNumber AS [Account Number],
tblProducts.PropertyType AS [Property Type], Null AS [Property Status],
tblProducts.CUSIP, tblProducts.SecurityName AS [Security Name], Null AS [Sub-Issue]
FROM tblStatesAll INNER JOIN (tblCustomers INNER JOIN tblProducts ON tblCustomers.CustomerNumber = tblProducts.CustomerNumber)
ON tblStatesAll.StateFS = tblCustomers.State
WHERE tblCustomers.DateLost <= @Date)
ORDER BY tblCustomers.State
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:zimmer9
ID: 13771954
I tried your suggestion above in MS SQL Server within the Stored Procedure. When I click on the Check Syntax button, I get the following error message:

Microsoft SQL-DMO (ODBC SQLState: 42000)
Error 403: Invalid operator for Data Type. Operator equals boolean AND, type equals nvarchar.

0
 
LVL 1

Accepted Solution

by:
bcopping earned 2000 total points
ID: 13772109
does it tell you what line the error is on? i think it might be from:

Right(tblCustomers.OfficeNumber,3) & ' ' & tblCustomers.CustomerNumber AS [Account Number],

try replaceing & with +
0
 

Author Comment

by:zimmer9
ID: 13772370
You were absolutely correct in my misuse of the ampersands.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question