dynamic sql within stored procedure

Question

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.

And for which database would that be? Things like "dynamic SQL" or stored procedures are highly vendor-specific - so we really need to know what concrete database you're working with.... — marc_s
– marc_s, Commented Apr 15, 2013 at 17:20

MichaelJCox · Accepted Answer · 2013-04-15 20:40:36Z

1

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).

answered Apr 15, 2013 at 20:40

MichaelJCox

7547 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

dynamic sql within stored procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related