Precompiling checking problem

i have a stored procedure i want to save
the commands all execute fine if i execute them one at a time, but when i
try to create a sp out of the script, it fails
problem... my sp adds a coulmn to a table in the begining, does some
processing and uses that column, then drops it.
the process that saves the sp tries to validate each command individually
instead of as a progression so obviously a select statement with the new
column is not valid on its own and it wont process the script.
is their any directive in QA that can turn off this checking?
i have a work around, build the query dynamically, but this is a pain as far
as im concerned.
any suggestions?
not sure I understand completely, BUT I think your problem may be resolved
by placing GO statements after each step in the sproc.
try that (if you havent already) and script the thing out and paste it in
here if that still does not work..
that we we can all have a look at it and get through the guesswork.
Greg Jackson
PDX, Oregon
|||example:
table1 has column1, column2, column3
i create a script to make a stored procedure in query analyzer:
create proc test1 as
alter table to add column4
update table1 to set column4 = to something
update table1 to set other columns to something
alter table to drop column4
go
1) execute each internal line seperately, each runs without error
2) execute script to create the proc, ERROR cause it checks each command
before creating the proc and since the second line sets column4 to
something, and column4 is not there right now, its an invalid command and
the proc isnt created
3) cant put go's between the commands in a stored procedure and even if you
could, the system still wont save the procedure cause line2 is still invalid
at this moment in time.
|||Claude Hebert wrote:
> example:
> table1 has column1, column2, column3
> i create a script to make a stored procedure in query analyzer:
> create proc test1 as
> alter table to add column4
> update table1 to set column4 = to something
> update table1 to set other columns to something
> alter table to drop column4
>
Without trying to understand what you are doing, the problem is that
each of those statements must be in its own batch. You have to use
dynamic sql to do what you want:
create table dbo.test1 (col1 int)
go
insert into dbo.test1 values (1)
create proc dbo.test2 as
begin
EXEC ('alter table dbo.test1 add column4 int')
select * from dbo.test1
EXEC ('update dbo.test1 set column4 = 5')
select * from dbo.test1
EXEC ('alter table dbo.test1 drop column column4')
select * from dbo.test1
end
go
exec test2
drop proc dbo.test2
create table dbo.test1
David Gugick
Imceda Software
www.imceda.com
|||i know that works, and this is a very simple case...
because of the size of our project, the number of procedures, the number of
lines in those procedures that would have to be moved into EXEC statements,
i was trying to find another way...
i was just hopping there was a set something that i could do in QA to allow
the code to run without all the EXEC's
im guessing at this point, the answer is NO
|||Hi Greg
GO is a batch separator for the client, it is not a SQL statement. GO tells
the CLIENT to send a set of commands to SQL Server separately (that is what
a batch is).
GO is not possible inside a stored procedure, which always executes within a
single batch. In fact, if you are trying to create a stored proc in the
Query Analyzer, as soon as a GO is entered, that is the end of the
procedure.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:eG51le%23VFHA.2616@.TK2MSFTNGP14.phx.gbl...
> not sure I understand completely, BUT I think your problem may be resolved
> by placing GO statements after each step in the sproc.
> try that (if you havent already) and script the thing out and paste it in
> here if that still does not work..
> that we we can all have a look at it and get through the guesswork.
>
> Greg Jackson
> PDX, Oregon
>
|||yep my bad.
I didnt read carefully enough. I though he was talking about a SQL Script.
GAJ