Dynamic query quote problem.

[simian101]

Need some help with single and double quotes.

I am trying to build a dynamic query but I can't get the quotes right around the date.

declare @mydate datetime
set @mydate=getdate()

declare @comp
set @comp='1st'

--Version 1
exec('UPDATE [MyDatebase].[dbo].[' + @comp + ' table]' +
' SET [BillingDate] = ' + char(39) +
CONVERT(VARCHAR(10), @mydate, 101) + char(39))

--Version 2
exec('UPDATE [MyDatebase].[dbo].[' + @comp + ' table]' +
' SET [BillingDate] = CONVERT(VARCHAR(10), @mydate, 101)))


This is what the final query should look like

UPDATE [MyDatabase].[dbo].[1st table] SET [BillingDate] = '04/26/2009'


Thanks

Simi

 

[RiverGuy]

When you do something like this, debug it first. Instead of trying to execute it first, use the PRINT statement to see if the string is a valid SQL statement.

DECLARE @Sql VARCHAR(1000)

SELECT @Sql = 'UPDATE [MyDatebase].[dbo].[' + @comp + ' table]' +
' SET [BillingDate] = ' + char(39) +
CONVERT(VARCHAR(10), @mydate, 101) + char(39))

PRINT @Sql

Then, execute the variable instead of the string literal.

 

[simian101]

I tried that but I get....

Incorrect syntax near ')'.

Thanks

Simi

 

[markros]

DECLARE @Sql VARCHAR(1000)

SELECT @Sql = 'UPDATE [MyDatebase].[dbo].[' + @comp + ' table]' +
' SET [BillingDate] = ' + char(39) +
CONVERT(VARCHAR(10), @mydate, 101) + char(39)

PRINT @Sql

There was a typo - extra ) at the end.

 

[simian101]

Thanks Guys!!

Simi

 

[lburdo]

If simian101 is Chadwick Kaufman, I'll freak.