September 25, 2015 at 9:28 am
Hi all
I get the feeling this should be easy but i just can't seem to figure it out :blush:
I've got an update statement with a subquery (I'll post the code further down) that I need to either make dynamic or do something else to make sure it does what I want.
The query is as follows:-
UPDATE dbo.tbl_Process_List_Control_Table
SET LastUpdateDateTime = (
SELECT ISNULL(MAX([LatestRowUpdateDateTime]), @LastUpdateDateTime)
FROM [wtbl_Process_List_Patient]
)
WHERE ProcessList = @ProcessName
This is a called proc with the following parameters:-
@LastUpdateDateTime is the date of the last record to be loaded
@ProcessName is the name of the process that was started/finished.
I need to make [wtbl_Process_List_Patient] dynamic so it looks at a different table based on a passed parameter.
I've tried making the whole thing dynamic but it states I need to declare @LastUpdateDateTime which I can't see how to do as it's already passed to the proc (as is the process name).
Any help on this would be greatly appreciated.
September 25, 2015 at 9:34 am
How have you tried making the whole thing dynamic? Did you use sp_executesql? Please will you post what you tried?
John
September 25, 2015 at 12:47 pm
I'm still not a fan of dynamic procedures with variable table names. You may consider writing the procedure for each table that it will run against and having the front end caller determine which it needs to call based off of the table name in the variable. Make the 'dynamic' part in your calling code and not in your database code.
September 25, 2015 at 2:49 pm
Hope this helps. Could do more if you had posted what you have done so far.
d e c l a r e --remove the spaces in the preceding word, only way I could post the code.
@SQLCmd nvarchar(max),
@SQLParm nvarchar(max),
@TableName sysname;
set @TableName = N'MyTable';
set @SQLCmd = N'
UPDATE dbo.tbl_Process_List_Control_Table SET
LastUpdateDateTime = (SELECT ISNULL(MAX([LatestRowUpdateDateTime]), @inLastUpdateDateTime) FROM [' + @TableName + N'])
WHERE
ProcessList = @inProcessName
';
set @SQLParm = N'@inProcessName varchar(50), @inLastUpdatedDateTime datetime';
exec sp_executeSQL @SQLCmd, @SQLParm, @inProcessName = @ProcessName, @inLastUpdatedDateTime = @LastUpdatedDateTime;
September 28, 2015 at 2:01 am
John Mitchell - That's what I was trying to do and failing miserably.
John Rowan - I'm trying to keep the number of procs to a minimum (for ease of scaling) which is why I wanted it dynamic.
Lynn - Thanks for that. I'd tried to do the first bit of what you posted, but didn't realise you could use parameters in this fashion. Just one question, why have you defined @TableName as sysname?
September 28, 2015 at 10:12 am
richardmgreen1 (9/28/2015)
John Mitchell - That's what I was trying to do and failing miserably.John Rowan - I'm trying to keep the number of procs to a minimum (for ease of scaling) which is why I wanted it dynamic.
Lynn - Thanks for that. I'd tried to do the first bit of what you posted, but didn't realise you could use parameters in this fashion. Just one question, why have you defined @TableName as sysname?
I use the same datatype for table names as Microsoft uses in its system views (sys.tables for instance).
September 29, 2015 at 1:45 am
Thanks Lynn
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply