How to pass input to a stored procedure

Question

5.00/5 (1 vote)

See more:

SQL-Server-2005

SQL

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

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]

Posted 21-Dec-11 22:59pm

idhris2011

Updated 21-Dec-11 23:21pm

OriginalGriff

v3

Add a Solution

Comments

OriginalGriff 22-Dec-11 5:22am

DON'T SHOUT. Using all capitals is considered shouting on the internet, and rude (using all lower case is considered childish). Use proper capitalisation if you want to be taken seriously.

Amir Mahfoozi 24-Dec-11 11:01am

How are you ? :)

hitech_s 26-Dec-11 0:19am

separate TCODE with comas while sending

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

sheraj ahmad · Answer 1 · 2011-12-22T00:31:00

try this way .

SQL

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

SQL

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'

uspatel · Answer 2 · 2011-12-21T23:17:00

Solution 1

EXEC TASK_UPDATE 'TS001 TS002' it does not work.'TS001 TS002' treated as one ID.
use

SQL

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

or use looping for changing id for each iteration.

Posted 21-Dec-11 23:17pm

uspatel

Comments

[no name] 22-Dec-11 23:07pm

this query also work fine just check it.

Om Prakash Pant · Answer 3 · 2011-12-21T23:19:00

SQL

--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 function is picked from the following :
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

Amir Mahfoozi · Answer 4 · 2011-12-22T06:52:00

First create a split function in your database :
http://blog.codelab.co.nz/2009/07/29/split-function-for-t-sql-using-xml/[^]

Then change your query to this :

SQL

CREATE PROC TASK_UPDATE @TCODE varchar(100)
AS
UPDATE CMO_TBL_TASK SET STATUS='Completed' WHERE TASKCODE IN ( select * from dbo.split(',', @TCODE) )

Then call it in this way :

SQL

TASK_UPDATE 'TS001,TS002'

Hope it helps.

How to pass input to a stored procedure

4 solutions

Solution 4

Solution 1

Solution 2

Solution 5

Add your solution here

Preview 0