April 26, 2010 at 9:42 am
Hi,
I have created a stored procedure ABC and a view in a procedure .since i can't declare variables in a view i created a stored procedure and declared variables there :
Create procedure ABC
AS
Declare
@a int,
@b-2 int
select
@a = a
,@b = b
from
table D
where
D.gf = 0
--Creating the view
EXEC('
CREATE VIEW dbo.vGPH
WITH SCHEMABINDING
AS
SELECT
@a as a,
@b-2 as b,
'Default' as c
FROM
table dgh
')
Now i am getting error :
Incorrect syntax near the keyword 'DEFAULT'.
Must declare the scalar variable "@a".
Can anyone please help me.
April 26, 2010 at 10:39 am
itskumar2004 (4/26/2010)
Hi,I have created a stored procedure ABC and a view in a procedure .since i can't declare variables in a view i created a stored procedure and declared variables there :
Create procedure ABC
AS
Declare
@a int,
@b-2 int
select
@a = a
,@b = b
from
table D
where
D.gf = 0
--Creating the view
EXEC('
CREATE VIEW dbo.vGPH
WITH SCHEMABINDING
AS
SELECT
@a as a,
@b-2 as b,
'Default' as c
FROM
table dgh
')
Now i am getting error :
Incorrect syntax near the keyword 'DEFAULT'.
Must declare the scalar variable "@a".
Can anyone please help me.
I'm confused. Could you please explain what you are attempting to accomplish?
April 26, 2010 at 10:49 am
You can't pass parameters to a view, so you'll have to answer Lynn's question in order to get any help. I don't understand what you're trying to achieve either.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 26, 2010 at 11:10 am
Well i am trying to create a view in which there are two variables @a and @b which will be coming from date table ,now how can i create a view with these two columns because i can't join in the from clause .
and my other question why is it not accepting c column ,i want to create a column c with value 'Default' in it,why am i getting the error i mentioned above.
April 26, 2010 at 11:12 am
Is there a reason why you cant join the tables in the where clause?
April 26, 2010 at 11:15 am
Grant Fritchey (4/26/2010)
You can't pass parameters to a view, so you'll have to answer Lynn's question in order to get any help. I don't understand what you're trying to achieve either.
If you can query "date table" to get these two values you certainly can add "date table" to your view.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 26, 2010 at 11:26 am
Instead of posting fake code, how about showing us what you are trying to accomplish. You will get much better help if you follow the instructions in the first article I reference below in my signature block, plus include expected results based on the sample data the article instructs you to provide.
The best thing about following the advice of the article, you will get tested code in return for the extra effort you demonstarte.
April 26, 2010 at 11:32 am
itskumar2004 (4/26/2010)
Well i am trying to create a view in which there are two variables @a and @b which will be coming from date table ,now how can i create a view with these two columns because i can't join in the from clause .
and my other question why is it not accepting c column ,i want to create a column c with value 'Default' in it,why am i getting the error i mentioned above.
But, you can't use variables like that within a view, so, you'll need to explain why you can't join on the table. That type of thing is done all the time.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 26, 2010 at 11:48 am
I can't join the tables because there are no reference columns.
i am attaching the code below:
when i try to create the procedure i get the following error:
Incorrect syntax near 'DEFAULT'.
if i remove that column and run the code ,a stored procedure is created but when i try to execute it i got the following error:
must declare scalar variable @Month.
I have also added update statement for the view at the bottom, also got the error :
Cant find the object dbo.vgph.
Please let me know if i am wrong or didn't understand what i am trying to say..
April 26, 2010 at 11:59 am
Just one question, did you even read the first article I reference below in my signature block regarding asking for help?
April 26, 2010 at 12:30 pm
I am very confused by this code.
I'm going to take a shot at what you're trying to do, but I don't understand why you're doing it.
Are you attempting to dynamically create a view using values selected from the database?
Or, are you attempting to create default values for a view, like this:
SELECT 'Dude' AS x
FROM Table
Maybe if you changed your code to look a bit like this:
EXEC('
CREATE VIEW dbo.vGph
WITH SCHEMABINDING
AS
--I deleted all the other columns
SELECT
''DEFAULT'' as method,
''' + @Year + ''' as FYInt,
'''+ @Month + ''' as FMInt,
'''' as cst
FROM
dbo.Opp O
inner join dbo.tCustomer C
on O.CustomerID = C.CustomerID
INNER JOIN dbo.tPart P ON P.PartID = O.PartID
LEFT OUTER JOIN dbo.tUser U ON U.UserID = O.EditUserID
')
If you're trying to use the values of @Year and @Month as defaults, then you need to add them in seperately. Further, you have to use the two single quotes to designate where one should be within a string.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 26, 2010 at 1:11 pm
Grant Fritchey (4/26/2010)
Are you attempting to dynamically create a view using values selected from the database?
Or, are you attempting to create default values for a view, like this:
SELECT 'Dude' AS xFROM Table
If you're trying to use the values of @Year and @Month as defaults, then you need to add them in seperately. Further, you have to use the two single quotes to designate where one should be within a string.
I created a view using values selected from the database and default values ,but didn't mention the values from the database in the .txt file i sent you.and @year and @month are not defaults they are selected from date view D.
select
@Year = Year
,@Month = Month
from
View D
where
D.MonthByRef = 0 -- this will give current month details,unfortunately i couldn't join this Date view with the other tables from the join
because there are no reference columns.
I changed the query with your suggestions and it worked.
Thank you for the replies.
but i want to update the view dbo.vGph cst column .is it possible to update the view,i did read that we can update the view using update statement.
Can you give me suggestions on this..
April 26, 2010 at 1:15 pm
You can update views, but there's a whole slew of restrictions around it. These are straight from the Books Online:
Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.
The columns that are being modified in the view must reference the underlying data in the table columns directly. They cannot be derived in any other way, such as through:
An aggregate function (AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR and VARP).
A computation; the column cannot be computed from an expression using other columns. Columns formed using set operators (UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT) amount to a computation and are also not updatable.
The columns that are being modified cannot be affected by GROUP BY, HAVING, or DISTINCT clauses.
TOP cannot be used anywhere in the select_statement of the view when WITH CHECK OPTION is also specified.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 26, 2010 at 1:23 pm
Thanks for the replies and suggestions.
April 26, 2010 at 2:49 pm
I have updated the view ,but it is giving me error:
Msg 4406, Level 16, State 1, Line 1
Update or insert of view or function 'dbo.vGmopph' failed because it contains a derived or constant field.
** Table **
Create Table dbo.tOpp
(
UserID int,
flag int NULL
)
insert into dbo.tOpp (UserID, flag) Values(1, 1)
insert into dbo.tOpp (UserID, flag) Values(2, 1)
insert into dbo.tOpp (UserID, flag) Values(3, 0)
insert into dbo.tOpp (UserID, flag) Values(4, 1)
insert into dbo.tOpp (UserID, flag) Values(5, 1)
insert into dbo.tOpp (UserID, flag) Values(6, 0)
UPDATE
dbo.vGph
SET
dbo.vGph.cst= 'Y'
FROM
dbo.vGph O
join dbo.tOpp OP
on
O.UserID= OP.UserID AND
OP.Flag=1
If anyone know how to update, if the statement contains a derived or constant field please tell me how to do that.
I appreciate any help.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply