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
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
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
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
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
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
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
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
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
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
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
Sep 25, 2007
Can you create views within a stored procedure?
View 10 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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