how to use reusable code

Question

Post reply

how to use reusable code

neliii

Say Hey Kid

Points: 701
More actions
May 10, 2010 at 1:08 pm

#232488

Hi guys, Please help me with this ASAP
I got a code which is reused so many times in my query...it's a long code..i want to know wether i can set a name to that code and use that name instead of that code..i tried of using userdefined function but i couldnt get that as i'm not that much practised on that..
so here is the code..
(substring(stuff(column,CHARINDEX('.', column),CHARINDEX('\',column,3)-CHARINDEX('.',column),''),0,charindex('\',stuff(column,CHARINDEX('.', column),CHARINDEX('\',column,3)-CHARINDEX('.',column),''),charindex('\',stuff(column,CHARINDEX('.', column),CHARINDEX('\',column,3)-CHARINDEX('.',column),''),charindex('\',stuff(column,CHARINDEX('.', column),CHARINDEX('\',column,3)-CHARINDEX('.',column),''),3)+1)+1)),column)
i need to use the above code for so many times in my query
the output of that code is a string..
in breif the string will be like
\\tom\kit.sun.ek\abs\free\tog
if i use the above code for this string i'll get the result as
\\tom\kit\abs\free
in the above code colum=\\tom\kit.sun.ek\abs\free\tog
Thanq in advance

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66773 More actions · Answer 1

creating custom functions and other stored procedure alike objects is the way to go.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

neliii (5/10/2010)
Hi guys, Please help me with this ASAP
I got a code which is reused so many times in my query...it's a long code..i want to know wether i can set a name to that code and use that name instead of that code..i tried of using userdefined function but i couldnt get that as i'm not that much practised on that..
so here is the code..
(substring(stuff(column,CHARINDEX('.', column),CHARINDEX('\',column,3)-CHARINDEX('.',column),''),0,charindex('\',stuff(column,CHARINDEX('.', column),CHARINDEX('\',column,3)-CHARINDEX('.',column),''),charindex('\',stuff(column,CHARINDEX('.', column),CHARINDEX('\',column,3)-CHARINDEX('.',column),''),charindex('\',stuff(column,CHARINDEX('.', column),CHARINDEX('\',column,3)-CHARINDEX('.',column),''),3)+1)+1)),column)
i need to use the above code for so many times in my query
the output of that code is a string..
in breif the string will be like
\\tom\kit.sun.ek\abs\free\tog
if i use the above code for this string i'll get the result as
\\tom\kit\abs\free
in the above code colum=\\tom\kit.sun.ek\abs\free\tog
Thanq in advance

I suspect that you're going to have a busy day when you finally get your code written because your formula results in the following...

Msg 102, Level 15, State 1, Line 3

Incorrect syntax near ','.

So that I don't actually have to read a 416 character, undocumented, unformatted, single line formula in your code, tell me this... are you always trying to return the 3rd directory after the machine name or are you just trying to strip off everything after the last backslash?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions