How do I assign a variable field name?

[delphidestructor]

CREATE PROCEDURE testdynamtable
AS
set nocount on

declare @tmpcolname varchar(20)

set @tmpcolname = 'col1'

create table #tmptable (@tmpcolname int)
^
|
How do I do this?

GO

Mike

 

[skuhlman]

declare @tmpcolname varchar(20)
set @tmpcolname = 'col1'

declare @FinalSQL = nvarchar(4000)

set @FinalSQL = 'create table #tmptable ('+@tmpcolname+' int)'

exec (@FinalSQL)

 

[delphidestructor]

I have tried this and it does work for creating the table, apparently. The problem I run into now is inserting data into the field and returning the temp table.

neither

set @FinalSQL = ('insert into #tmptable values(888)')
exec(@FinalSQL)

or

insert into #tmptable values(888)

works.

Invalid object name '#tmptable'. is returned with either.

Mike

 

[donutman]

I don't know the answer, but let me ask a basic question. Why are you writing a dynamic SQL statement? I'm beginning to think that dynamic SQL should be part of the Axis of Evil.
-Karl

[red] Cursors, Triggers and User Definded Functions are part of the Axis of Evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLBill]

It may be due to the use of a LOCAL temporary table. Once you leave the session that created the table, the table is lost to others (out of scope).

Try using a global temporary table (##temptablename).

-SQLBill

 

[delphidestructor]

To answer Karl. I work in a laboratory environment. If I designed the database to hold all the possible test results for a sample I would have tables with 500 + columns with only 5 – 20 or so of those fields with actual values. It seems better to me to store the values in table with a reference value that accesses a table to tell me if this a pH value, nitrate value, etc. I can then build virtual tables, if I can get it to work, based on defined tests. If I want to store a different value for a new test I do not have to add another field to this already enormous table I just add a record to the test details table and reference this record with the new upload result to tell me what value this represents for the sample being tested.

Mike

 

[donutman]

You're on the right track Mike, but you need to have at least three tables to do it "right": a sample table, a type of test table and a test result table.
The sample table should have a primary key that is then inserted in a column of the test result table (which has it's own primary key). Another column in the result table is the primary key of the type of test for the particular result you performed.
You may need additional tables (or columns) that handle situations where a particular type of test has more than 1 result that you have to record. So I'm sure there will be some complications, but I hope this gives you some direction. If you need an example of the table structure let me know.
-Karl

[red] Cursors, Triggers and User Definded Functions are part of the Axis of Evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[skuhlman]

Karl
I've run across something similar to Mike. We have many reports that are too complex for Crystal Reports to handle by itself. You can however, link the CR to a stored procedure and pass a few parameters. The SP will then take those parameters and produce result sets that are in a similar format even though they pull entirely different types of data based upon the parameters.

Bill & Mike
I'm curious to know if the ##Temp thing works. I think that I have tried that before and never could get it functioning. My assumption was that the ##Temp file is shared to all users as long as the creating connection is open, however, the 'exec (@finalsql)' runs using its own connection which is immediatly terminated once the statement has finished. Maybe I'm all messed up, but... Looking back at all my uses in the code, I find the same scenario. It always looks like:

create table #Temp (fieldx varchar(20), .......)
declare @FinalSQL nvarchar(4000)
set @FinalSQL = '...........'
insert into #Temp
    exec (@FinalSQL)
rest of the code....

Because the "rest of the code..." always requires a common layout in my usage, I always create the field list as I need them in the following code. The the @FinalSQL string is built differently based upon parameters and field types. By doing this, I could have an integer, datetime, character, bit... field all stored as fieldx withing the #Temp file. Convert/Cast statements in the @FinalSQL are used to switch integers and dates and... to characters. The resulting table is one that has a field I can use for grouping and sorting and other things, but the field will always have a consistant name.

 

[donutman]

Skuhlman, very cool. I agree that Crystal Reports changes things quite a bit. I hate the fact that you can't easily change the SP that CR refers to without trashing the report design. I like your solution.
I have a growing feeling, however, that the vast majority of posts about dynamic SQL comes from neophytes who are not solving the problem the "right way". It makes sense that if they have to ask about dynamic SQL syntax, then they PROBABLY don't know when to use it.
-Karl

[red] Cursors, Triggers and User Definded Functions are part of the Axis of Evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[delphidestructor]

Karl, yes I do have tables you said I needed. The samples table, results table, and a test details table. I didn't have any luck with the ##tempTable. It kind of worked but not for what I needed. I did this simplified code to do what I needed though.

CREATE PROCEDURE testdynamtable AS
set nocount on
declare @tmpcolname varchar(20)
declare @sqlstr varchar(200)
set @tmpcolname= 'f1 int, f2 int'
set @sqlstr = 'create table #tmptable (' + @tmpcolname + ') insert into #tmptable (f2) values(888) select * from #tmptable'
exec(@sqlstr)
GO



Mike

 

[SQLBill]

Have you considered this:

Create 'real' tables instead of temp tables. Then when your script is done or you no longer need them, truncate the tables.

1. create a Database
2. create the table(s)
3. populate the table(s)
4. use the table(s)
5. when done, run TRUNCATE TABLE tablename for each table.

That will leave the database/table structures intact, but there won't be any data in them until you populate them again. Same thing as using temp tables, but without the access issues.

-SQLBill

 

[TheBugSlayer]

SQLBill is right. That's what I do: create the table once, leave the schema intact and TRUNCATE when no longer needed. Less overhead.

 

[skuhlman]

Remember that the "TRUNCATE TABLE..." will remove any records from the table, but still leaves the table in the database. If it truely is a temporary perminant table, you probably want to do a "DROP TABEL..." to remove it when done.

We actually do use a couple of "trash" tables within our application. In addition to having the fields needed for the report, we have a report ID and a report date field on the records. Within the application, whenever we use this table, we remove any detail lines that are more than 2 days old and add new lines with a unique ID and the current date for our current report run. By doing this, we don't have to keep adding and dropping the same table, and we also don't have to worry about millions of outdated records.

 

[donutman]

I must be missing the point, because I don't see why Mike needs either a temporary table or a "work" table. He should be able to get the reports he needs without resorting to the use of other tables.
This doesn't appear to be a similar situation as Skuhlman's where there are many similar reports to generate. Mike can you explain the "big picture" of what you are trying to accomplish.
-Karl

[red] Cursors, Triggers and User Definded Functions are part of the Axis of Evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]