Michael Krumpe
asked on
Stored Procedure with Cursor Replace then Update
I have not done any work with cursor's before in a stored procedure, I've researched it, but I can seem to get my head around it
I have a variable @message char(1000)
I have table contacts
I need to query table contacts where userid = @userid
With the results, I need to add records to another table cque
With that though, the varaiable @message may have text in it that I need to replace before adding it to the cque table
So I dont know how this would all work... but logically, I see it
-------------------------- ---------- ---------- ---------- ---------- ---------- -----
ALTER PROCEDURE [dbo].[sp_broadcast]
@userid int,
@message char(1000)
-- Declare cursor and query contacts table
DECLARE c CURSOR
For Select * from contacts where userid = @userid
open c
WHILE (@@FETCH_STATUS=0) BEGIN
/*
Here's where I get confused
I need to get fields from the cursor and do a replace such as
REPLACE(@message,'-fname-' ,cursorfna mevalue)
REPLACE(@message,'-lname-' ,cursorlna mevalue)
REPLACE(@message,'-phone-' ,cursorpho nevalue)
*/
END
close c
deallocate c
-------------------------- ---------- ---------- ---------- -----
I think I'm halfway there... I just dont know how to put it all together... I think what I dont know how to do is how to get the values out of the cursor... not sure
Help!
I have a variable @message char(1000)
I have table contacts
I need to query table contacts where userid = @userid
With the results, I need to add records to another table cque
With that though, the varaiable @message may have text in it that I need to replace before adding it to the cque table
So I dont know how this would all work... but logically, I see it
--------------------------
ALTER PROCEDURE [dbo].[sp_broadcast]
@userid int,
@message char(1000)
-- Declare cursor and query contacts table
DECLARE c CURSOR
For Select * from contacts where userid = @userid
open c
WHILE (@@FETCH_STATUS=0) BEGIN
/*
Here's where I get confused
I need to get fields from the cursor and do a replace such as
REPLACE(@message,'-fname-'
REPLACE(@message,'-lname-'
REPLACE(@message,'-phone-'
*/
END
close c
deallocate c
--------------------------
I think I'm halfway there... I just dont know how to put it all together... I think what I dont know how to do is how to get the values out of the cursor... not sure
Help!
Why are you using a cursor?
insert into yourothertable (value1, value2)
select replace(@message,'-fname-' , fname),
replace(@message,'-lname-' , lname),
replace(@message,'-phone-' , phonevalue)
from contacts
FNAME, LNAME, and Phonevalue would be the actual column names from your contacts table...
Brett
insert into yourothertable (value1, value2)
select replace(@message,'-fname-'
replace(@message,'-lname-'
replace(@message,'-phone-'
from contacts
FNAME, LNAME, and Phonevalue would be the actual column names from your contacts table...
Brett
-- you must specify all variables from select, when opening cursor. so choose only those, you need and with correct datatype.
ALTER PROCEDURE [dbo].[sp_broadcast]
@userid int,
@message char(1000)
declare @curVariableForCol1 int,@curVariableForCol2 varchar(20),@curVariableFo rCol3 int
DECLARE c CURSOR
For Select col1, col2,col3, ... from contacts where userid = @userid
open c
fetch next from c into @curVariableForCol1, @curVariableForCol2, @curVariableForCol3 /* from now col1,col2 and col3 are stored in variables declared above */
WHILE (@@FETCH_STATUS=0) BEGIN
/*
REPLACE(@message,'-fname-' ,cursorfna mevalue)
REPLACE(@message,'-lname-' ,cursorlna mevalue)
REPLACE(@message,'-phone-' ,cursorpho nevalue)
*/
fetch next from c into @curVariableForCol1, @curVariableForCol2, @curVariableForCol3
END
close c
deallocate c
--rem: but do you really need a cursor? something like this query is not working?
insert into cque
select clumn1, column2,..., REPLACE(@message,'-fname-' ,cursorfna mevalue)
from contacts where userid = @userid
ALTER PROCEDURE [dbo].[sp_broadcast]
@userid int,
@message char(1000)
declare @curVariableForCol1 int,@curVariableForCol2 varchar(20),@curVariableFo
DECLARE c CURSOR
For Select col1, col2,col3, ... from contacts where userid = @userid
open c
fetch next from c into @curVariableForCol1, @curVariableForCol2, @curVariableForCol3 /* from now col1,col2 and col3 are stored in variables declared above */
WHILE (@@FETCH_STATUS=0) BEGIN
/*
REPLACE(@message,'-fname-'
REPLACE(@message,'-lname-'
REPLACE(@message,'-phone-'
*/
fetch next from c into @curVariableForCol1, @curVariableForCol2, @curVariableForCol3
END
close c
deallocate c
--rem: but do you really need a cursor? something like this query is not working?
insert into cque
select clumn1, column2,..., REPLACE(@message,'-fname-'
from contacts where userid = @userid
actually, you would have one more value so the columns match:
insert into yourothertable (value1, value2,value3)
value1, value2, value3 are the columns in the table you want to insert into.
Maybe if you expand a little more on what you're trying to do. I don't see, based on your post, why you need a cursor. Cursors should be avoided unless needed...
insert into yourothertable (value1, value2,value3)
value1, value2, value3 are the columns in the table you want to insert into.
Maybe if you expand a little more on what you're trying to do. I don't see, based on your post, why you need a cursor. Cursors should be avoided unless needed...
Agree with arbert. I don't think you need a cursor. A combination of CASE / REPLACE statements should do it
Cursors are slower, more complicated, and usually, avoidable
Cursors are slower, more complicated, and usually, avoidable
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
So much great feedback. I only chose a cursor as I didnt know that I could do a replace inside a select, inside an insert statement. I will try that.
"So much great feedback. I only chose a cursor as I didnt know that I could do a replace inside a select, inside an insert statement. I will try that."
Definately--only think about using a cursor as a last resort :)
Definately--only think about using a cursor as a last resort :)
You could do the following...
--------------------------
ALTER PROCEDURE [dbo].[sp_broadcast]
@userid int,
@message char(1000)
-- Declare cursor and query contacts table
DECLARE c CURSOR FOR
Select fname,lname,phone from contacts where userid = @userid
open c
FETCH NEXT FROM c INTO @fname,@lname, @phone
WHILE (@@FETCH_STATUS=0) BEGIN
REPLACE(@message,'-fname-'
REPLACE(@message,'-lname-'
REPLACE(@message,'-phone-'
FETCH NEXT FROM c INTO @fname,@lname, @phone
END
close c
deallocate c
--------------------------
Let me know if u have further questions...
Here I presume the table contacts has columns like fname,lname,phone...