Using Variables To Create Views

May 8, 2007

I am stuck on creating views using the variables. I keep on getting the 'CREATE VIEW' must be the first statement in a query batch error. I understand that views need to be the first statement, but I have a lot of views that need to reference specific variables - is there any way to do this?

The code I am using is as follows:

DECLARE @view varchar(MAX)
DECLARE @database varchar(30)
SET @database = 'KateTEST3'

--Insert views
SET @view = 'USE ['+@database+']
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON;

CREATE view [dbo].[userssupplier]
as
SELECT *
FROM dbo.Users
WHERE (User_Type = 4.0) OR
(User_Type = 5.2)'
EXEC(@view)





Thanks

View 11 Replies


ADVERTISEMENT

Scalar Variables And Views

Apr 24, 2007

I have a stored procedure that I'm trying too, and must figure out how to convert it to a view. The stored procedure uses two scalar variable for a date range in the WHERE CLAUSE (@StartDate and @EndDate). What can we do in a view if we cannot hard code date ranges? If I use the date field (Paydate) in the view then I cannot get a sum of the Payamt.
I appreciate your help.

View 15 Replies View Related

Using EM To Create Views

Jun 6, 2000

When I create a view using Enterprise Manager, I can click on the 'Add Table' button to select a table. Is there any way I can tell EM that I want to include a table from another server?

Thanks,

Randy

View 1 Replies View Related

How To Create Parameterised Views?

Jun 15, 2007

I am using SqlServer2005 and asp.net 2005. I have a large database for which I have to provide reports for it. I need a report that user will specify a date, and the report will be run, from this month & year and I will calculate the first day of the month and the last day of the month and these will serve as the input parameters for a long SELECT query.For my previous reports i've used views . But for this functionality i need  a parameterized view, which i cant create.Please help me how to create a parameterized view.... Thanks in Advance... 

View 3 Replies View Related

Create Views Not Tables

Jun 9, 2004

I want to allow a group of users to create views but not be able to create new tables or stored procedures... how can I do this ??

Thanks, John :eek:

View 1 Replies View Related

Create Views With Union

Sep 17, 2007

Hey

I am very new to database and have a question about views, that I hope someone can help me with, i am sure its simple:

I have to tables for storing different users, I want(for a log in function),to make a view that combine these to tables.

so all names stored in table1 under column customer_name, and all names stored in table2 under column name contact_name will in the view be stored under column username.

What shall a do?

Thanks for all help

View 10 Replies View Related

Create My Own @@Variables

Apr 16, 2008



Does anyone know if there is a way to create a server-wide variable... something like:

declare @@MyVar BIGINT
set @@MyVar = 22456

then refer to this var in any scope throughout the server?


View 7 Replies View Related

Create Views In The Order Of Their Dependency

Feb 5, 2007

In SQL 2K...i need to write a script that writes a script that creates all views in the order that they are nested and I am feeling lazy. Does anyone have anything handy?For example if View_BOB says...CREATE VIEW VIEW_BOBASSELECT COL_1 FROM VIEW_JOHNI need the script to generate a script that creates View_JOHN before View_BOB.

View 1 Replies View Related

How To Create Identity Column In Views

Mar 21, 2006

Hi there,

I'm new to SQL Server please help me, i want to create identity column(AutoNumber) when creating views.

View 6 Replies View Related

Can Not Create Views, Tables Etc * URGENT*

Jul 20, 2005

Hi,I am not getting option as 'new view' and 'new table' when I rightclick onviews and tables option in VisualStudio.net IDE server explorersqlservers database to create new objects.Looks like some setup issue in my database.Thanks for your help in advance.RgdsCV

View 1 Replies View Related

Should I Create Indexes On Views Or Tables

May 22, 2007

I have a pretty large database that has tables that will contain millions of rows of records. I will predominantly be using Views just to select the data. (I will not be performing any updates or inserts). I propose creating indexes on the views. My question is - if I create indexes on my views, do I have to create them on the tables as well? Is it good practice to create indexes on tables by default even if I am not going to be performing select statements directly on my tables but via my indexed views? Any advice is appreciated.

Thanks
Ran

View 4 Replies View Related

Create Views In Stored Procedures?

Sep 25, 2007

Can you create views within a stored procedure?

View 10 Replies View Related

Unable To Create/edit Views, Tables.. From VS .NET

Mar 21, 2005

