Variable problem

[mikej336]

I have the following code...

In testing my variable worked fine. But I only had one query.

I find this works if I redefine the variable after each "go".

Is there a way to define the vaiable for the whole script.
I would probably call it a global variable.

Thanks

Uncle Mike



--Get the date from a table and use that for the Billing date

declare @mydate datetime
set @mydate = '9/1/2007'
--set @mydate=(select [BillingDate] from [400_Metafile].[dbo].[BillingDate] WHERE [DateDesc]='Custbills')

print @mydate

-- Update the account Number From the Filename

UPDATE [MetaViewer Enterprise].[dbo].[BUG Customer Bills]
SET [AccountNumber] = left([Filename],10)
WHERE [AccountNumber] is null

GO
print @mydate

-- Update the branch

UPDATE [MetaViewer Enterprise].[dbo].[BUG Customer Bills]
SET [Branch] = substring([filename], CHARINDEX('_',[filename])+1,
CHARINDEX('.',[filename]) - CHARINDEX('_',[filename])-1)
WHERE CHARINDEX('_',[filename])>0 and [branch] is null

GO
print @mydate

--Update the ActBranch where is not null

UPDATE [MetaViewer Enterprise].[dbo].[BUG Customer Bills]
SET [ActBrch] = [Accountnumber] + [Branch]
where [branch] is not null and [ActBrch] is null

Go
print @mydate

--Update the ActBranch where is null

UPDATE [MetaViewer Enterprise].[dbo].[BUG Customer Bills]
SET [ActBrch] = [Accountnumber]
where [branch] is null and [ActBrch] is null

GO
print @mydate

-- Update the Billing Date to the Date to the billing period
print @mydate

UPDATE [MetaViewer Enterprise].[dbo].[BUG Customer Bills]
SET [BillingDate] = @mydate
WHERE [BillingDate] is Null

GO

-- View what we just did

SELECT Filename, AccountNumber, Branch, ActBrch, BillingDate
FROM [MetaViewer Enterprise].[dbo].[BUG Customer Bills]
Where [BillingDate] = @mydate

GO

 

[LNBruno]

If I understand you correctly, you might try:

1) Getting rid of all the GO statements (not needed).
2) Declaring and setting the variable right before the query where it is used.

< M!ke >
Your right to an opinion does not obligate me to take you seriously.
- Winston Churchill

 

[mikej336]

After hours of searching I think I found my answer... But it leads me to anouther question. I have always use 'GO' but is it needed?

In the example above.

Thanks

Uncle Mike

From SQL Manual....

Users must follow the rules for batches. For example, any execution of a stored procedure after the first statement in a batch must include the EXECUTE keyword. The scope of local (user-defined) variables is limited to a batch, and cannot be referenced after a GO command.

Copy Code
USE AdventureWorks;
GO
DECLARE @MyMsg VARCHAR(50)
SELECT @MyMsg = 'Hello, World.'
GO -- @MyMsg is not valid after this GO ends the batch.

-- Yields an error because @MyMsg not declared in this batch.
PRINT @MyMsg
GO

SELECT @@VERSION;
-- Yields an error: Must be EXEC sp_who if not first statement in
-- batch.
sp_who
GO


SQL Server applications can send multiple Transact-SQL statements to an instance of SQL Server for execution as a batch. The statements in the batch are then compiled into a single execution plan. Programmers executing ad hoc statements in the SQL Server utilities, or building scripts of Transact-SQL statements to run through the SQL Server utilities, use GO to signal the end of a batch.

 

[mikej336]

Thanks LNBruno... I guess that answers both questions.

Thanks again.

Uncle Mike

 

[gmmastros]

>> After hours of searching

It appears as though Mike answered your question within 6 minutes.

-George

"the screen with the little boxes in the window." - Moron

 

[ESquared]

You don't need any of those GOs.

Each separate GO is an instruction to the client program, (not the server), to submit the instructions in each block separately.

Some instructions require being submitted in a single batch, namely, the creation of triggers, SPs, views and functions. All other statements don't require GO, unless some kind of object dependency is in place which would fail the parsing of a later query (such as trying to do something with a table that is created earlier only in dynamic SQL, or trying to create and drop an index which since the index doesn't exist yet parsing will fail).

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]