I have a stored proc, that returns the full contents of a table, some 21 columns. Now, I have no parameters on this proc, as I intend to use it for list population etc.

Thing is, it won't deploy. I get an error that says a problem occurred with a stored proc. A problem near the word 'SELECT'.

Now if I add

@ID int

AS

at the top of the proc, it deploys fine.

Proc is

Select ID,JobNo,ProjectName,WorkDescription,ClientID,ClientContact,ContactDetails,JobManager,JobArchitect,

JobType,ClientType,EnduserCLientType,SourceofWork,Timebased,FixedFee,PercentageValueOfProject,

ExpensesID,ProjectFee,EstimatedProjectValue,ProjectPercentage,ProjectFee2 from Job

If I replace the text with SELECT * from Job it also works fine.

Any Ideas chaps?

thanks

Geoff

If don't think the AS is optional in a proc, e.g.

CREATE PROCEDURE dbo.myProc

AS

    SELECT * FROM MyTable

GO

Maybe omitting the AS is causing the problem?

Cheers, Peter

If you are, then pay close attention to the labels of the Stored Procedure form... they list the code that will be executed when deploying the procedure...

After the "CreateProcedure" you would still need to place the AS keyword, event if you don't use any parameters.  So, the textbox would contain:

AS
Select ID,JobNo,ProjectName,WorkDescription,ClientID,ClientContact,ContactDetails,JobManager,JobArchitect, JobType,ClientType,EnduserCLientType,SourceofWork,Timebased,FixedFee,PercentageValueOfProject, ExpensesID,ProjectFee,EstimatedProjectValue,ProjectPercentage,ProjectFee2 from Job

Thanks for this. Indeed in this case adding the 'AS' does resolve the issue.

What I do have though are other procedures in the DDT, that do not have the AS in the proc, but return only a small number of columns and they have deployed fine.

If you can replicate this I would be interested to know.

thanks Geoff

However, if you posted an example sproc of one that deploys fine without the AS and one that requires the AS to deploy, then I might be able to figure something out.