如何将输入传递给存储过程

问题描述

CREATE PROC TASK_UPDATE @TCODE varchar(100)
AS
UPDATE CMO_TBL_TASK SET STATUS='Completed' WHERE TASKCODE IN (@TCODE)

EXEC TASK_UPDATE 'TS001 TS002'

TCODE   TNAME              TDESC                         _BY     STATUS
TS001	Client Meeting	   describing project to client	  WS	  Open
TS002	Part Management	   Module Completion	          WS	  Open
TS003	Update 	           Update values	          WS	  Progress

如果我执行上述步骤，则会显示0行受影响，我不知道传递参数的对齐方式.

我必须将所有3个tcode值传递给上述存储过程，并且所有三行都应在一次执行中更新.上面的sp是否会起作用，如果可以，请帮助我传递tcode值或与其他sp一起帮助我.

[edit] SHOUTING已删除，表格已格式化-OriginalGriff [/edit]

If i exec above procedure, 0 rows affected is showing, i dono how the alignment of passing parameters.

I have to pass all the 3 tcode values to the above stored procedure and all the three rows should be updated in a single execution. will the above sp works, if so , help me to pass tcode values or help me with other sp.

[edit]SHOUTING removed, table formatted - OriginalGriff[/edit]

推荐答案

CREATE PROC TASK_UPDATE @TCODE varchar(100)
AS
exec('UPDATE CMO_TBL_TASK SET STATUS=''Completed'' WHERE TASKCODE IN ('+@TCODE+')')

EXEC TASK_UPDATE '''TS001'',''TS002'',''TS003'''

或

OR

CREATE PROC TASK_UPDATE @TCODE varchar(100)
AS
UPDATE CMO_TBL_TASK SET STATUS='Completed' WHERE  charindex(','+cast(TASKCODE  as varchar(50))+',',@TCODE)>0

EXEC TASK_UPDATE 'TS001,TS002,TS003'

EXEC TASK_UPDATE 'TS001'
EXEC TASK_UPDATE 'TS002'
EXEC TASK_UPDATE 'TS003'

或使用循环更改每次迭代的ID.

or use looping for changing id for each iteration.

--Create test table
Create Table CMO_TBL_TASK (TASKCODE varchar(20), STATUS varchar(20))
insert into CMO_TBL_TASK values ('TS001','Open')
insert into CMO_TBL_TASK values ('TS002','Open')
insert into CMO_TBL_TASK values ('TS003','Progress')


--Create 
CREATE PROC TASK_UPDATE @TCODE varchar(100)
AS
UPDATE CMO_TBL_TASK SET STATUS='Completed' WHERE TASKCODE IN (SELECT DATA FROM dbo.Split(@TCODE,','))

EXEC TASK_UPDATE 'TS001,TS002'


--Use any of the split function to split the data

CREATE FUNCTION dbo.Split
(
	@RowData nvarchar(2000),
	@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
	Id int identity(1,1),
	Data nvarchar(100)
)
AS
BEGIN
	Declare @Cnt int
	Set @Cnt = 1

	While (Charindex(@SplitOn,@RowData)>0)
	Begin
		Insert Into @RtnValue (data)
		Select
			Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

		Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
		Set @Cnt = @Cnt + 1
	End

	Insert Into @RtnValue (data)
	Select Data = ltrim(rtrim(@RowData))

	Return
END

split函数选自以下内容:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

split function is picked from the following :
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

这篇关于如何将输入传递给存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！