Hi,
I'm working with few examples from a 70-315 exam prep book.
I'm trying to view the Table design... edit/ create views from VS. NET environment.
But when i right click on the View node under Data Connection tree i get only "Refresh" & "Properties". According to this book.. i should be able to create new views from here.
I'm working with a SQL Server 2000 local installation. VS .NET is connected to the DB, as i tried other examples and i'm able to read from the DB.
My SQL server installation is a 120 days eval installation... has this got anything to do with the behavior ??
I also reied to establish a connection with SQL server 'sa' login... i could connect, but again i wasnt able to create 'views' access tables design.
Any help would be appreciated.

Thanks.

View 1 Replies View Related

SQL 2012 :: Should Use Views Or Create A Data Mart

Oct 9, 2015

our group is debating whether we should use views or data marts. WE have some huge queries that can have many joins up to 20 in some cases. One part of our group wants to use views to pull this data and another thinks we should create a data mart off an SSIS package and run it early every morning and then have the users access the data directly from there.

I am not really sure how a view would speed things up. If I have 20 users and they all call a view the view is created 20 times is that not correct? Since a view is basically just a stored sql statement (not a stored proc) I am not seeing how this is any more efficient.

View 8 Replies View Related

CREATE VIEW, Seperate One Column In Two Views..

Oct 17, 2007

I am creating a view for the table:
bellus=# select * from host_application_definition;
id | type_value | connection_value | group_value | application_value | host
----+------------+------------------+-------------+-------------------+----


From the table meta_host_types;

id | value | types | name
----+-------+-----------+--------
1 | agm | host-type | Rencid

I would like to seperate value into type_value and connection_value, because it holds both values.

Any tip?

bellus=# d host_application_definition;
Table "public.host_application_definition"
Column | Type | Modifiers
-------------------+---------+--------------------------------------------------------------------------
id | integer | not null default nextval('host_application_definition_id_seq'::regclass)
type_value | integer |
connection_value | integer |
group_value | integer |
application_value | integer |
host | integer |
Indexes:
"host_application_definition_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"host_application_definition_application_value_fkey" FOREIGN KEY (application_value) REFERENCES appsmarteye(id)
"host_application_definition_connection_value_fkey" FOREIGN KEY (connection_value) REFERENCES meta_host_types(id)
"host_application_definition_group_value_fkey" FOREIGN KEY (group_value) REFERENCES meta_host_grouptypes(id)
"host_application_definition_host_fkey" FOREIGN KEY (host) REFERENCES master_hosts(id)
"host_application_definition_type_value_fkey" FOREIGN KEY (type_value) REFERENCES meta_host_types(id)



d meta_host_types;
Table "public.meta_host_types"
Column | Type | Modifiers
-------------+---------+---------------------------------------------------------------------
id | integer | not null default nextval('meta_types_application_id_seq'::regclass)
application | integer |
value | text |
comments | text |
types | text |
Indexes:
"meta_types_application_pkey" PRIMARY KEY, btree (id)
"meta_host_types_id_key" UNIQUE, btree (id)
"meta_host_types_value_key" UNIQUE, btree (value, application)
"meta_host_types_value_key1" UNIQUE, btree (value)
Foreign-key constraints:
"meta_types_application_application_fkey" FOREIGN KEY (application) REFERENCES appsmarteye(id)

View 3 Replies View Related

Is There A Way To Give Someone The Right To Create Only Views And No Other Objects In A Database

Feb 25, 2008

Is there a way to give someone the right to create only views and no other objects in a database? Currently I have given the individual ddladmin database rose, but would rather be more restrictive?

View 1 Replies View Related

Create A View Based On Variables?

Jul 28, 2004

Hi All,

I would like to create a view based on a variable (a date).

CREATE VIEW testView (@myDate)
AS

select * from testTable
WHERE testVar = @myDate

--
This is far from what the final view would look like, but if anyone knows if something similiar to this can be done, I would greatly appreciate it.

Thanks.

View 1 Replies View Related

Create Database Using Local Variables

Feb 4, 1999

I have an installation script and want to create a database based on some data in a table (config). Testing the script though I get a message:

Msg 170, Level 15, State 1
Line 12: Incorrect syntax near '@DBData'.

The relevant sql is:

declare @DBName varchar (40)
declare @DBData varchar (40)
declare @DBLog varchar (40)
declare @DBSize int
select @DBName = obj_txt from config where obj_nm='DBName'
select @DBData = @DBName + '_Data'
select @DBLog = @DBName + '_Log'
select @DBSize = obj_int from config where obj_nm='DBSize'
raiserror('Creating Database %s ....',0,1,@DBName) with nowait

Create Database @DBName
on @DBData = @DBSize
Log on @DBLog = @DBSize

I don't think there's anything wrong, apart from the fact I am using the local variables. Is this allowed on a Create Database statement? I haven't found anything in Technet that may help.

Here's hoping....
Thanks
Dan

View 1 Replies View Related

Concatenating Variables To Create A Column Name

May 5, 2008

I am trying to build a Windows application using: Windows XP Pro ; VS Pro 2005, C# and SQL2005.

I have 2 database table3 as follows:
eg
1) myGameRecency which contains columns : GameId (identity specification column/primary key/not null), Date (not null), [1], [2], [3], [4] , WeeksSinceDr0, WeeksSinceDr1, WeeksSinceDr2
2) myGameFrequency which contains columns : AllBallsFrequency , WeeksSinceDrawnAllBalls

Using the myGameRecencyAllBalls table ---
I wish to insert a 0 into a column corresponding to a ball that has been drawn, eg if a 4 has been drawn, then a 0 will be inserted into that column. If a ball has not be drawn, then the value in that column will be a running total, signifying the number of draws since it was last drawn ( ie since a 0 was inserted into that column).

I place a 1 in the column corresponding to the number of weeks since a number has been drawn. The name of the column is therefore the concatenation of the string literal 'WeeksSinceDrawn' and the value held by the variable, @lastRowCount obtained by the lastrow_CURSOR.

I have declared a variable @colName to hold the concatenation / Set @colName = 'WeeksSinceDr' + CONVERT(nvarchar(max), @lastRowCount) and then tried to use it as follows: SET [@colName] = 1

however, I receive an error message advising me that I have an invalid column name. Is there any means of setting a column name by concatenating two variables or , a string literal and a variable ?

Thank you
lpbcorp


sqlCmd.CommandText = "DECLARE @colName nvarchar(max) " +

"DECLARE @lastRowCount int " +

"DECLARE lastrow_cursor CURSOR SCROLL FOR " +

"(SELECT [" + i.ToString() + "] FROM " + DBGameName.ToString() + "RecencyAllBalls) " +

"OPEN lastrow_cursor " +

"FETCH LAST FROM lastrow_cursor INTO @lastRowCount " +

"SET @colName = 'WeeksSinceDr' + CONVERT(nvarchar(max), @lastRowCount) " +

"IF @lastRowCount <= 175 " +

"BEGIN UPDATE " + DBGameName.ToString() + "RecencyAllBalls SET [@colName] = 1 WHERE Date = '" + Date + "' " +

"END " +

"ELSE " +

"UPDATE " + DBGameName.ToString() + "RecencyAllBalls SET WeeksSinceDrOver175 = 1 WHERE Date = '" + Date + "' " +

"CLOSE lastrow_cursor " +

"DEALLOCATE lastrow_cursor";

sqlCmd.ExecuteScalar();

View 3 Replies View Related

Create Item Availability Views - Date Calculations

Mar 18, 2008


I am having a hard time creating a view with some complex math to chow availability for rental items.
My tables are as follows:

OrderHeader

OrderID int,
Site int,
StartDate datetime,
EndDate datetime

OrderDetail

OrderDetailID int,
Site int,
OrderID int,
ItemName varchar(30),
Qty int

ShipHeader

ShippingID int,
Type int,
ActionDate datetime

ShipDetail

ShipDetailID int ,
ShippingID int,
OrderDetailID int,
Qty int

Transaction

TransactionID int,
Type int

PurchaseHeader

PurchaseHeaderID int,
Site int
PickupDate datetime,
ReturnDate datetime,

PurchaseDetails

PurchaseDetailsID int,
PurchaseHeaderID int,
ItemName varchar(30),
Qty int


I need a view that shows how many items will be available on a particular day for a specified item, date range and a specified €œSite€? (Office location. For example, NY, LA, CA, etc.)

The quantity actually owned is calculated from the Transaction table. Type, Qty are the columns. (type 1 is a purchase or addition and type 2 is a sale or subtraction. The quantity owned is the sum of all type 1s minus the sum of all type 2s.

The ShipHeader table also shows types. Type 1 is ship, Type 2 is return and Type 3 is lost.

Initially, all availability is calculated based on the FromDate and ToDate of our order headers. However, once the order has shipped, the availability should change to our ship and return dates in our ShipDetails table. Additionally, there is a PurchaseDetails table that shows items to be €œSub-Rented€? for a time frame. The PurchaseHeader table contains the FromDate and ToDate for these sub-rented items to be added to availability. If an item is kept beyond the due date set in the OrderHeader, then the item should show as unavailable from the FromDate through all future dates (as there is no way of telling when a late item will be returning. Once, the item is either returned or marked as lost, it should become available again as of that date.

The view should list dates in each row with the number of units available on that date.

Also, a second view needs to be created that shows the details for the dates listed in the first view. this is essentially a list of the orders or puchase orders that were used to calculate the number available.

I think this covers it.

As I said, this is a bit complicated. i understand what needs to be done but need assistance assembling the code to make it happen.

View 21 Replies View Related

Create Variables With Same Format In One Single Step?

Jul 23, 2005

I would need to create multiple variables with the same format forfuture update, what I did is listing them separately, is there an easyway to combine them in one step? please see query below. Thanks a lot!--Current Query--SELECT cast(0.0 as money) as balance_1,cast(0.0 as money) as balance_2,cast(0.0 as money) as balance_3,cast(0.0 as money) as balance_4,cast(0.0 as money) as balance_5,cast(0.0 as money) as balance_6,cast(0.0 as money) as balance_7,cast(0.0 as money) as balance_8,account_no,XXXINTO table1FROM accountCan I do something like this? This one didn't work.SELECT balance_1 to balance_8 (cast 0.0 as money),account_no,XXXINTO table1FROM account

View 5 Replies View Related

Is It Possible To Dynamically Create Multiple Variables In A Package?

May 21, 2008

I'd like to be able to call different packages from a control flow. These packages will have different requirements for parameters therefore I'd like to create them dynamically.

Is this possible? Can I do it using a script task?

Thanks in advance.
Ben

View 10 Replies View Related

SQL Server 2008 :: Create Indexed Views On User Defined Functions?

Jul 14, 2015

I am creating mateialized view but it is failing with error that it can't be schema bound.

The query I am working to create materialized view are having joins with different tables and function.

Is it possible to create Indexed views on user defined functions?

View 2 Replies View Related

Dose It Make Sense To Create Indexted Views On A Single Table?

Apr 24, 2007

Hi, all experts here,

Thank you very much for your kind attention.

I am wondering if there is any sense to create indexed views on single table? I simple want to improve the report query performance as most of the reports data are from a single table. As views most of the time are created as for joined across tables.

Thank you very much for your advices and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

View 5 Replies View Related

DB Design :: Create Views From 2 Tables One Of Which Is Lookup Table That Will Give Column Names

Aug 5, 2015

I am trying a create views that would join 2 tables:

Table 1: Has all the columns need by a view (
Name: Product
Structure: ID, Attribute 1, Attribute 2, Attribute 3, Attribute 4, Attribute 5 etc
Table 2: Is a lookup table that provides the names of columns
Name: lookupTable
Structure: tableName, ColumnName, columnValue
Values: Product, Attribute1, Color
Product, Attribute2, Size
Product, Attribute3, Flavor
Product, Attribute4, Shape

I want to create a view that looks like

ID, Color, Size, Flavor, Shape

View 4 Replies View Related

Are Embedded Views (Views Within Views...) Evil And If So Why?

Apr 3, 2006

Fellow database developers,I would like to draw on your experience with views. I have a databasethat includes many views. Sometimes, views contains other views, andthose views in turn may contain views. In fact, I have some views inmy database that are a product of nested views of up to 6 levels deep!The reason we did this was.1. Object-oriented in nature. Makes it easy to work with them.2. Changing an underlying view (adding new fields, removing etc),automatically the higher up views inherit this new information. Thismake maintenance very easy.3. These nested views are only ever used for the reporting side of ourapplication, not for the day-to-day database use by the application.We use Crystal Reports and Crystal is smart enough (can't believe Ijust said that about Crystal) to only pull back the fields that arebeing accessed by the report. In other words, Crystal will issue aSelect field1, field2, field3 from ReportingView Where .... eventhough "ReportingView" contains a long list of fields.Problems I can see.1. Parent views generally use "Select * From childview". This meansthat we have to execute a "sp_refreshview" command against all viewswhenever child views are altered.2. Parent views return a lot of information that isn't necessarilyused.3. Makes it harder to track down exactly where the information iscoming from. You have to drill right through to the child view to seethe raw table joins etc.Does anyone have any comments on this database design? I would love tohear your opinions and tales from the trenches.Best regards,Rod.

View 15 Replies View Related

T-SQL (SS2K8) :: Procedure That Create Views With Table Name And A Table Field Parameter?

Aug 4, 2015

I would like to create a procedure which create views by taking parameters the table name and a field value (@Dist).

However I still receive the must declare the scalar variable "@Dist" error message although I use .sp_executesql for executing the particularized query.

Below code.

ALTER Procedure [dbo].[sp_ViewCreate]
/* Input Parameters */
@TableName Varchar(20),
@Dist Varchar(20)
AS
Declare @SQLQuery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(2000)

[code]....

View 9 Replies View Related

Execute DTS 2000 Package Task Editor (Inner Variables Vs Outer Variables)

Sep 4, 2006

Hi,

I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on " Execute DTS 2000 Package Task Editor - Inner Variables ". I believe the SSIS variables must be mapped on " Execute DTS 2000 Package Task Editor - OuterVariables ". How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"? How can I do it? Much Thanks.

João





View 8 Replies View Related

Large Views Vs Multiple Small Views

Sep 6, 2007

Which is more efficient? One large view that joins >=10 tables, or a few smaller views that join only the tables needed for individual pages?

View 1 Replies View Related

How To Design A Package With Variables So That I Can Run It By Dos Command Assigning Values To Variables?

Jan 24, 2006

Hi,

I would like to design a SSIS package, which have couple of variables. It loads a xls file specified in a variable [varExcelFileFullPath] .

I will run it by commands: exec xp_cmdshell 'dtexec /SQL ....' (pls see an example below).

It seems it does not get the values passed in for those variables. I deployed the package to a sql server.

are there any grammar errors here? I copied it from dtexecui. It worked inside Dtexecui not in dos command.

exec xp_cmdshell 'dtexec /SQL "LoadExcelDB" /SERVER test /USER *** /PASSWORD ****

/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"

/Set Package.Variables[User::varExcelFileName].Properties[Value];"TestAdHocLayer"

/Set Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"

/Set Package.Variables[User::varExcelFileFullPath].Value;"D: estshareTestAdHocLayer.xls"

/Set Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"

/Set Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp"

'



thanks,



Guangming

View 2 Replies View Related

Recompiling Views That Reference Other Views

Jun 28, 2007

Hello.

Newbie here. I've only been using SQL for about a year now and have some minor questions about sql objects that reference other objects.



We have some views which reference other views in the joins. I will call one the primary view and the one being referenced in the joins as the secondary view.

Recently we made changes to the secondary view.

After which the primary views which referenced it would not work because of this change and had to be 'refreshed' by using drop/create scripts which essentially just dropped it and recreated the exact same view. I do not recall the exact error message that was returned other than it seemed to suggest that it could no longer see the secondary view since it had been changed. Nothing in the primary view was changed in any way, just the secondary.



Some here where I work have suggested off hand that this was a recompile of the primary view because the contents of the secondary changed.

My questions are:

1. Exactly why did this happen and is there a proper name for it when it does?

2. The same problem does not seem to occur when we have stored procedures referencing views in the joins which had just been changed. Why is that?



Thanks for any help on the matter. I greatly appreciate it.

View 3 Replies View Related

Quicky : Views Of Views Of Views

Feb 22, 2007

Hello,

to make a report easier I'm developing it using a view of joined views of joined views.

Is there any significant performance penalty as opposed to just having one big select?

Cheers.

View 1 Replies View Related

Views Dependent On Other Views

Mar 14, 2006

Hello There,I'm trying to create a view that has calculations dependent oncalculations, where the problem resides is that each time I make acalculation I must create an intermediate view so I can reference aprevious calculation.for example lets say I have my_table that has columns a & b. now I wanta view that has a & b, c = a + b, and d = c + 1.this is grossly simplified, the calculations I actually use are fairlycomplex and copying / pasting them is out of the question.so what I have is my_view_a which makes column c, and my my_view_finalwhich makes column d (however, in my real application I have 5 of theseviews, a/b/c/d/e/)is there anyway I can consolidate all these views into one? I wasthinking of using a stored procedure with temp tables or somethingalong those lines.I just which I can use the aliases that I create for c in d in onestep.any insight would be greatly appreciated.

View 5 Replies View Related







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