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

Dynamic Table Name In Parameterized Ole Db Source Query?

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

Dynamic Parameterized MDX Dataset In Report Table

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

Stored Procedure To Update A Table Using Parameterized CASE Statement - Erroring Out

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

Parameterized Queries Running Slower Than Non-parameterized Queries

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

Oracle Parameterized Queries To Update Oracle Table Do Not Work

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

Creating Trigger On Creating Table

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

Difference In Creating Temporary Table By #table And ##table

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

Creating A Common Table Expression--temporary Table--using TSQL???

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

Creating A Table Column That Only Takes Data From Another Table.

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

Creating Table Fields Dynamically From Another Table Row

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

Creating A Table And Dropping That Table At End Of Execution

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

Creating A Stored Procedure That Will Summarize Data In A Table Into A Table Reflecting Period Data Using An Array Type Field

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

Creating A Dimension Table From A 3-key Table

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

Creating New Table From Existing Table

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

Creating Table

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

Creating A Table

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

Creating A Table

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

Creating Table In RAM

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

Creating Table

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

Creating Table

Dec 22, 2006

vijay writes "how can i creat a new table as i have in my database"

View 2 Replies View Related

Creating A Table

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

Creating A Table From .NET

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

Parameterized AND IN (@Var

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

Creating Table Problem

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

Creating A Third Table From Two Tables

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

Creating A Table Like This(probably 2 PrimaryKeys?)

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

Creating A Table Programatically!

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

Problems Creating A Table

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

Creating Table With Different SCHEMA...

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

Creating Table For A Project

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

Creating Table By Variable Name

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

Creating A Blank Table From Another

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







Copyrights 2005-15 www.BigResource.com, All rights reserved