Create Table from a variable
-
I have the following "start" to a new proc I'm going to be working on and am having an issue.
Alter Procedure [ProcessCold]
( @Dept varchar(4)
,@Office varchar(5)
,@State varchar(2)
)
As
DECLARE @SameCharity int,
@DiffCharity int,
@FileName varchar(20),
@SQL nvarchar(500)
set @SameCharity=180
set @DiffCharity=45
set @FileName=left(@Dept,2) + @State + convert(varchar(2),datepart(Month,Getdate()))+'-'+convert(varchar(2),datepart(Day,getdate()))+'-'+@Office
What I'd like to do is:
CREATE TABLE @FILENAME (PHONE VARCHAR(10)
But I'm receving an error when trying to do that because I'm assuming SQL doesn't allow you to directly create tables based off of variables? Is there a work-around that I can use instead?
Thanks.
-
DECLARE @FILENAME TABLE (PHONE VARCHAR(10))
Regards,
gova -
Govin,
I'm building the name of the file in the proc, therefor when I try the declare method you posted, I'm receving an error about a duplicated declaration.
-
That is because you already declared @FileName as VARCHAR(20). Change the name of any one of this you will be okay.
Regards,
gova -
If you want this CREATE TABLE @FILENAME (PHONE VARCHAR(10)) you will need to build ALL of it into a variable and then EXEC the variable or sp_runsql....
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Can you elaborate on this, I'm sorry but I don't understand I guess.
I'm wanting to send the proc the following:
exec processcold 'ABCD','2,'TX'
I'm wanting the proc to create a table named:
ABTX6-30-2
With the table name being programatically created with the set @filename statement.
What I'm unsure of though is how to integrate your declaration into the stored procedure and make things work. Would you mind maybe re-writing your idea out for me so I get a clearer picture of what you're meaning?
-
NM, I figured it out with AJ's answer.
Thanks much.
-
Why can't you use static sql for this task?
-
DECLARE @SQL VARCHAR(2000)
SELECT @SQL = 'CREATE TABLE ' + @FileName + '(Phone VARCHAR(10) )'
EXEC(@SQL)
Thanks AJ Ahrens I did not take the question.
Regards,
gova -
Another question along this same issue.
I'm trying to create the insert statement and am running into problems because I use the - as part of my @FileName variable. Anyone know the way around this other than simply changing the -?
statement I'm using is:
set @SQL = 'Insert into ['+@FileName+'] select top 100 Phone,Title,Fname,Lname,Address,City,State,Zip,'+@Dept+' as Dept,'+'75'+','+@FileName+' as Batch from cold2'
Note: the first @Filename is fine because it's the actual table, but I use the second @Filename to identify which "batch" of leads I'm sending out, and they just have always happened to be the same name.
-
Try this.
Your missing the ' around the Character fiedlds your trying to insert
When your having issues with dynamic sql,comment out the execute, and put a select statement prior to execution so you can see what the string looks like.
set @SQL = 'Insert into ['+@FileName+'] select top 100 Phone,Title,Fname,Lname,Address,City,State,Zip,'''+@Dept+''' as Dept,75,'''+@FileName+''' as Batch from cold2'
-- exec sp_ExecuteSql @sql
Copy and paste the results from the select to a query analyzer window and your issues may become clearer.
Please read, alot of information about dynamic sql, and why you may, or may not want to use.
-
The real question is why do you want to many different tables that keep the same information?
-
A good question indeed.
A new table for each day of the week right?
Thats about the only reason I can think of.
-
Talk about crappy design... Seems to be on the topic a lot today.
-
boy, slow down
* Noel
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply