Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

For varchar(max) should i use normarl update or .write in following situation Expand / Collapse
Author
Message
Posted Tuesday, June 24, 2014 11:36 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 28, 2014 10:33 PM
Points: 47, Visits: 143
Hi,

I want to update a col , which is null initially.

and i have to run UPDATE STMT at many places becuase it keeps error history for the row sprated by | and :

It can be done it two way.
1)
CREATE TABLE #remarkT
(ID int NOT NULL PRIMARY KEY,
Remark varchar(max));
GO

INSERT INTO #remarkT
VALUES (1, '');
-- Output for controll
SELECT * FROM #remarkT;


UPDATE #remarkT
SET Remark .Write(' error2: message2 , procedure2 etc|', LEN(Remark), NULL)
WHERE ID = 1;
SELECT * FROM #remarkT;

DROP TABLE #remarkT;


2)
UPDATE #remarkT SET Remark = ISNULL(Remark,'') + 'error1: message , procedure etc|'
WHERE ID =1

SELECT * FROM #remarkT


-----------------------------------------------------------------------

Q1) Please tel me which one is good in my situation.

Q2) Please tel me is there any better way to accomplish it.

Q3) It gives error if i put null in first insert stmt, so i have to put '' in insert stmt,
but that does not matter i am ready to put empty '' initially.

Is there any other way so that i can keep NULL initially in my col for each row IN #remarkT , except the normal update stmt.


yours sinclerely
Post #1585747
Posted Wednesday, June 25, 2014 12:23 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 5:03 AM
Points: 701, Visits: 1,260
you can use the 2nd option, Varchar(MAX) can be used like regular varchar(50). no need to use Mutator "Wite()". keep it simple.

i would suggest that to avoid the null value in the column you can use the default at the table definition. like this

CREATE TABLE #remarkT
(ID int NOT NULL PRIMARY KEY,
Remark varchar(max) Default(''));
GO

hope it helps
Post #1585756
Posted Monday, June 30, 2014 12:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 28, 2014 10:33 PM
Points: 47, Visits: 143
thank u,

I wanted to know , why msdn says to use it , though the excution plan shows some more task is done
when some .write is used.

yours sincerley
Post #1587513
Posted Monday, June 30, 2014 1:00 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 5:03 AM
Points: 701, Visits: 1,260
please share that execution plan.
Post #1587515
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse