PASSING VALUE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

PASSING VALUE

Author

Topic

hizakemi
Starting Member

33 Posts

Posted - 2007-04-24 : 11:35:17

Help!

Attached proc is working fine. But how should I pass the value of @LexNexID without manul key in the value. How can I call this proc. to update comment in table tblSEMS_FTP_FINAL automatically

Thk

CREATE PROCEDURE DBO.SP_SEMS_ADD_COMMENT (
@lexnexid int,
@commentstring varchar(4000)
)
AS

DECLARE @Comment varchar(4000)
--DECLARE @CommentString varchar(4000)
--DECLARE @LexNexID INT

--SET @LexNexID = 1
SET @CommentString = ''

DECLARE Test CURSOR
FOR

SELECT
convert(varchar(4000),Comment) COMMENT
FROM dbo.TBLSEMS_FTP
WHERE LEXNEXID = @LexNexID
OPEN Test

FETCH NEXT FROM Test
INTO @Comment

WHILE @@FETCH_STATUS = 0
BEGIN

SET @CommentString = @CommentString + @Comment + '{CR}{LF}' + ' '

FETCH NEXT FROM Test
INTO @Comment
END

CLOSE Test
DEALLOCATE Test

--PRINT @CommentString

update tblSEMS_FTP_FINAL
set comment = @CommentString
where lexnexid=@LexNexID

GO

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-24 : 11:39:13

quote:
But how should I pass the value of @LexNexID without manul key in the value

exec DBO.SP_SEMS_ADD_COMMENT @lexnexid = 1, @commentstring = 'something'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-24 : 11:41:29

actually you don't need cursor at all


SET    @CommentString = ''
select @CommentString = @CommentString + convert(varchar(4000), Comment) +  '{CR}{LF}' + ' '
FROM   dbo.TBLSEMS_FTP
WHERE  LEXNEXID = @LexNexID

update tblSEMS_FTP_FINAL
set    comment  = @CommentString
where  lexnexid = @LexNexID

hizakemi
Starting Member

33 Posts

Posted - 2007-04-24 : 11:42:59

quote:
Originally posted by khtan

quote:
But how should I pass the value of @LexNexID without manul key in the value

exec DBO.SP_SEMS_ADD_COMMENT @lexnexid = 1, @commentstring = 'something'

KH

KH
I dont want manual key in the value. I need the stor procedure to call value from the table.

Th

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-24 : 11:45:26

quote:
I dont want manual key in the value. I need the stor procedure to call value from the table.

What do you mean by that ?

hizakemi
Starting Member

33 Posts

Posted - 2007-04-24 : 11:52:52

quote:
Originally posted by khtan

quote:
I dont want manual key in the value. I need the stor procedure to call value from the table.

What do you mean by that ?

KH

When I run my store proc. is asking for @lexnexid. I can key in as @lexnexid = '7'. But, all the @lexnexid values are coming from tblSEMSFTP- I want the proc. to automatically get the value from tblSEMSFTP and update the table. The proc. Wil be scheduled to run everyday.

Doe it makes sense?

Thank you for help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-24 : 12:01:17

read this link on how to write a concat udf http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx


update tblSEMS_FTP_FINAL
set    comment  = dbo.concat_udf(lexnexid)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources