dynamically creating temp table names

Hello,
I am interested in dynamically creating temp tables using a
variable in MS SQL Server 2000.

For example:

DECLARE @.l_personsUID int

select @.l_personsUID = 9842

create table ##Test1table /*then the @.l_personsUID */
(
resultset1 int

)

The key to the problem is that I want to use the variable
@.l_personsUID to name then temp table. The name of the temp table
should be ##Test1table9842 not ##Test1table.

Thanks for you help.

Billy"Billy Cormic" <billy_cormic@.hotmail.com> wrote in message
news:dd2f7565.0311251937.cf18cf9@.posting.google.co m...
> Hello,
> I am interested in dynamically creating temp tables using a
> variable in MS SQL Server 2000.
> For example:
> DECLARE @.l_personsUID int
> select @.l_personsUID = 9842
> create table ##Test1table /*then the @.l_personsUID */
> (
> resultset1 int
>
> )
> The key to the problem is that I want to use the variable
> @.l_personsUID to name then temp table. The name of the temp table
> should be ##Test1table9842 not ##Test1table.

May I ask why?

You can probably do this by dynamically building the string.

But it's going to be messy.

> Thanks for you help.
> Billy|||billy_cormic@.hotmail.com (Billy Cormic) wrote in message news:<dd2f7565.0311251937.cf18cf9@.posting.google.com>...
> Hello,
> I am interested in dynamically creating temp tables using a
> variable in MS SQL Server 2000.
> For example:
> DECLARE @.l_personsUID int
> select @.l_personsUID = 9842
> create table ##Test1table /*then the @.l_personsUID */
> (
> resultset1 int
>
> )
> The key to the problem is that I want to use the variable
> @.l_personsUID to name then temp table. The name of the temp table
> should be ##Test1table9842 not ##Test1table.
> Thanks for you help.
> Billy

You could use dynamic SQL, but that would not be a good solution. If
the table names are dynamic, then all code accessing the tables would
need to be dynamic also, and that will create a lot of issues.

A better approach would be to have a single, permanent table, with
personsUID as part of the key. See here for a good discussion of this
issue:

http://www.algonet.se/~sommar/dynam...html#Sales_yymm

Simon|||I want to do this so that i can create individual tables to set as
datasources for certain crystal reports.

"Greg D. Moore \(Strider\)" <mooregr@.greenms.com> wrote in message news:<2jWwb.144035$ji3.17559@.twister.nyroc.rr.com>...
> "Billy Cormic" <billy_cormic@.hotmail.com> wrote in message
> news:dd2f7565.0311251937.cf18cf9@.posting.google.co m...
> > Hello,
> > I am interested in dynamically creating temp tables using a
> > variable in MS SQL Server 2000.
> > For example:
> > DECLARE @.l_personsUID int
> > select @.l_personsUID = 9842
> > create table ##Test1table /*then the @.l_personsUID */
> > (
> > resultset1 int
> > )
> > The key to the problem is that I want to use the variable
> > @.l_personsUID to name then temp table. The name of the temp table
> > should be ##Test1table9842 not ##Test1table.
> May I ask why?
> You can probably do this by dynamically building the string.
> But it's going to be messy.
>
> > Thanks for you help.
> > Billy|||>> I am interested in dynamically creating temp tables using a
variable in MS SQL Server 2000. <<

Learn to write correct SQL instead. The use of temp tables is usually
a sign of really bad code -- the temp tables are almost always used to
hold steps in a procedural solution instead of a having a set-oriented
non-proceudral solution. This also says that you have no data model
and that any user, present or future, can change it on the fly.

Oh, if you don't care about performance, portability, readability,
security, and all that other stuff, then you can use dynamic SQL to
screw up your application this way.|||OK. I will just create anohter table... not a bunch of temp tables to
hold the results.

thanks

joe.celko@.northface.edu (--CELKO--) wrote in message news:<a264e7ea.0311261052.12098cb6@.posting.google.com>...
> >> I am interested in dynamically creating temp tables using a
> variable in MS SQL Server 2000. <<
> Learn to write correct SQL instead. The use of temp tables is usually
> a sign of really bad code -- the temp tables are almost always used to
> hold steps in a procedural solution instead of a having a set-oriented
> non-proceudral solution. This also says that you have no data model
> and that any user, present or future, can change it on the fly.
> Oh, if you don't care about performance, portability, readability,
> security, and all that other stuff, then you can use dynamic SQL to
> screw up your application this way.

Dynamic Where clause

What is the best way to dynamically choose the where clause based on a
variable.
In the following test example, depending on @.i value, WHERE clause could
compare against being null or not null.
Another way to do would be writing ugly sql string like @.select + @.where
Please let me know.
TIA...
set nocount on
go
create table z_test_del
(
c1 int,
c2 int
)
go
insert z_test_del values(1,null)
insert z_test_del values(2,333)
insert z_test_del values(3,null)
insert z_test_del values(4,5555)
go
declare @.i int
set @.i = 0
if (@.i = 0)
select * from z_test_del where c2 is null
else
select * from z_test_del where c2 is not null
go
drop table z_test_del
go>> What is the best way to dynamically choose the where clause based on a
variable. <<
Dynamic is poor choice of words in SQL -- it implies that you are
writing code on the fly.
SELECT *c1, c2 -- never use * in production code!!
FROM Foobar
WHERE (c2 IS NULL AND @.flag = 0)
OR (c2 IS NOT NULL AND @.flag <> 0);|||You can use a CASE or use OR-ed predicates or write two separate statements.
For some alternatives refer to: http://www.sommarskog.se/dyn-search.html
Anith|||Thanks Joe.
"--CELKO--" wrote:

> variable. <<
> Dynamic is poor choice of words in SQL -- it implies that you are
> writing code on the fly.
> SELECT *c1, c2 -- never use * in production code!!
> FROM Foobar
> WHERE (c2 IS NULL AND @.flag = 0)
> OR (c2 IS NOT NULL AND @.flag <> 0);
>

Dynamic SQL Statement for Cursor

Hi
I have a cursor that I need to pass a dynamic sql statement to that is in a
variable, is this possible?
e.g. DECLARE curOneRecord CURSOR FOR @.SQL
Thanks
BBoth cursors and dynamic SQL are considered bad practices. Anyhow, if you st
ill want to do this:
EXEC('DECLARE curOneRecord CURSOR FOR ' + SQL)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <Ben@.Newsgroups.microsoft.com> wrote in message news:u8pL9ZkqFHA.904@.TK2MSFTNGP10.phx
.gbl...
> Hi
> I have a cursor that I need to pass a dynamic sql statement to that is in
a
> variable, is this possible?
> e.g. DECLARE curOneRecord CURSOR FOR @.SQL
> Thanks
> B
>|||Ben,
Do the contrary, open the cursor inside the dynamic sql statement.
Example:
declare @.c cursor
declare @.sql nvarchar(4000)
set @.sql = N'
set @.c = cursor for
select
orderid, customerid, orderdate
from
dbo.orders
where
customerid = @.customerid; open @.c'
exec sp_executesql @.sql, N'@.c cursor output, @.customerid nchar(5)', @.c
output, 'alfki'
if cursor_status('variable', '@.c') = 1
begin
while 1 = 1
begin
fetch next from @.c
if @.@.error != 0 or @.@.fetch_status != 0 break
end
end
if cursor_status('variable', '@.c') >= 0
close @.c
deallocate @.c
go
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
Did you try to find a set-based solution before using cursors?
AMB
"Ben" wrote:

> Hi
> I have a cursor that I need to pass a dynamic sql statement to that is in
a
> variable, is this possible?
> e.g. DECLARE curOneRecord CURSOR FOR @.SQL
> Thanks
> B
>
>|||an example ofr dynamic
sql statement. It might help
use northwind
declare @.test nvarchar(4000)
declare @.values nvarchar (200)
set @.values='1,2,3,4'
set @.test='select * from employees where employeeid in ('+ (@.values)+')'
EXEC(@.test)
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Ben" wrote:

> Hi
> I have a cursor that I need to pass a dynamic sql statement to that is in
a
> variable, is this possible?
> e.g. DECLARE curOneRecord CURSOR FOR @.SQL
> Thanks
> B
>
>

Dynamic SQL Statement for Cursor

Hi
I have a cursor that I need to pass a dynamic sql statement to that is in a
variable, is this possible?
e.g. DECLARE curOneRecord CURSOR FOR @.SQL
Thanks
BBoth cursors and dynamic SQL are considered bad practices. Anyhow, if you st
ill want to do this:
EXEC('DECLARE curOneRecord CURSOR FOR ' + SQL)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <Ben@.Newsgroups.microsoft.com> wrote in message news:u8pL9ZkqFHA.904@.TK2MSFTNGP10.phx
.gbl...
> Hi
> I have a cursor that I need to pass a dynamic sql statement to that is in
a
> variable, is this possible?
> e.g. DECLARE curOneRecord CURSOR FOR @.SQL
> Thanks
> B
>|||Ben,
Do the contrary, open the cursor inside the dynamic sql statement.
Example:
declare @.c cursor
declare @.sql nvarchar(4000)
set @.sql = N'
set @.c = cursor for
select
orderid, customerid, orderdate
from
dbo.orders
where
customerid = @.customerid; open @.c'
exec sp_executesql @.sql, N'@.c cursor output, @.customerid nchar(5)', @.c
output, 'alfki'
if cursor_status('variable', '@.c') = 1
begin
while 1 = 1
begin
fetch next from @.c
if @.@.error != 0 or @.@.fetch_status != 0 break
end
end
if cursor_status('variable', '@.c') >= 0
close @.c
deallocate @.c
go
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
Did you try to find a set-based solution before using cursors?
AMB
"Ben" wrote:

> Hi
> I have a cursor that I need to pass a dynamic sql statement to that is in
a
> variable, is this possible?
> e.g. DECLARE curOneRecord CURSOR FOR @.SQL
> Thanks
> B
>
>|||an example ofr dynamic
sql statement. It might help
use northwind
declare @.test nvarchar(4000)
declare @.values nvarchar (200)
set @.values='1,2,3,4'
set @.test='select * from employees where employeeid in ('+ (@.values)+')'
EXEC(@.test)
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Ben" wrote:

> Hi
> I have a cursor that I need to pass a dynamic sql statement to that is in
a
> variable, is this possible?
> e.g. DECLARE curOneRecord CURSOR FOR @.SQL
> Thanks
> B
>
>