Stored Procedure Containing Insert 1

[stoolpigeon]

Is there anything special I need to know to write a stored procedure that is an Insert query?

I am running 6.5. I am trying to write a stored procedure that will insert a new row into a table.

I am logged onto the database as SA and I am the dbo. When I write out the proc (using create proc and all that good stuff) it tells me that I have error 208- "Invalid object name"

I have used the full name- dbo.procname, and I have used the exact same name to create a select query and it was created w/no problems.

As dbo I know I have full permissions to the table it should be adding to.

I've looked all over and I feel like I'm missing something that may be obvious.

 

[stoolpigeon]

Well I've got a new question.

I got my stored procedure to take by removing the following from the end:

"GRANT EXECUTE ON dbo.procname TO Public"

Why can't I do that? And- will this cause a problem when I try to use this proc through my application?

I have a process that generates all these notes and I just want a quick way to add them from the app.

Well maybe no one will look at this now that it has a reply but I'm hoping. And the key questions here, I can answer myself when I try it out. But I thought there might be some wisdom to gain.

thanks,
Ron

 

[tlbroadbent]

When you include the Grant statement in the same batch as the Create procedure statement, SQL analyzes the query and at that point the proc doesn't exist because it hasn't yet been created. Therefore, no permissions can be granted.

Insert a GO on the line before the Grant statement and you'll have no problem. If you've already created the procedure, just execute the grant statement by itself in ISQL/w. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[stoolpigeon]

Terry,

You seem to do most of the answering around here, I appreciate you getting back to me on that.

I had a GO in there. I tried adding it again once the stored procedure was there- but when I re ran it, it disappeared. It's kind of odd.

The books online says that an insert has to follow batch rules and that it can't be mixed w/other things but I had seperated everything w/"GO".

Usually when I create a Stored Procedure- I just open up one that already exists and then modify it and run it. Then it already is in the format of

GO
If exists...
GO
create proc as....
GO
Grant ....
GO

But for some reason I could not do the insert that way. (All the rest of the stored procedures to this point have been select statements)

My app seems to work o.k. now running it.

Our database has never had proper administration and it shows. I'm trying to clean up the code on the front end (that's my job) and fix up the back end (not my job but it needs to be done.)

thanks again.

 

[tlbroadbent]

Stored procedures cannot contain embedded Go. The Create Procedure statement would end at the first Go SQL encounters. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.