Hi Bitbucket,

I'm actually trying to create a dynamic UPDATE statement within the stored procedure as there are multiple (100's) of update statements I'll end up with which does differing calculations on the same data table. I started passing this to a stored procedure as some of the Fields in each select statement are common such as Select BranchCode and also Group By BranchCode are always used. I'm basically trying to cut down the length of the coding required by using the same common items where possible.

What's different in each line is the calculation I want and also the WHERE clause based on two dates. Some calculations are dependant on the WHERE clause requiring a range between two dates, others where no WHERE clause is applicable. I got the dynamic update statement working fine, but I can't use the two date variables directly in the EXECUTE statement as it appears is not allowed.

I think the example I used was a bit too simple, but as RBarry pointed out that you can't use expressions in EXECUTE statements, that seems to have explained what the problem was, rather than me posting the rest of the code first.

I've posted it below if you were curious to take a look.

--this basically gets repeated many times over with a different calculation and works, but has a fixed date in the WHERE clause

EXECUTE zzUpdateSummaryData 'zzTEMP_BranchSummary', 'BranchCode', 'SalesExGST', 'SELECT BranchCode, ISNULL(SUM(ExtendedNetAmount)-SUM(ExtendedTaxAmount),0) AS SalesExGST WHERE SaleDate>='20081201 00:00:00' and SaleDate<'20090101 00:00:00' FROM zzTEMP_SaleLine GROUP BY BranchCode'

--this this is what I tried doing, as some, but not all of the statements will use the same date range that defined earlier

--1st calculation, uses the dates

EXECUTE zzUpdateSummaryData 'zzTEMP_BranchSummary', 'BranchCode', 'SalesExGST', 'SELECT BranchCode, ISNULL(SUM(ExtendedNetAmount)-SUM(ExtendedTaxAmount),0) AS SalesExGST WHERE SaleDate>=''' + @StartDate + ''' AND SaleDate<''' + @EndDate + ''' FROM zzTEMP_SaleLine GROUP BY BranchCode'

--2nd calculation, uses the dates

EXECUTE zzUpdateSummaryData 'zzTEMP_BranchSummary', 'BranchCode', 'GST', 'SELECT BranchCode, ISNULL(SUM(ExtendedTaxAmount),0) AS GST WHERE SaleDate>=''' + @StartDate + ''' AND SaleDate<''' + @EndDate + ''' FROM zzTEMP_SaleLine GROUP BY BranchCode'

--3rd calculation, does not use the dates, but does use same table name and group by etc.

EXECUTE zzUpdateSummaryData 'zzTEMP_BranchSummary', 'BranchCode', 'AvgItemsSale', 'SELECT BranchCode,ISNULL(NoItemsSold/NULLIF(NoSales,0),0) AS AvgItemsSale FROM zzTEMP_BranchSummary'

--The Stored Procedure

CREATE PROCEDURE zzUpdateSummaryData

@TableName VARCHAR(30), @primarykey-2 VARCHAR(30), @FieldName VARCHAR(30), @sqlStatement NVARCHAR(4000)

AS

SET NOCOUNT ON

DECLARE @sql AS NVARCHAR(4000)

SET @sql='

UPDATE ' + @TableName + '

SET ' + @FieldName + '=tmpRecordSet.' + @FieldName + '

FROM

('+ @sqlStatement + ') tmpRecordSet

WHERE tmpRecordSet.' + @primarykey-2 + ' = ' + @TableName + '.' + @primarykey-2 + '

UPDATE ' + @TableName + '

SET ' + @FieldName + ' = 0

WHERE ' + @FieldName + ' IS NULL'

EXECUTE sp_executesql @sql

GO

Regards, Richard.