Table variables have locks on them too...

You can... But it could lead to problems. You just have to use a table hint to control the locking mechanism.

UPDATE dbo.Mytable

WITH (ROWLOCK)

SET MyCol= 'SomeValue'

...

Be VERY careful doing stuff like...

FYI, this is a repost of another discussion going through the same perambulations over here.

http://www.sqlservercentral.com/Forums/Topic520048-338-1.aspx

Do you have to have a title? Grand-High-Worshipful-Poobah-and-Support?

Seriously it sounds like you're the "IT Guy." I did that for a time. I wrote WordPerfect macros, designed databases, ran networks, punched...

It's just a straight-forward cost/benefit analysis along with risk and mitigation. Do you need the new functionality or is it nice to have? You need it. How big is the...

Locking is determined by the query engine. It always goes with a least cost lock. From the sounds of things, you don't have too much data in the table so...

Everyone has covered it so well up to this point, especially Phil Factor's bit. The one technical thing I would add is that a new DBA must learn the logical...

From the BOL:

You're locking a range when what you really want is to lock a row.

Even though GSquared eliminated the cursor, there are still other optimizations pointed out by others that can be done to the proc.

You're using the parameter twice, once to set & once to search. It can't be done like that. You have to refer to the column twice, but if you have...

anjan.ashok (6/19/2008)

---

hi grant see my sp pls and help me

CREATE PROCEDURE sp_replace_value (

@strcvalue varchar(100),

@strdesc nvarchar(100)

)

as

update testtbl set value=@strconfigvalue,desc=@strconfigdesc where value=@strconfigvalue

here assume we have...

You should be able to take the same structures that I supplied and modify it for a different column and/or data type. It all works the same way.

UPDATE table

SET col1...

This was a cross-post. Answers there too:

http://www.sqlservercentral.com/Forums/Topic520048-338-1.aspx

Please don't cross-post.

You mean something like this?

CREATE PROCEDURE UpdateEmail

(@OldEmail nvarchar(275)

,@NewEmail nvarchar(275)

AS

BEGIN TRY

UPDATE MyEmail

SET Email = @NewEmail

WHERE Email = @OldEmail

END

BEGIN CATCH

RETURN ERROR_NUMBER();

END

So much for contacting people.

I'm still baffled. I'm not sure why this is happening like it is. Odd stuff.