Stored Proc Issue


Author
Message
Geoff Hirst
Geoff Hirst
StrataFrame Novice (123 reputation)StrataFrame Novice (123 reputation)StrataFrame Novice (123 reputation)StrataFrame Novice (123 reputation)StrataFrame Novice (123 reputation)StrataFrame Novice (123 reputation)StrataFrame Novice (123 reputation)StrataFrame Novice (123 reputation)StrataFrame Novice (123 reputation)
Group: Forum Members
Posts: 123, Visits: 3.5K
Chaps,

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


Peter Jones
Peter Jones
StrataFrame User (392 reputation)StrataFrame User (392 reputation)StrataFrame User (392 reputation)StrataFrame User (392 reputation)StrataFrame User (392 reputation)StrataFrame User (392 reputation)StrataFrame User (392 reputation)StrataFrame User (392 reputation)StrataFrame User (392 reputation)
Group: Forum Members
Posts: 386, Visits: 2.1K
Hi 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

StrataFrame Team
S
StrataFrame Developer (3.1K reputation)StrataFrame Developer (3.1K reputation)StrataFrame Developer (3.1K reputation)StrataFrame Developer (3.1K reputation)StrataFrame Developer (3.1K reputation)StrataFrame Developer (3.1K reputation)StrataFrame Developer (3.1K reputation)StrataFrame Developer (3.1K reputation)StrataFrame Developer (3.1K reputation)
Group: StrataFrame Developers
Posts: 3K, Visits: 2.5K
Are you deploying through the DDT? 

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

StrataFrame Team
S
StrataFrame Developer (3.1K reputation)StrataFrame Developer (3.1K reputation)StrataFrame Developer (3.1K reputation)StrataFrame Developer (3.1K reputation)StrataFrame Developer (3.1K reputation)StrataFrame Developer (3.1K reputation)StrataFrame Developer (3.1K reputation)StrataFrame Developer (3.1K reputation)StrataFrame Developer (3.1K reputation)
Group: StrataFrame Developers
Posts: 3K, Visits: 2.5K
Yep, Peter is right, the AS is not optional, even without parameters.
Geoff Hirst
Geoff Hirst
StrataFrame Novice (123 reputation)StrataFrame Novice (123 reputation)StrataFrame Novice (123 reputation)StrataFrame Novice (123 reputation)StrataFrame Novice (123 reputation)StrataFrame Novice (123 reputation)StrataFrame Novice (123 reputation)StrataFrame Novice (123 reputation)StrataFrame Novice (123 reputation)
Group: Forum Members
Posts: 123, Visits: 3.5K
Chaps,

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

StrataFrame Team
S
StrataFrame Developer (3.1K reputation)StrataFrame Developer (3.1K reputation)StrataFrame Developer (3.1K reputation)StrataFrame Developer (3.1K reputation)StrataFrame Developer (3.1K reputation)StrataFrame Developer (3.1K reputation)StrataFrame Developer (3.1K reputation)StrataFrame Developer (3.1K reputation)StrataFrame Developer (3.1K reputation)
Group: StrataFrame Developers
Posts: 3K, Visits: 2.5K
Hrm... that is strange.  I can't remember the code explicitly, but we might be trying to Regex off the AS on the front, and if we don't find one, we add it.  Maybe your sproc doesn't pass our Regex, so we can't find out that the AS is missing.  I can't remember.

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.

GO

Merge Selected

Merge into selected topic...



Merge into merge target...



Merge into a specific topic ID...




Similar Topics

Reading This Topic

Login

Explore
Messages
Mentions
Search