Stored Proc Problem - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Proc Problem

Author

Topic

elnikoff
Starting Member

4 Posts

Posted - 2003-01-23 : 04:36:59

Hi everybody,

I'm quite new in the wonderful world of SQL-Server programming, but I try to do my best. I work with MSDE version 1 (i think, it was in the package with VB 6).

I found the way to create a stored proc, which works with two parameters, the first being an input string, the second being an output string which contains informations depending on the first string.

What I want to do is the following :
I want to get all the records in a table (Mvt) for which a field (doss) is null (no problem)
For each of these records, I want to compute the result of the previous stored proc, based on the content of a field (comment), and to put this result in the field doss.
So... Is it possible to do this with a single Update statement ? I don't think so, because I read that the Exec statement need a local-variable which should be given with the OUTPUT keyword, so I need a local variable in the part "SET doss = xxx" of the Update statement.
Is it right ?

For me, the answer must be in another stored proc, but I cannot imagine how to say that I want the job being done for each record.

I imagine that this stored proc should define a way to access to these records, but cannot find any idea which should be a starting point for getting the solution.

Can somebody help me ??

Thanks

Thierry

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-23 : 05:17:57

Easiest way to do this is to put the logic from your SP into an update statement

update Mvt
set doss = case when comment = 'qwer' then 'sss' when comment = 'zxsa' then 'dddd' else 'other' end
where doss is null

if you want to step through the table and call the SP for the result then

declare @comment varchar(128)
declare @s varchar(128)
while exists (select * from mvp where doss is null)
begin
select @comment = (select top 1 comment from mvp where dos is nuul)
exec spGetValue @comment, @s out
update mvp set doss = @s
where doss is null
and comment = @comment
end

could be a bit more efficient than this but will be slow whatever you do.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

elnikoff
Starting Member

4 Posts

Posted - 2003-01-27 : 02:59:40

Thanks a lot !

It's done, now !!

What if I want to put it in an insert trigger ??

I wanna say : how can I modify the value of a field in a record being added ?

example :
Somebody use the "INSERT INTO Mvt (Comment) VALUES ('blah blah blah')"
What is the code to have, automatically, something computed on the "blah blah blah" in another field which should be used as an 'index' field ? (The blah blah is long ;))
(Now, i know how to use the SP with a 'sort of' ADODB.Recordset ;)))))

Thierry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources