Dynamic Update with a sub select

Hi I need some help writing a dynamic Update with a sub select.

I am trying to execute this query and retrieve a variable.The update and select work separately but when I put them together I get the following error,

incorrect syntax near’= ‘

DECLARE @.SQL NVARCHAR(4000)

DECLARE @.ParameterList NVARCHAR(4000)

Declare @.WorkingSheduleID Bigint

SET @.ParameterList = ' @.XCustomerID bigint, @.XWorkingSheduleID bigint OUTPUT, @.XDeliveryDate smallDatetime'

SET @.SQL = 'UPDATEdbo.['+ @.TableName +'] SET UID ='

Set @.SQL = @.SQL + '@.XCustomerID'

Set@.SQL = @.SQL +' ,SlotClosed=1 where WorkingSheduleID ='

Set@.SQL = @.SQL +' (Select @.XWorkingSheduleID = (Max (WorkingSheduleID)'

Set@.SQL = @.SQL +' From dbo.['+ @.TableName +'] Where DeliveryDate =(CAST('

Set@.SQL = @.SQL +'@.XDeliveryDate'

Set@.SQL = @.SQL +' AS datetime)) And (SlotClosed=0)) )'

EXEC sp_executesql @.SQL, @.ParameterList,@.CustomerID ,@.WorkingSheduleID OUTPUT,@.DeliveryDate

Any Help appreciated

Mr Tumnus wrote:

Hi I need some help writing a dynamic Update with a sub select.

I am trying to execute this query and retrieve a variable. The update and select work separately but when I put them together I get the following error,

incorrect syntax near’= ‘

DECLARE @.SQL NVARCHAR(4000)

DECLARE @.ParameterList NVARCHAR(4000)

Declare @.WorkingSheduleID Bigint

SET @.ParameterList = ' @.XCustomerID bigint, @.XWorkingSheduleID bigint OUTPUT, @.XDeliveryDate smallDatetime'

SET @.SQL = 'UPDATE dbo.['+ @.TableName +'] SET UID ='

Set @.SQL = @.SQL + '@.XCustomerID'

Set @.SQL = @.SQL +' ,SlotClosed=1 where WorkingSheduleID ='

Set @.SQL = @.SQL +' (Select @.XWorkingSheduleID = (Max (WorkingSheduleID)'

Set @.SQL = @.SQL +' From dbo.['+ @.TableName +'] Where DeliveryDate =(CAST('

Set @.SQL = @.SQL +'@.XDeliveryDate'

Set @.SQL = @.SQL +' AS datetime)) And (SlotClosed=0)) )'

EXEC sp_executesql @.SQL, @.ParameterList,@.CustomerID ,@.WorkingSheduleID OUTPUT, @.DeliveryDate

Any Help appreciated

I think that the indicated (red) bracket is wrong as this is bracketing the SELECT away from (at a different level to) the other parts of the query (FROM, WHERE). I am less certain about which corresponding bracket to remove but I think it is the indicated one (blue).

|||

No change, I still get the same error

I have tried to use @.@.Identity to retrieve the variable, but I keep getting the identity of a query I run earlier in the SP (don’t sure I am using @.@.Identity properly).

I am fairly new to SQL and would appreciate any advice.

|||

Have you tried capturing the value of @.SQL and running that interactively with correct surrouding code (the DECLAREs, SETs, and a SELECT to inspect the final value). If you can find a version that works like that then you should only need to build it.

Another option is to split the operation into a batch of 2 steps like:

Code Snippet

SET @.SQL = 'SELECT @.XWorkingSheduleID = Max (WorkingSheduleID)'
SET @.SQL = @.SQL +' From dbo.['+ @.TableName +']'
SET @.SQL = @.SQL + ' Where (DeliveryDate = CAST(@.XDeliveryDate'
SET @.SQL = @.SQL +' AS datetime)) And (SlotClosed=0); '
SET @.SQL = @.SQL + 'UPDATE dbo.['+ @.TableName +'] SET UID = '
SET @.SQL = @.SQL + '@.XCustomerID, SlotClosed = 1'
SET @.SQL = @.SQL +' WHERE (WorkingSheduleID = @.XWorkingSheduleID)'

For your @.SQL setting code.

|||Hi,

try this:

Code Snippet

DECLARE @.SQL NVARCHAR(4000)

DECLARE @.TABLENAME VARCHAR(100)

DECLARE @.ParameterList NVARCHAR(4000)

Declare @.WorkingSheduleID Bigint

SET @.ParameterList = ' @.XCustomerID bigint, @.XWorkingSheduleID bigint OUTPUT, @.XDeliveryDate smallDatetime'

SET @.TableName = 'SomeTable'

SET @.SQL = 'UPDATE dbo.['+ @.TableName +'] SET UID ='

Set @.SQL = @.SQL + '@.XCustomerID'

Set @.SQL = @.SQL +' ,SlotClosed=1 where WorkingSheduleID = '

Set @.SQL = @.SQL +' (Select Max (WorkingSheduleID)'

Set @.SQL = @.SQL +' From dbo.['+ @.TableName +'] Where DeliveryDate =(CAST('

Set @.SQL = @.SQL +'@.XDeliveryDate'

Set @.SQL = @.SQL +' AS datetime)) And (SlotClosed=0)) )'

PRINT @.SQL

UPDATE dbo.[SomeTable]

SET

UID =@.XCustomerID ,

SlotClosed=1

where WorkingSheduleID =

(

Select Max (WorkingSheduleID) From dbo.[SomeTable]

Where DeliveryDate =(CAST(@.XDeliveryDate AS datetime)) And (SlotClosed=0))

)

Don′t know why you did the thing with the @.XcustomerId in the brackets, but you wither leave that out or put it somewhere in there where-clause instead.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Try to change the query as follows :

DECLARE @.SQL NVARCHAR(4000)

DECLARE @.ParameterList NVARCHAR(4000)

Declare @.WorkingSheduleID Bigint

SET @.ParameterList = ' @.XCustomerID bigint, @.XWorkingSheduleID bigint OUTPUT, @.XDeliveryDate smallDatetime'

Set @.SQL = 'Select @.XWorkingSheduleID = Max (WorkingSheduleID)'

Set @.SQL = @.SQL +' From dbo.['+ @.TableName +'] Where DeliveryDate =(CAST('

Set @.SQL = @.SQL +'@.XDeliveryDate'

Set @.SQL = @.SQL +' AS datetime)) And (SlotClosed=0);'

set @.SQL = @.SQL + 'UPDATE dbo.['+ @.TableName +'] SET UID ='

Set @.SQL = @.SQL + '@.XCustomerID'

Set @.SQL = @.SQL +' ,SlotClosed=1 where WorkingSheduleID = @.XWorkingSheduleID '

EXEC sp_executesql @.SQL, @.ParameterList,@.CustomerID ,@.WorkingSheduleID OUTPUT, @.DeliveryDate

SELECT @.XWorkingSheduleID