User Function

Question

User Function

Viewing 5 posts - 1 through 6 (of 6 total)

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Look up creating a "scalar function". That's a function returning a single value as opposed to returning an entire table.

create function myfunc(@myparams int, ...)

RETURNS @bob int

as

BEGIN

.....

RETURN

END

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

BAGs Right there with Babe Points: 793 More actions · Answer 2

thank you Matt

i will do that and let you know how i make out.

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 3

DECLARE@Sample TABLE (Data VARCHAR(100))

INSERT@Sample

SELECT'str001dt020208reg005trn0001'

SELECTs.Data,

t.*

FROM@Sample AS s

CROSS APPLY(

SELECTSUBSTRING(Data, 1, 6) AS Store,

SUBSTRING(Data, 7, 8) AS Date,

SUBSTRING(Data, 15, 6) AS RegisterNum,

SUBSTRING(Data, 21, 7) AS TransactionNum

) AS t

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 4

Ummm... why is Cross-Apply necessary here? Seems to be a bit of overkill when the simple substrings would probably do just fine...

--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)

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 5

Just in case there are more than piece of information of same type.

I suspect we haven't been told the full picture.

N 56°04'39.16"
E 12°55'05.25"