CODEWITHSUNDEEP
stored-proceduressql-server-2008-r2dynamic-sql
Tim Vavra
Tim Vavra

Reputation: 537

dynamic sql within stored procedure

I am running into a problem with a view creation. I am trying to create a view with one of the fields being 7 weekdays before another date in the table. The field [Live_Date] is provided by the client and I need to create the date [ImageDeliveryDate].

I have removed all of the other fields since they are working. I have a problem somewhere in the syntax but I can't seem to find it.

Here is what I have written:

set @SQL = 'CREATE VIEW vw_GCS_Export
    As
    select  ''' + DATEADD(WEEKDAY,-7,''' + [Live_Date] + '+ CHAR(39) + ')' + CHAR(39) +' '' as [ImageDeliveryDate]

    from Sheet1$'

Can anyone shed some light on what I am messing up? I had this running at one point and now it is failing. I change the name of the field adding the underscore and that is the only change.

Upvotes: 0

Views: 248

Answers (1)

MichaelJCox
MichaelJCox

Reputation: 756

This works on my SQL Server 2008 R2 to create a view with the 7 day prior column:

declare @SQL nvarchar(255)
set @SQL = N'CREATE VIEW vw_GCS_Export As
select DATEADD(WEEKDAY,-7,[Live_Date])as [ImageDeliveryDate]
from Sheet1$'

But it's not clear to me why it needs to be dynamic and not just created with a regular drop/create, or what all of the quotes were intended for (including the CHAR(39)s).

Upvotes: 1

Related Questions