Using a variable in a SELECT INTO statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using a variable in a SELECT INTO statement

Author

Topic

RJ
Starting Member

11 Posts

Posted - 2011-06-23 : 16:45:17

I have a Crystal report that obtains data via a stored procedure. The SP should also copy the selcted data into a new table. If I do no use a variable then the process works, however, once the variable is used I get an error message in Crystal "Failed to retrive data from the database".

The variable in the SP is to prompt for the dates in Crystal. Can anyone assist me to overcome this problem.

Here is a copy of the script:

ALTER PROCEDURE [dbo].[A001_SVC_DATES]

(
@StartDate Datetime,
@EndDate Datetime
)
AS

DROP TABLE SVCCRM.SVCREM

select cust.custno, cust.fname, cust.lname, cust.addr1, cust.addr2, cust.addr2e, cust.addr3, cust.po, cust.postcd,
cust.email, cust.htel, cust.wtel, cust.tel3, cust.consent, cust.nocontact,
vehi.licno, vehi.serialno, vehi.make, vehi.model, vehi.submodel,
convert(varchar,vehi.fregd, 103) as RegDate,
convert(varchar,vehi.nmd, 103) as NextSvcDate,
convert(varchar,vehi.last_service, 103) as LastSvcDate,
convert(varchar,vehi.ninspecday, 103) as NxtInspDate,
convert(varchar,vehi.inspecd, 103) as LastInspDate,
convert(varchar,vehi.next_emission_inspect, 103) as NxtEmiDate,
convert(varchar,vehi.last_emission_inspect, 103) as LastEmiDate

INTO SVCCRM.SVCREM
from vehi
join cust on cust.custno = vehi.custno
where nmd between @StartDate and @EndDate

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-06-23 : 16:52:04

Your stored procedure doesn't return any data. It just puts the data into another table. Don't you want it to display data in the report? If so, then you should add a select statement to query the new table.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

RJ
Starting Member

11 Posts

Posted - 2011-06-23 : 17:13:50

Many thanks, such a simple solution. Added SELECT * FROM SVCCRM.SVCREM at the bottom of the script and it works fine.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-06-23 : 17:57:18

Cool, glad I could help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources