CODEWITHSUNDEEP
sql-server
user1066713
user1066713

Reputation: 15

error in EXECUTE sp_executesql

when i execute this query, i get error of 'Incorrect syntax near the keyword 'VIEW'' please help me, what is the problem in this query?

EXECUTE sp_executesql 
N'
CREATE VIEW LastDayOfMonth
AS
SELECT @MAX = MAX(DATEPART(DAY,CurrencyRateDate))AS CurrencyRateDate FROM Sales.CurrencyRate 
WHERE 
DATEPART(YEAR,CurrencyRateDate)=@YEAR
AND
DATEPART(MONTH,CurrencyRateDate)= @MONTH',
N' @YEAR CHAR(4),@MONTH CHAR(2)',
@YEAR = '2004',
@MONTH = '02';

Upvotes: 0

Views: 1157

Answers (4)

Dragos D
Dragos D

Reputation: 1

I had a similar issue. It seems that you cannot use parameters with view. Instead process the SQL to include the parameters: 

set @sql = N'create view as select * from table where year = ' + cast(@year as varchar(4));
EXECUTE sp_executesql @sql;

Upvotes: 0

Nonym
Nonym

Reputation: 6299

Could you try this?:

EXECUTE DATABASENAME..sp_executesql 
N'
CREATE VIEW LastDayOfMonth
AS
SELECT @MAX = MAX(DATEPART(DAY,CurrencyRateDate))AS CurrencyRateDate FROM Sales.CurrencyRate 
WHERE 
DATEPART(YEAR,CurrencyRateDate)=@YEAR
AND
DATEPART(MONTH,CurrencyRateDate)= @MONTH',
N' @YEAR CHAR(4),@MONTH CHAR(2)',
@YEAR = '2004',
@MONTH = '02';

If you still get problems, then it's probably going to be because you can't work parameters with views ..

Upvotes: 0

vstrien
vstrien

Reputation: 2605

I think the problem is in the use of the variable @MAX, which isn't declared. When you want to return this value, you won't need a variable, just do it like this:

EXECUTE sp_executesql 
N'
CREATE VIEW LastDayOfMonth
AS
SELECT 
   MAX(DATEPART(DAY,CurrencyRateDate))AS CurrencyRateDate AS Maximum
FROM Sales.CurrencyRate 
WHERE 
DATEPART(YEAR,CurrencyRateDate)=@YEAR
AND
DATEPART(MONTH,CurrencyRateDate)= @MONTH',
N' @YEAR CHAR(4),@MONTH CHAR(2)',
@YEAR = '2004',
@MONTH = '02';

Upvotes: -1

Ed Harper
Ed Harper

Reputation: 21505

There's no obvious reason to do what you're doing here either using a view or using sp_executesql.

The following should work:

DECLARE @MAX INT
DECLARE @YEAR INT
DECLARE @MONTH INT

SET @YEAR = 2004
SET @MONTH = 02

SELECT @MAX = MAX(DATEPART(DAY,CurrencyRateDate))AS CurrencyRateDate FROM Sales.CurrencyRate 
WHERE 
DATEPART(YEAR,CurrencyRateDate)=@YEAR
AND
DATEPART(MONTH,CurrencyRateDate)= @MONTH

and to show the result:

SELECT @MAX

If this isn't what you're trying to do, perhaps you could edit the question and clarify?

Upvotes: 1

Related Questions