Prev: Running a query in SSIS thwn importing Excel spreadsheets
Next: Building Dynamic Case Statement
From: Roy Goldhammer on 8 Aug 2010 07:59
Hello there

I have two procedures which one calls another. Proc1 sometimes declare @@var
and sometimes not.

I would like to build statement on Proc2 that if @@var exists do one else do
another thing.

Is there a way to do it without try..catch?


From: Dan Guzman on 8 Aug 2010 10:06
> I have two procedures which one calls another. Proc1 sometimes declare
> @@var and sometimes not.
>
> I would like to build statement on Proc2 that if @@var exists do one else
> do another thing.
>
> Is there a way to do it without try..catch?

I'm not sure I understand your question. Declared variables are local in
scope regardless of the number of '@' prefixes. Variables declared in Proc1
are not visible to Proc2 and visa-versa. You can have the same name
declared in both procs but these are actually different variables.

One typically uses parameters to pass data between procedures. When
parameters are optional, you can assign a default value and take different
actions accordingly. See example below.

CREATE PROC dbo.Proc2
@var int = NULL
AS
IF @var IS NULL
BEGIN
--do something
END
ELSE
BEGIN
--do something else
END
GO

CREATE PROC dbo.Proc1
AS
EXEC dbo.Proc2;
EXEC dbo.Proc2 @var = 1;
GO


--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Roy Goldhammer" <royg(a)yahoo.com> wrote in message
news:edK76EvNLHA.2276(a)TK2MSFTNGP06.phx.gbl...
> Hello there
>
> I have two procedures which one calls another. Proc1 sometimes declare
> @@var and sometimes not.
>
> I would like to build statement on Proc2 that if @@var exists do one else
> do another thing.
>
> Is there a way to do it without try..catch?
>
From: Roy Goldhammer on 8 Aug 2010 10:27
Whell Dan.

This is what i'm trying to do with local\global variables and not by
temporary tables.

I have table who have trigger that insert data to audit.

The table is being updated from many procedures.

one of the procedure update specific data 200 approx in minute.

I would like not do insert data to autid when this specific update occur.

A way i found out to do it is by creating temp table ##temp. set the spid on
it and on trigger if the table exists and the spid in the table is the same
as the one of the trigger don't insert data to audit.

the problem that it cannot work when i have 60 updates in a second even when
i drop the table after doing the update.

so i thought to solve it by using @@gloabl variable. but i couldn't find a
way to know if this variable exists or not.

"Dan Guzman" <guzmanda(a)nospam-online.sbcglobal.net> wrote in message
news:72BBF0B3-DA37-40BF-9398-788E5A15C094(a)microsoft.com...
>> I have two procedures which one calls another. Proc1 sometimes declare
>> @@var and sometimes not.
>>
>> I would like to build statement on Proc2 that if @@var exists do one else
>> do another thing.
>>
>> Is there a way to do it without try..catch?
>
> I'm not sure I understand your question. Declared variables are local in
> scope regardless of the number of '@' prefixes. Variables declared in
> Proc1 are not visible to Proc2 and visa-versa. You can have the same name
> declared in both procs but these are actually different variables.
>
> One typically uses parameters to pass data between procedures. When
> parameters are optional, you can assign a default value and take different
> actions accordingly. See example below.
>
> CREATE PROC dbo.Proc2
> @var int = NULL
> AS
> IF @var IS NULL
> BEGIN
> --do something
> END
> ELSE
> BEGIN
> --do something else
> END
> GO
>
> CREATE PROC dbo.Proc1
> AS
> EXEC dbo.Proc2;
> EXEC dbo.Proc2 @var = 1;
> GO
>
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
>
> "Roy Goldhammer" <royg(a)yahoo.com> wrote in message
> news:edK76EvNLHA.2276(a)TK2MSFTNGP06.phx.gbl...
>> Hello there
>>
>> I have two procedures which one calls another. Proc1 sometimes declare
>> @@var and sometimes not.
>>
>> I would like to build statement on Proc2 that if @@var exists do one else
>> do another thing.
>>
>> Is there a way to do it without try..catch?
>>


From: Erland Sommarskog on 8 Aug 2010 11:00
Roy Goldhammer (royg(a)yahoo.com) writes:
> so i thought to solve it by using @@gloabl variable. but i couldn't find a
> way to know if this variable exists or not.

There is no such thing like a global variable in SQL Server.

But there was once. That is, what now are known as the "system
functions" @@spid, @@rowcount etc, were once known as "global
variables", but Microsoft renamed them with SQL7. Nevertheless, these
functions are different from all other functions as you can use them
as actual parameters to stored procedures as well as default values to
parameters.

I don't recall if it ever was possible to create your own global variables,
but I don't think it was.

> I have table who have trigger that insert data to audit.
>
> The table is being updated from many procedures.
>
> one of the procedure update specific data 200 approx in minute.
>
> I would like not do insert data to autid when this specific update occur.

OK, so I lied. There is a way to have process-global values in SQL Server.
Actually there are two.

The one I would use in this case is this:

-- Skip auditing if we are cheating.
IF object_id('tempdb..#dont$audit') IS NOT NULL
RETURN

That is, you can use the mere existence of a temp table as a global
flag to control a trigger. Just make sure that you give it a name
which is unique in your code base!

The other method is to use the command SET CONTEXT_INFO to set the value
and then read it with the context_info() function. The context_info
data is varbinary(128), so you have more than one value. The typical
use for context_info is to store the name of the actual user in an
application that uses the same login for all users. Audit triggers
can then get the user name from this value.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Running a query in SSIS thwn importing Excel spreadsheets
Next: Building Dynamic Case Statement