ALTER PROCEDURE [dbo].[t_work]
@wl_id_m int,
@wl_id_e int,
@wl_id_n int,
@tbl_name nvarchar(20),
@day_no nvarchar(10),
@day_month nvarchar(10),
@day_years nvarchar(10)
AS
BEGIN
declare @Work_Status nvarchar(20)
SET NOCOUNT ON;
DECLARE ENS_cursor cursor for
select wl_id
from [ENS].[dbo].[tblWorkList]
where wl_id_m = @wl_id_m and wl_id_e = @wl_id_e and wl_id_n = @wl_id_n
OPEN ENS_cursor
FETCH NEXT FROM ENS_cursor
INTO @Work_Status
WHILE @@FETCH_STATUS = 0
BEGIN
update [ENSUSER].[dbo].[tblP30006] --ตรงนี้อยากแทนชื่อตารางด้วย @tbl_name
set t_work_status = @Work_Status
where t_day_no = @day_no and t_month_no = @day_month and t_year_no = @day_years
FETCH NEXT FROM ENS_cursor
INTO @Work_Status
END
CLOSE ENS_cursor
DEALLOCATE ENS_cursor
END
มันมีวิธีการนี้บ้างมั้ยครับ ลอง Search ส่วนใหญ่เจอแต่เรื่อง User Defined Table Type ซึ่งลองศึกษาดูแล้ว มันไม่ค่อยตรงตามความต้องการ หรือ ผมอาจไม่รู้ว่ามันใช้ยังไง หากใครรู้แนะนำทีนะครับ ^_^
Tag : .NET, Ms SQL Server 2014, Ms SQL Server 2016
ALTER PROCEDURE [dbo].[t_work]
@wl_id_m int,
@wl_id_e int,
@wl_id_n int,
@tbl_name nvarchar(20),
@day_no nvarchar(10),
@day_month nvarchar(10),
@day_years nvarchar(10),
@table_name nvarchar(100)
AS
BEGIN
declare @Work_Status nvarchar(20)
SET NOCOUNT ON;
declare @sql nvarchar(max);
DECLARE ENS_cursor cursor for
select wl_id
from [ENS].[dbo].[tblWorkList]
where wl_id_m = @wl_id_m and wl_id_e = @wl_id_e and wl_id_n = @wl_id_n
OPEN ENS_cursor
FETCH NEXT FROM ENS_cursor
INTO @Work_Status
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = "
update " + @table_name + " --ตรงนี้อยากแทนชื่อตารางด้วย @tbl_name
set t_work_status = " + @Work_Status + "
where t_day_no = " + @day_no + " and t_month_no = " + @day_month + " and t_year_no = " + @day_years + " ";
exec @sql;
FETCH NEXT FROM ENS_cursor
INTO @Work_Status
END
CLOSE ENS_cursor
DEALLOCATE ENS_cursor
END
The name 'update [SKTH_ENSUSER].[dbo].tblP30006
set t_work_status = 19
where t_day_no = 01 and t_month_no =03 and t_year_no = 2017' is not a valid identifier.
อันนี้โค้ดที่ผมลองเปลี่ยนครับ Code (SQL)
ALTER PROCEDURE [dbo].[twork_status]
@wl_id_m int,
@wl_id_e int,
@wl_id_n int,
@tbl_name nvarchar(20),
@day_no nvarchar(10),
@day_month nvarchar(10),
@day_years nvarchar(10)
AS
BEGIN
DECLARE @Work_Status nvarchar(10)
SET NOCOUNT ON;
DECLARE @sql nvarchar(max);
DECLARE ENS_cursor cursor for
select wl_id
from [SKTH_ENS].[dbo].[tblWorkList]
where wl_id_m = @wl_id_m and wl_id_e = @wl_id_e and wl_id_n = @wl_id_n
OPEN ENS_cursor
FETCH NEXT FROM ENS_cursor
INTO @Work_Status
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = N'update [SKTH_ENSUSER].[dbo].'+@tbl_name+'
set t_work_status = '+@Work_Status+'
where t_day_no = '+@day_no+' and t_month_no ='+ @day_month+' and t_year_no = '+@day_years+''
Exec @sql;
FETCH NEXT FROM ENS_cursor
INTO @Work_Status
END
CLOSE ENS_cursor
DEALLOCATE ENS_cursor
END
The name 'update [SKTH_ENSUSER].[dbo].tblP30006
set t_work_status = 19
where t_day_no = 01 and t_month_no =03 and t_year_no = 2017' is not a valid identifier.
จริง ๆ คำสั่งที่ได้น่าจะต้องเป็น
'update [SKTH_ENSUSER].[dbo].tblP30006
set t_work_status = 19
where t_day_no = '01' and t_month_no ='03' and t_year_no = 2017'
SET @sql = N'update [SKTH_ENSUSER].[dbo].'+@tbl_name+'
set t_work_status = '+@Work_Status+'
where t_day_no = '''+@day_no+''' and t_month_no ='''+ @day_month+''' and t_year_no = '+@day_years+''
Exec @sql;
SET @sql = N'update [SKTH_ENSUSER].[dbo].'+@tbl_name+'
set t_work_status = '+@Work_Status+'
where t_day_no = '''+@day_no+''' and t_month_no ='''+ @day_month+''' and t_year_no = '+@day_years+''
EXEC sp_executesql @sql;