change character in a string

i would like to know ... How to replace a certain character in a string not using a cursor ... or .. a mixed of substring an charindexes ...
i would like to know if there is any centence that do that
thanksTry REPLACE|||I fonud it

its REPLACE

sorry

bye bye

Challenge - Swap two columns

Ok .. a challenge ...
How do you swap two columns based on some condition without using a cursor ( I mean a set based solution).alter table foo add tempcol

update foo set tempcol=field1

update foo set field1 = field2

update foo set field2=tempcol

alter table drop column tempcol

you did not say it had to be good, you did not say it had to be efficient, you said merely no cursor|||did you forget it is not possible to add a column and update it in the same sql batch ...|||forget? no, because i never knew that to begin with

okay, so just run those steps in separate batches|||forget? no, because i never knew that to begin with

You are joking ? Right ?|||not in the slightest, no, i was not joking

i don't know what a "batch" is

i just know it's something you DBAs concern yourself with

is a batch like a transaction block? isn't there a COMMIT statement you can use?|||not in the slightest, no, i was not joking

i don't know what a "batch" is

i just know it's something you DBAs concern yourself with

is a batch like a transaction block? isn't there a COMMIT statement you can use?
"Go" and Read up on "GO" statement in the holy book ...
Also
Try this

use pubs
go
create table Mytable99 (a int)
go
insert into Mytable99 select 1
go
begin tran
alter table MyTable99
add b int
commit tran
update MyTable99 set b = 2
go

Then Try this

drop table MyTable99
go
use pubs
go
create table Mytable99 (a int)
go
insert into Mytable99 select 1
go
begin tran
alter table MyTable99
add b int
commit tran
go
update MyTable99 set b = 2
go

drop table MyTable99
go|||Try this
use pubs
go
create table Mytable99 (a int)
go
insert into Mytable99 select 1
go
begin tran
alter table MyTable99
add b int
commit tran
update MyTable99 set b = 2
go

Error: Invalid column name 'b'. (State:S0022, Native Code: CF)

Then Try this
drop table MyTable99
go
use pubs
go
create table Mytable99 (a int)
go
insert into Mytable99 select 1
go
begin tran
alter table MyTable99
add b int
commit tran
go
update MyTable99 set b = 2
go

drop table MyTable99
go

This command did not return data, and it did not return any rows

sweet

and your point is?

and how does this swap two columns??????|||I am new to SQL Server so maybe I am misunderstanding the requirement but is there anything wrong with the following?

update table99 set a=b, b=a

with an optional where condition.|||I am new to SQL Server so maybe I am misunderstanding the requirement but is there anything wrong with the following?

update table99 set a=b, b=a

with an optional where condition. At least the way that I understand Enigma's question, your answer is exactly what I would have suggested. The part that I'm still fuzzy on is that the rest of this thread seems to have veered off on a tangent, which makes me wonder if I understood the original question.

-PatP|||That was the answer I was looking for ... coz I was intrigued by the way sql server handles updates in this case

R937 : the point is the sql batch ... all statements in a sql batch are compiled into one execution plan ... and you cannot alter and update in the same sql batch ... Transaction has no effect on a sql batch ...|||I am new to SQL Server so maybe I am misunderstanding the requirement but is there anything wrong with the following?

update table99 set a=b, b=a

with an optional where condition.

Not a shining moment for the gurus of dbforums.com...|||Yep. For those geeky enough to care, the ISO standard calls for all of the RValues in an UPDATE statement to evaluate before any of the LValues are resolved. This means that all of the computations have to be complete before the first change is allowed to happen.

-PatP

Centralizing Two Stored Procedure Queries (one using Cursor Ou

Alejandro,
Sorry, one more question: In your example, would there be any way to return
only a subset of the fields in the stored proc query. For example, if you
wanted a temporary table with only the orderid field. Like this:
create table #t (orderid int)
insert into #t(orderid)
exec dbo.p1 @.sd, @.ed
I'll need it like this because my store proc "spTrucker" has many, many
fields, and I only need about 4 or 5 of them in the cursor.
Thanks again,
John
"Alejandro Mesa" wrote:
> John,
> You do not need a cursor output parameter. You can grab the result of the
sp
> "spTrucker" in sp "spDistinctCodes". See "insert into ... exec ..." in BOL
.
> Example:
> use northwind
> go
> create procedure dbo.p1
> @.sd datetime,
> @.ed datetime
> as
> set nocount on
> select
> orderid, orderdate, customerid
> from
> dbo.orders
> where
> orderdate >= convert(char(8), @.sd, 112)
> and orderdate < convert(char(8), dateadd(day, 1, @.ed), 112)
> return @.@.error
> go
> create procedure dbo.p2
> @.sd datetime,
> @.ed datetime
> as
> set nocount on
> declare @.orderid int
> declare @.orderdate varchar(25)
> declare @.customerid nchar(5)
> create table #t (orderid int, orderdate datetime, customerid nchar(5))
> insert into #t(orderid, orderdate, customerid)
> exec dbo.p1 @.sd, @.ed
> declare my_cursor cursor local fast_forward
> for
> select orderid, orderdate, customerid
> from #t
> order by orderdate
> open my_cursor
> while 1 = 1
> begin
> fetch next from my_cursor into @.orderid, @.orderdate, @.customerid
> if @.@.error != 0 or @.@.fetch_status != 0 break
> raiserror('%d %s %s', 10, 1, @.orderid, @.orderdate, @.customerid) with nowa
it
> end
> close my_cursor
> deallocate my_cursor
> go
> exec dbo.p2 '19970701', '19970731'
> go
> drop procedure p2, p1
> go
> How to share data between stored procedures
> http://www.sommarskog.se/share_data.html
>
> AMB
>
> "John Walker" wrote:
>John,
The ddl for the temporary table need to match all columns returned by the
sp. You can not grab just a subset.
AMB
"John Walker" wrote:
> Alejandro,
> Sorry, one more question: In your example, would there be any way to retu
rn
> only a subset of the fields in the stored proc query. For example, if you
> wanted a temporary table with only the orderid field. Like this:
> create table #t (orderid int)
> insert into #t(orderid)
> exec dbo.p1 @.sd, @.ed
> I'll need it like this because my store proc "spTrucker" has many, many
> fields, and I only need about 4 or 5 of them in the cursor.
> Thanks again,
> John
> "Alejandro Mesa" wrote:
>