Variable Scope? 1

[jgoodman00]

I have an sp as follows:
CREATE PROCEDURE dbo.sp_Bramble_UPD_InspDates
(@InspectionDate DateTime, @Surveyor int, @InspectionType int)--, @RowsInserted int output, @RowsUpdated int output)
AS

INSERT INTO dbo.HISTORY_tblAssetBramble
(RefNoL, IdentifiedWorksNumber, Spray, Cutdown, RemoveUnwantedSpecies, VisitsPerYear, ReplantingRequired, Retain, DesignRequired,
KillBramble, SoilID, HeightM, InspectionDate, InspectionTypeID, SurveyorID, Priority, Remarks, Budget, Rate)
SELECT RefNo, IdentifiedWorksNumber, Spray, Cutdown, RemoveUnwantedSpecies, VisitsPerYear, ReplantingRequired, Retain, DesignRequired,
KillBramble, SoilID, HeightM, InspectionDate, InspectionTypeID, SurveyorID, Priority, Remarks, Budget, Rate
FROM dbo.qryBramble_Form
WHERE (Priority = 'A' OR Priority = 'B' Or Priority = 'C' OR Priority = 'D' OR Priority = 'E')
AND (DateDiff(mm,[InspectionDate],GetDate()) > 2)
--SET @RowsInserted = @@RowCount

UPDATE dbo.qryBramble_Form
SET InspectionDate = @InspectionDate, SurveyorID = @Surveyor, InspectionTypeID = @InspectionType
FROM dbo.qryBramble_Form
WHERE Priority = 'A' OR Priority = 'B' Or Priority = 'C' OR Priority = 'D' OR Priority = 'E'
--SET @RowsUpdated = @@RowCount
GO

As a test, I have tried adding (below the first batch):
INSERT INTO dbo.tblEnvironmentalHistory (RefNo, ModField, OldValue, NewValue)
SELECT RefNo, 'InspectionDate', InspectionDate, @InspectionDate
FROM dbo.qryBramble_Form



However, it seems the @InspectionDate variable goes out of scope when the first batch is executed. According to the help file, a local variable declared within a batch will not be available outside the batch, but to me this looks like the variable is being declared outside the batch, & as a procedure-level variable, which should be in scope.


James Goodman MCP

http://www.james-goodman.co.uk

 

[rajeevnandanmishra]

Hi James,

Can you tell us that what error message you are getting and on which line of this stored procedure?


---
Raj

 

[jgoodman00]

Basically, the sp will not compile. Taken from SQL Query Analyzer:
ALTER PROCEDURE dbo.sp_Bramble_UPD_InspDates
(@InspectionDate DateTime, @Surveyor int, @InspectionType int)
AS

INSERT INTO dbo.HISTORY_tblAssetBramble
(RefNoL, IdentifiedWorksNumber, Spray, Cutdown, RemoveUnwantedSpecies, VisitsPerYear, ReplantingRequired, Retain, DesignRequired,
KillBramble, SoilID, HeightM, InspectionDate, InspectionTypeID, SurveyorID, Priority, Remarks, Budget, Rate)
SELECT RefNo, IdentifiedWorksNumber, Spray, Cutdown, RemoveUnwantedSpecies, VisitsPerYear, ReplantingRequired, Retain, DesignRequired,
KillBramble, SoilID, HeightM, InspectionDate, InspectionTypeID, SurveyorID, Priority, Remarks, Budget, Rate
FROM dbo.qryBramble_Form
WHERE (Priority = 'A' OR Priority = 'B' Or Priority = 'C' OR Priority = 'D' OR Priority = 'E')
AND (DateDiff(mm,[InspectionDate],GetDate()) > 2)



UPDATE dbo.qryBramble_Form
SET InspectionDate = @InspectionDate, SurveyorID = @Surveyor, InspectionTypeID = @InspectionType
FROM dbo.qryBramble_Form
WHERE Priority = 'A' OR Priority = 'B' Or Priority = 'C' OR Priority = 'D' OR Priority = 'E'

GO

INSERT INTO dbo.tblEnvironmentalHistory (RefNo, ModField, OldValue, NewValue)
SELECT RefNo, 'InspectionDate', InspectionDate, @InspectionDate
FROM dbo.qryBramble_Form
GO



When I attempt to compile it, I get:
Server: Msg 137, Level 15, State 2, Line 4
Must declare the variable '@InspectionDate'.

James Goodman MCP

http://www.james-goodman.co.uk

 

[LFCfan]

Does it error when you include

INSERT INTO dbo.tblEnvironmentalHistory (RefNo, ModField, OldValue, NewValue)
SELECT RefNo, 'InspectionDate', InspectionDate, @InspectionDate
FROM dbo.qryBramble_Form
GO

or do you mean the UPDATE part of the stored proc when you say "second batch"?

 

[jgoodman00]

It errors when the second batch is added, after the first GO keyword...

James Goodman MCP

http://www.james-goodman.co.uk

 

[Rhys666]

Go Signals the end of a batch of Transact-SQL statements, so the INSERT statement is a seperate batch and would need the @InspectionDate variable declared within it's scope.
Remove the GO between the Update and Insert statements.

Rhys
Thought out... Maybe,
Opinionated... Probably
But it is only an opinion!

 

[jgoodman00]

I need them to execute as seperate batches...

James Goodman MCP

http://www.james-goodman.co.uk

 

[LFCfan]

James,

it fails because it is out of scope - the first GO signifies the end of the stored proc code, so you need to declare @InspectionDate
try the following

DECLARE @InspectionDate datetime
INSERT INTO dbo.tblEnvironmentalHistory (RefNo, ModField, OldValue, NewValue)
SELECT RefNo, 'InspectionDate', InspectionDate, @InspectionDate
FROM dbo.qryBramble_Form
GO

This is presuming you dont want the INSERT INTO dbo.tblEnvironmentalHistory part to actually happen inside your stored procedure. If you want to include it in the SP, remove the first GO (and also remove the DECLARE statement i added!)

Hope this makes sense, and that i've understood you correctly!


LFCfan

 

[Rhys666]

Go Signals the end of a batch of Transact-SQL statements is straight from the Book so can't be changed.

Do you mind my asking why you need it to execute as a seperate batch?

Could you create a new sProc for the second batch with the input parameters you want and call it at the end of the first batch, passing the Parameter Values it needs??

Rhys
Thought out... Maybe,
Opinionated... Probably
But it is only an opinion!

 

[Rhys666]

By 'Book' I mean the Sql Server Books Online documentation,
apologies...

Rhys
Thought out... Maybe,
Opinionated... Probably
But it is only an opinion!

 

[jgoodman00]

The reason I am trying to execute it as a seperate batch is that it wont execute as a single one. If I try to execute it as a single batch, it returns an error saying multiple tables would need to be updated...



James Goodman MCP

http://www.james-goodman.co.uk

 

[Rhys666]

Have you tried nesting each element of the process as a child sProc underneath a parent sProc. The Controlling (Parent) sProc would have imput parameters of everything required by all child sProcs, and it can simply call the child sProcs, passing each one the requisite variables for it's part of the process. Wrap it all back with some transaction processing/error, (rollbacks etc.), and you can ensure that if one fails they all rollback to the previous state.


Rhys
Thought out... Maybe,
Opinionated... Probably
But it is only an opinion!

 

[jgoodman00]

That sounds impressive!

Can you give me an example to get started?

James Goodman MCP

http://www.james-goodman.co.uk

 

[Rhys666]

OK.
Set up sProcs for each element of your overall process. I.E.,
1)
CREATE PROCEDURE [dbo].[sp_Bramble_INS_History]
AS

INSERT INTO dbo.HISTORY_tblAssetBramble
(RefNoL, IdentifiedWorksNumber, Spray, Cutdown, RemoveUnwantedSpecies, VisitsPerYear, ReplantingRequired, Retain, DesignRequired,
KillBramble, SoilID, HeightM, InspectionDate, InspectionTypeID, SurveyorID, Priority, Remarks, Budget, Rate)
SELECT RefNo, IdentifiedWorksNumber, Spray, Cutdown, RemoveUnwantedSpecies, VisitsPerYear, ReplantingRequired, Retain, DesignRequired,
KillBramble, SoilID, HeightM, InspectionDate, InspectionTypeID, SurveyorID, Priority, Remarks, Budget, Rate
FROM dbo.qryBramble_Form
WHERE (Priority = 'A' OR Priority = 'B' Or Priority = 'C' OR Priority = 'D' OR Priority = 'E')
AND (DateDiff(mm,[InspectionDate],GetDate()) > 2)

GO

2)
CREATE PROCEDURE [dbo].[sp_Bramble_UPD_Form]
(@InspectionDate DateTime, @Surveyor int, @InspectionType int)
AS

UPDATE dbo.qryBramble_Form
SET InspectionDate = @InspectionDate, SurveyorID = @Surveyor, InspectionTypeID = @InspectionType
FROM dbo.qryBramble_Form
WHERE Priority = 'A' OR Priority = 'B' Or Priority = 'C' OR Priority = 'D' OR Priority = 'E'

GO

3)
CREATE PROCEDURE [dbo].[sp_Bramble_INS_EnvHistory]
(@InspectionDate DateTime)
AS

INSERT INTO dbo.tblEnvironmentalHistory (RefNo, ModField, OldValue, NewValue)
SELECT RefNo, 'InspectionDate', InspectionDate, @InspectionDate
FROM dbo.qryBramble_Form

GO

So each of your Update and Insert elements are handled by their own sProc...

Then script, (always keep a copy!) your existing sProc which is attempting to do everything, drop it and recreate it to call each of the Update and Insert elements in turn
I.E.,

-- Controlling (Parent) sProc --
CREATE PROCEDURE [dbo].[sp_Bramble_UPD_InspDates]
(@InspectionDate DateTime, @Surveyor int, @InspectionType int)
AS
-- Begin transaction
BEGIN TRANSACTION

-- Calls first Insert sProc
EXEC [dbo].[sp_Bramble_INS_History]

-- Calls Update sProc
EXEC [dbo].[sp_Bramble_UPD_Form]
@InspectionDate = @InspectionDate,
@Surveyor = @Surveyor,
@InspectionType = @Inspectiontype

-- Calls second Insert sProc
EXEC [dbo].[sp_Bramble_INS_EnvHistory]
@InspectionDate = @InspectionDate


-- Check transaction error count
IF (@@error <> 0)
-- If there's been an error, roll back everything
BEGIN
ROLLBACK TRANSACTION MyName
END
ELSE
BEGIN
-- Otherwise commit the changes to the database
COMMIT TRANSACTION
END

GO

This compiles for me, but I can't run it cause none of the tables etc exist. Let me know how you get on. Also, check out the Sql Server Books Online regarding Transactions, rollbacks and committing changes to the database for more detail in this area. In the above the Transaction in the outermost sProc, wraps up the others within it, so rolling it back, undoes changes made by those sProcs called between the BEGIN TRANSACTION and ROLLBACK TRANSACTION.


Rhys
Thought out... Maybe,
Opinionated... Probably
But it is only an opinion!