Creating A Table With A Parameterized Name
Apr 26, 2008
Hi,
I'm a Transact-SQL newbie, and I would like to create a procedure that creates a table with a parameterized name. I wrote the following, which I thought should do the job:
CREATE PROCEDURE procedure_AddFund
@FundName varchar(10)
AS
BEGIN
CREATE TABLE @FundName (Date smalldatetime, Price money)
END
GO
But I am getting the following error in Management Studio:
Msg 102, Level 15, State 1, Procedure procedure_AddFund, Line 5
Incorrect syntax near '@FundName'.
Presumably, I need to convert the @FundName to some appropriate string type. My question is what is the string type, and how do I do the conversion.
Thanks!
Adam Cataldo
View 1 Replies
ADVERTISEMENT
Sep 11, 2006
hi everyone,
joy mundy alluded in her webcast that it is possible to dynamically specify a table name in a parameterized ole db source query. is this true? if so, how can it be done?
View 6 Replies
View Related
Oct 10, 2007
Hi, I was wondering if there is a way to solve this issue.
I have the following MDX to retrieve specialised time related data from a cube
Code Block
SELECT NON EMPTY { { { [Measures].[% Vacancy], [Measures].[Total Square Area], [Measures].[Deliveries], [Measures].[Net Absorption] } * { [Time].[Quarter].[2007 Q1], [Time].[Quarter].[2007 Q1].lag(1), [Time].[Quarter].[2007 Q1].lag(2), [Time].[Quarter].[2007 Q1].lag(3), [Time].[Quarter].[2007 Q1].lag(4), ytd( [Time].[Quarter].[2007 Q1]), ytd( [Time].[Quarter].[2007 Q1].lag(4)),ytd([Time].[Quarter].[2007 Q1].parent.lag(1)),ytd([Time].[Quarter].[2007 Q1].parent.lag(2)) } } } ON COLUMNS ,
NON EMPTY { DESCENDANTS( [Location].[All Location], [Location].[Market] ) } ON ROWS
FROM [QMS]
WHERE ( [Overall].[Overall].&[Y] )
I can populate a table with the columns i need from the resulting dataset, however it is not dynamic. i.e.
The dataset field names come up as say,
Measures___Vacancy_Time_All_Time_2007_2007_Q1
Measures___Vacancy_Time_All_Time_2006_2006_Q4
Which i can place in the report...
But if I then want to parameterize [2007 Q1] like as follows
Code Block
="SELECT NON EMPTY { { { [Measures].[% Vacancy], [Measures].[Total Square Area], [Measures].[Deliveries], [Measures].[Net Absorption] } * { [Time].[Quarter].[" + Parameters!Quarter.Value + "], [Time].[Quarter].[" + Parameters!Quarter.Value + "].lag(1), [Time].[Quarter].[" + Parameters!Quarter.Value + "].lag(2), [Time].[Quarter].[" + Parameters!Quarter.Value + "].lag(3), [Time].[Quarter].[" + Parameters!Quarter.Value + "].lag(4), ytd( [Time].[Quarter].[" + Parameters!Quarter.Value + "]), ytd( [Time].[Quarter].[" + Parameters!Quarter.Value + "].lag(4)),ytd([Time].[Quarter].[" + Parameters!Quarter.Value + "].parent.lag(1)),ytd([Time].[Quarter].[" + Parameters!Quarter.Value + "].parent.lag(2)) } } } ON COLUMNS ,NON EMPTY { DESCENDANTS( [Location].[All Location], [Location].[Market] ) } ON ROWS FROM [QMS] WHERE ( [Overall].[Overall].&[Y] )"
so that everything is then driven from a single selected Quarter value, the table report no longer gets populated, as it has hardcoded field values such as
=Fields!Measures___Vacancy_Time_All_Time_2007_2007_Q1.Value
and if the Quarter selected is [2006 Q1] for example, this field will not exist in the dataset.
Is there a way to accomplish this? I am using SSRS 2005 against SSAS 2000 cubes
View 5 Replies
View Related
May 2, 2008
I am trying to create a stored procedure that will take a text value passed from an application and update a table using the corresponding integer value using a CASE statement. I get the error: Incorrect syntax near the keyword 'SET' when I execute the creation of the SP. What am I missing here? This looks to me like it should work. Here is my code.
CREATE PROCEDURE OfficeMove
-- Add the parameters for the stored procedure here
@UserName nvarchar(10),
@NewLocation nchar(5),
@NewCity nvarchar(250)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
Execute as user = '***'
DELETE FROM [SQLSZD].[SZDDB].dbo.Employee_Office_Assignments
WHERE User_Name = @UserName
INSERT INTO [SQLSZD].[SZDDB].dbo.Employee_Office_Assignments
SET User_Name = @UserName,
Room_ID = @NewLocation
UPDATE [SQLSZD].[SZDDB].dbo.Employee_Locations
SET Office_ID =
CASE
WHEN @NewCity = 'Columbus' THEN 1
WHEN @NewCity = 'Cleveland' THEN 2
WHEN @NewCity = 'Cincinnati' THEN 4
WHEN @NewCity = 'Raleigh' THEN 5
WHEN @NewCity = 'Carrollwood' THEN 6
WHEN @NewCity = 'Orlando' THEN 7
END
WHERE User_Name = @UserName
END
GO
View 4 Replies
View Related
Jul 20, 2005
HelloWhen I use a PreparedStatement (in jdbc) with the following query:SELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = ?ORDER BY group_nameIt takes a significantly longer time to run (the time it takes forexecuteQuery() to return ) than if I useSELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = 'M'ORDER BY group_nameAfter tracing the problem down, it appears that this is not preciselya java issue, but rather has to do with the underlying cost of runningparameterized queries.When I open up MS Enterprise Manager and type the same query in - italso takes far longer for the parameterized query to run when I usethe version of the query with bind (?) parameters.This only happens when the table in question is large - I am seeingthis behaviour for a table with > 1,000,000 records. It doesn't makesense to me why a parameterized query would run SLOWER than acompletely ad-hoc query when it is supposed to be more efficient.Furthermore, if one were to say that the reason for this behaviour isthat the query is first getting compliled and then the parameters aregetting sent over - thus resulting in a longer percieved executiontime - I would respond that if this were the case then A) it shouldn'tbe any different if it were run against a large or small table B) thisperformance hit should only be experienced the first time that thequery is run C) the performance hit should only be 2x the time for thenon-parameterized query takes to run - the difference in response timeis more like 4-10 times the time it takes for the non parameterizedversion to run!!!Is this a sql-server specific problem or something that would pertainto other databases as well? I there something about the coorect use ofbind parameters that I overall don't understand?If I can provide some hints in Java then this would be great..otherwise, do I need to turn/off certain settings on the databaseitself?If nothing else works, I will have to either find or write a wrapperaround the Statement object that acts like a prepared statement but inreality sends regular Statement objects to the JDBC driver. I wouldthen put some inteligence in the database layer for deciding whetherto use this special -hack- object or a regular prepared statementdepending on the expected overhead. (Obviously this logic would onlybe written in once place.. etc.. IoC.. ) HOWEVER, I would desperatelywant to avoid doing this.Please help :)
View 1 Replies
View Related
Apr 23, 2007
Oracle and MS drivers do not support parameterized queries, so update table set column=? where primarykey=? does not work for Oracle.
Anyone knows how to update an Oracle table through SSIS?
Thanks!
Wenbiao
View 5 Replies
View Related
Jan 28, 2008
Hi,
If I want to automatically insert a record which has default value in a table,
how can I create the trigger?
View 5 Replies
View Related
Nov 29, 2006
Banti writes "IF i create temporary table by using #table and ##table then what is the difference. i found no difference.
pls reply.
first:
create table ##temp
(
name varchar(25),
roll int
)
insert into ##temp values('banti',1)
select * from ##temp
second:
create table #temp
(
name varchar(25),
roll int
)
insert into #temp values('banti',1)
select * from #temp
both works fine , then what is the difference
waiting for ur reply
Banti"
View 1 Replies
View Related
Jul 23, 2005
Using SQL against a DB2 table the 'with' key word is used todynamically create a temporary table with an SQL statement that isretained for the duration of that SQL statement.What is the equivalent to the SQL 'with' using TSQL? If there is notone, what is the TSQL solution to creating a temporary table that isassociated with an SQL statement? Examples would be appreciated.Thank you!!
View 11 Replies
View Related
May 20, 2006
I am trying to create a table that holds info about a user; with the usual columns for firstName, lastName, etc.... no problem creating the table or it's columns, but how can I "restrict" the values of my State column in the 'users' table so that it only accepts values from the 'states' table?
View 2 Replies
View Related
Aug 31, 2006
Hello all:
Is it possible to creates fields of the table dynamically?. I have this situation in my project. This is just a small sample. I have row of length 140. I don't wan't to declare all this fields manually using the create table command.
The description of table is as, in this table all the field are of type varchar only, there are like 140 columns.
create dummy emp (
field1 VARCHAR(100), field2 varchar(200), field3 VARCHAR(100).... )
Table: Dummy
================================================== ==
field1 field2 field3..........
Empid Empname empaage1 sam 23...........
2 rai 22............
.
.
.
n raj 45.............
================================================== ==
Now I want to create another table as "EMP" , with proper data type
fields too..
create table emp (
empid int, empname varchar(100), empage int....)
The table should look like as:
Table: EMP
================================================== ==
Empid Empname empaage............
1 sam 23...............
2 rai 22................
.
.
.
n raj 45.................
================================================== ==
I want to do this dynamically.....
Some how I need to extract those field from table[dummy]; the first row acts as a column header for the table[Emp] and the subsequent row acts as a record for the table[Emp]
A small rough snippet of the code will be appreciated....
Waiting for replies........
saby
View 1 Replies
View Related
Mar 19, 2012
I am using a Stored procedure in which I am creating a table and dropping that table at the end of execution. This SP is calling every 10 second (but no concurrent access) from my application. Is there any issue using the drop table command in the SP? will it create any memory fragmentation issue in SQL server?
View 7 Replies
View Related
Sep 20, 2007
I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.
Any help would be greatly appreciated.
Current Table
Project | Task | Category | Fiscal Year | Fiscal Month | Total Hours
---------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 2007 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2007 | 02 | 20
Proj 1 | Task 1 | Cat 3 | 2007 | 03 | 35
Proj 1 | Task 1 | Cat 1 | 2008 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2008 | 02 | 40
Proj 1 | Task 1 | Cat 3 | 2008 | 03 | 40
Proposed Table
Project | Task | Category | Fiscal Month 01 | Fiscal Month 02 | Fiscal Month 03 | Fiscal Year
---------------------------------------------------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2007
Proj 1 | Task 1 | Cat 2 | 0 | 20 | 0 | 2007Proj 1 | Task 1 | Cat 3 | 0 | 0 | 35 | 2007
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2008
Proj 1 | Task 1 | Cat 2 | 0 | 40 | 0 | 2008
Proj 1 | Task 1 | Cat 3 | 0 | 0 | 40 | 2008
Thanks,
Mike Misera
View 6 Replies
View Related
Jan 13, 2005
Hi All,
I have a situation with a table that was created for a transactional
system with a 3 columns key. The table is similar to the following:
countrystatecitydescription
11221City A from country 1 and state 12
11321City A from country 1 and state 13
21422City B from country 2 and state 14
21522City B from country 2 and state 15
Now I'm trying to create a dts package that would allow me to build a
city dimension table with unique codes (keys) for each city. What kind of
transformation should I use to translate the old codes (based on the
country-state-city key) into the new ones and preserving the data
integrity?
Thanks,
Ignacio
View 2 Replies
View Related
Apr 20, 2006
Hi,
I m new to this forum with a Query
Create table <table name1 > as select * from <table name2>
this works for oracle. do anybody knows its alternative in sqlserver 2000
thanx. :)
View 4 Replies
View Related
Jan 15, 2008
I'm trying to create programmatically a table in my db on sql server 2005.1. I created a string "CREATE TABLE ....." and then tried to execute it using my ADO. That didn't work, so I copied the string into a query in sql server and got the message "CREATE TABLE sql is not supported". Why's that? 2. I created a stored procedure in my db that will get the "CREATE TABLE...." string and execute it. I thought it would look something like this: parameter @createString varchar(MAX) BEGIN BEGIN EXEC @createString or @createString END END Both these options didn't work.How can I make it work properly?Thanks.
View 3 Replies
View Related
Dec 22, 2003
I need a module to help me create a SQLServer database table. Is there a module that allows users to specify parameters in the creation of a SQLserver table?
View 1 Replies
View Related
May 1, 2000
Hello, folks.
I wonder whether I can create a table based on the script in SQL Analyzer.
If this is possible, can you folks tell me how to do it.
Thanks in advance.
Hyunhyo Jun
GIS Research Group
University of Colorado
View 1 Replies
View Related
Dec 18, 2001
Does anyone know if it is possible to create a tmp table in RAM without pinning it and how to do it? I already knew that SQL has a stored procedure allows pinning a table in RAM.
Thank you for any input.
View 1 Replies
View Related
Jul 27, 2006
Hi
I am trying to create a table in sql server 2000 and seem to be getting an error
the code that i am using is as folows:
CREATE TABLE Invoice
(
InvoiceID INT IDENTITY PRIMARY KEY NOT NULL Auto_Increment,
Invoice_no VARCHAR(50),
RefText VARCHAR(50),
CustomerDetails text,
)
I want to be able to have the invoiceid as the primary key that auto increments...
After creating the table id like to import a csv file with only the following data
Invoice_no, RefText, CustomerDetails
is it possible to do this as i want the id to automatically be generated to be unique
thanks
View 6 Replies
View Related
Dec 22, 2006
vijay writes "how can i creat a new table as i have in my database"
View 2 Replies
View Related
Jul 20, 2005
How do you create a new table from a SELECT statement of another tableusing MS SQL Server. This is part of a distributed database topic foruniversity. Unfortunately I can only seem to get the new table createdin Oracle and not MS.
View 4 Replies
View Related
Oct 23, 2006
I need to create tables from a C# code. I made a stored procedure hoping that I will pass a table name as a parameter: @tabName. The procedure executed in SqlServer All Right but when I called it from C# code it created not the table I put in as a parameter but table "tabName." Otherwise everything else was perfect: coumns, etc.
Here is my stored procedure.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CreateTableTick]
@tabName varchar(24) = 0
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE tabName
(
bid float NULL,
ask float NULL,
last float NULL,
volume float NULL,
dateTimed datetime NULL
)
END
What went wrong?
Thank you.
View 6 Replies
View Related
Nov 12, 2004
I am trying to create a list of values for an IN clause. How does one do it with parameterized queries?
If I use var = "val','val2','val3"
then the ' are replaced with '' - so how?
View 5 Replies
View Related
Jan 22, 2008
My program is to create temporary table. To avoid creating the same name table, I have to create it dynamically with this code. Random rand = new Random(); string num = "Temp"+rand.Next(100).ToString(); sql = "CREATE TABLE '"+num+"' ( , , , )"; When it is exeucuted, the error message tell it has error near Temp23(num). Is it possible to create table with this way?
View 1 Replies
View Related
Apr 7, 2008
Hi All,I will like to create a table from two tables this (explained below) way using a stored procedure. Basically i want the stored procedure to return me the third table but i dont know how to do it.Table ATopicID Topic ----------------------------- 1 Sometopic 12 Some topic 23 Some topic 34 Some topic 4 Table BRateID Rate TopicID---------------------------------------1 5 12 5 13 5 24 4 25 4 36 3 5 Table CTopicID Topic Rate--------------------------------------------1 Some topic1 52 Some topic 2 5 3 Some topic 3 5Basically i have a table that stores topics associated with a particular subject. Those topics are stored in table A above. My users can just read the messages associated with the topic and rate that message. The rating is then stored in table B.Now i will like my users to search for topics with a particular word or sentence in table A and the topics that are returned have a rating they specify. My procedure takes the word or sentence to search in table A and and the rating which will be used in table B. I will like to construct another table that has this newly searched results and return it using a stored procedure or anything simple. I dont know how to do this because i have very little knowledge in stored procedures or sql..... Any help(Code) will be greatly appreciated...
View 11 Replies
View Related
Jun 4, 2008
Hello there,
I'm going to, by my site, to create the aspnet membership thing.. (you know you can run aspnet_regsql.exe and then its create a lot of tables)But I'm stuck with a table with two primary keys? How the **** is that possible?I got this code to create table:'aspnet_UsersInRolesnonqueryCommand.CommandText = "CREATE TABLE aspnet_UsersInRoles (UserId uniqueidentifier NOT NULL PRIMARY KEY, RoleId uniqueidentifier NOT NULL PRIMARY KEY)"Console.WriteLine(nonqueryCommand.CommandText)Session("Tables") = Session("Tables") + "Number of Rows Affected with table aspnet_UsersInRoles is: " + nonqueryCommand.ExecuteNonQuery().ToString + "<br />"
But you ain't allowed to have two primary keys,How do you then create this table?:
Hope for help, and as you probably already has notice is I'm absolutely not expert to this :D
Ps. Tehre shouldn't already be a code for this in vb?
View 8 Replies
View Related
Nov 25, 2003
Hi SQ Gurus,
Please help me with this issue that I am facing. I want to create a table programatically but want to pass its name as a variable. I would like to do something as follows:
CREATE PROCEDURE BS_Create
(@Name nvarchar(25))
AS
CREATE TABLE [dbo].[@Name](
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Prod_Name] [nvarchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
The table name is passed in as a parameter. No matter what I pass as a parameter, the table name is '@Name', which is not what I want. Instead, if the variable contains the value 'Product', I would like to see a table with the name 'Product' to be created.
How can I solve this problem.
Any help is appreciated!
Krish Chandra
View 1 Replies
View Related
Dec 2, 1999
I have made a database device and a database with SQL Server.
After, I have created some tables and I have made inserts, deletes,
updates, select, etc.
I have 23 tables in my database. Now I'm trying to create a new one,
but I can't. I have this error:
"Unable to allocate new page for database 'ODDO'. There are no more
pages available or valid allocation pages.
Space can be created by droping objetcs, extending the database, or
dumping the log with no_log"
I'm not an expert in SQL Server. Can anybodt help me?
What I have to do?. What is a page?. How can I extend my database?
Thank you very much.
View 1 Replies
View Related
Sep 27, 2005
Hi all,
i m using sql server2005 CTP...i created a database called TEL and in that database i created a user(in security) as
USE [TEL]
GO
/****** Object: User [COLL_DB] Script Date: 09/27/2005 15:38:51 ******/
GO
CREATE USER [COLL_DB] FOR LOGIN [loginName] WITH DEFAULT_SCHEMA=[COLL_DB]
Now,when i m trying to create a table in the database TEL as
CREATE TABLE [COLL_DB].abc (c numeric)
commit;
it gives me error saying
The specified schema name "COLL_DB" either does not exist or you do not have permission to use it.
Now,can someone tell me...what i have to do to fix this error?????????
thanks...
View 1 Replies
View Related
Apr 29, 2012
I am having trouble creating my last table for a project.
I have a table named "Payment".
I have a composite key (ebay_ID & Cellphone_ID).
Would eBay_ID & cellphone_id also be foreign keys since they are the PK of their own table?
I also have an entity called "CC#" and "payment_amt".
For example there is a buyer who purchased 3 diff items, so the ebay_id would be the same and so would the cc#.
View 3 Replies
View Related
Feb 16, 2004
Hi
How can i Create a table with variable name
eg
suppose variable x=tab1
I want the table created to be tab1 if x=tab2 then table created shd be tab2.. so how can this be dynamically done(creation of tables as well as stored procs)
i tried : create table @x .. it doesnt seem to work
please help
regards
View 2 Replies
View Related
Feb 17, 2004
Hi again,
I want to create a blank table with the same column names as another but with no rows i.e. it is empty. Does anyone know how to do the last part?
I have so far
create table newtest as select * from shared.test ..... (empty bit here?)
Any good advanced sql tutorial urls would be good to.
Thanks in advance :)
View 12 Replies
View Related