Detecting an existence of local temporary table

Hi,
I create a temporary table. for example:
create table #t1 (ID_T1 integer)
Next I would like to detect If the table already exists:
exec sp_table #t1
if @.@.ROWCOUNT > 0
--Table #t1 exists
else
--Table #t1 doesn't exist
But the sp_table stored proc.works only for non-temporary tables and
the result using temporary tables is allways @.@.ROWCOUNT = 0.
Does anyone know, if there exists a solution to detect an existing
temporary table ?
Thanks a lot for any suggestions
LiborIf exists(Select name from sysobjects where name='#t1')
--Table #t1 exists
else
--Table #t1 doesn't exist
Madhivanan|||I'd prefer to use
IF object_id('TableName')IS NOT NULL
PRINT 'Yes'
ELSE
PRINT 'No'
<madhivanan2001@.gmail.com> wrote in message
news:1109142292.789540.177840@.o13g2000cwo.googlegroups.com...
> If exists(Select name from sysobjects where name='#t1')
> --Table #t1 exists
> else
> --Table #t1 doesn't exist
>
> Madhivanan
>|||Temp tables are special. You can't just check against sysobjects, and you ca
n't just check the
object id locally (as they live in tempdb). Try below:
IF OBJECT_ID('tempdb..#t') IS NOT NULL
PRINT 'exists'
ELSE
PRINT 'not exists'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Libor Forejtnik" <lforejtn@.seznam.cz> wrote in message
news:738o11lltb576hp4tdf345qcibnh156sld@.
4ax.com...
> Hi,
> I create a temporary table. for example:
> create table #t1 (ID_T1 integer)
> Next I would like to detect If the table already exists:
> exec sp_table #t1
> if @.@.ROWCOUNT > 0
> --Table #t1 exists
> else
> --Table #t1 doesn't exist
> But the sp_table stored proc.works only for non-temporary tables and
> the result using temporary tables is allways @.@.ROWCOUNT = 0.
> Does anyone know, if there exists a solution to detect an existing
> temporary table ?
> Thanks a lot for any suggestions
> Libor|||On 22 Feb 2005 23:04:52 -0800, madhivanan2001@.gmail.com wrote:

>If exists(Select name from sysobjects where name='#t1')
> --Table #t1 exists
>else
> --Table #t1 doesn't exist
>
>Madhivanan
Yes, the query should be exactlyt:
if exists(select * from tempdb..sysobjects where name like '#t1%')
.
.
but such query cannot be used for this purpose: while one session
creates a #t1 table and then other session performs such query
without creating is's own #t1 table, the result is allways more
than 0 rows.
Somewhere on server resides a relationship between a record of
tempdb..sysobject with name like '#t1%' and the user session. I tried
to recover such relationship, but (so far) I was unsuccessfull.
Libor|||On Wed, 23 Feb 2005 08:59:44 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:

>Temp tables are special. You can't just check against sysobjects, and you c
an't just check the
>object id locally (as they live in tempdb). Try below:
>IF OBJECT_ID('tempdb..#t') IS NOT NULL
>PRINT 'exists'
>ELSE
>PRINT 'not exists'
This works very good. Thanks
Libor

design pattern question

Say I have an object that can be created by some users and not others. And, I have a method something like -function createObject(userId as Integer, objectName as String). The method calls a stored procedure using the two input arguments as parameters.
Now, what I am wondering is this; I am obviously going to check in my SP to ensure that the user has the rights to create the new object before inserting the new record, and there will be an output parameter to send the new record ID back to the method, but do I
a) have a separate output parameter to report on the success of the creation (e.g. @.Err Int OUTPUT)
b) use the existing output (used to send the new record ID back) and send an error code (e.g. if newRecordId = -9999)
c) somehow try to cause a failure of the SP that can be caught in a try/catch block
Thanks in advance,
MartinI would use option #2. Since you already have this variable, it can easily be checked for an error code and Im sure you are probably doing other types of checks as well. I would never ever ever use option #3. try/catches should always only be used from true exceptions. I always send myself an error email when a catch is experienced and would always be receiving these emails when there really wasnt an error.
Nick

design Index problem

Hi all,
I have a very large table with many columns: dateTime type, nvarchar
type and integer field type.
A program exec many type of query with where clause.
Data field is always in where clause, but some other field is present
too. Sometimes integer field, sometimes nvarchar field.
Now I must create index for the query!
For choose index field what i can do?
I must create only index on a datetime field or every combination in
every type of query?
In the second case I must create very much index! But this is very
dispendious for update/insert/delete operation on the table!!!
Some ideas?
thnxEnorme Vigenti (LSimon5@.libero.it) writes:

Quote:

Originally Posted by

I have a very large table with many columns: dateTime type, nvarchar
type and integer field type.
A program exec many type of query with where clause.
Data field is always in where clause, but some other field is present
too. Sometimes integer field, sometimes nvarchar field.
Now I must create index for the query!
For choose index field what i can do?
I must create only index on a datetime field or every combination in
every type of query?
In the second case I must create very much index! But this is very
dispendious for update/insert/delete operation on the table!!!

How selective is the datetime column? If all queries are for a single
day, maybe an index on that column is sufficient, preferrably a clustered
index.

But if queries can be for longer periods of time, that may address too many
rows, and in such case you will need to add more indexes. How these indexes
should be designed depends on the queries. If a query can be on account
number and a date interval, it's probably better to have the account number
first in that index.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||If you could post an example Query ?

--

Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com
"Enorme Vigenti" <LSimon5@.libero.itwrote in message
news:0kZSi.151009$U01.1108206@.twister1.libero.it.. .

Quote:

Originally Posted by

Hi all,
I have a very large table with many columns: dateTime type, nvarchar type
and integer field type.
A program exec many type of query with where clause.
Data field is always in where clause, but some other field is present too.
Sometimes integer field, sometimes nvarchar field.
Now I must create index for the query!
For choose index field what i can do?
I must create only index on a datetime field or every combination in every
type of query?
In the second case I must create very much index! But this is very
dispendious for update/insert/delete operation on the table!!!
Some ideas?
thnx

design best practices on series number

Hi,

I need to design a table header for inventory transactions with the specifications as follows:

1. System-generated series numbers (integer)

2. Series numbers must be unique by branch by transaction type. Thus if I have following:

Branches: Br1, Br2

Transaction Type: SRS (Stock Receipt from Supplier), SRB(.. from Branch)

The series number must be implemented in such a way that,

Br1 SRS 0000000001

Br1 SRB 0000000001

Br2 SRS 0000000001

Br2 SRB 0000000001

Then, in every INSERT, series number should be incremented by 1, grouped by branch by transaction type. That is, after INSERT with Br1/SRB the figure may now look like,

Br1 SRS 0000000001

Br1 SRB 0000000002

How do I design my table in order to achieve this? Note that this table header will have a detail (master/detail) referenced by foreign key.

Thanks in advance.

What I have come up so far are the following:

1. Create an identity field which will be the designated PK for the table header.

2. Branch, Trx_Type, SeriesNum will be a compounded index with a unique constraint.

3. In the branch office, there will be a shared text file containing the last series number for the branch so that, upon saving the transaction (setup is real-time online), the system will get the last series number from the file then increment by 1 and use it as the series number for the transaction. Upon completion, the system will update the file with the new last series number.

I need your comments on this, and if you have a better solution, pls let me know.