Not quite sure if/how to use a function

[Ronald S. Cook]

In many procs, I will be receiving @CreatedByLoginName as a parameter and
needing to retrieve the associated ID value to use in the rest of the proc.

I could write this in every proc:

DECLARE @CreatedByLoginID uniqueidentifier
SELECT @CreatedByLoginID = LoginID FROM Login WHERE LoginName =
@CreatedByLoginName

.. but I'm thinking maybe a function? I'm not too familiar with functions
and I see a few different types.

Can anyone please advise how I should do this in a function, or if I should?

Thanks,
Ron

[Tibor Karaszi]

Yes, if you do this a lot, a UDF can be a good thing. You would want a "Scalar User Defined
Function", something like:

CREATE FUNCTION dbo.ResolveLogin(@CreatedByLoginID uniqueidentifier)
AS
BEGIN
RETURN (SELECT LoginID FROM Login WHERE LoginName = @CreatedByLoginName)
END

One thing to watch out for is usages like:

SELECT dbo.ResolveLogin(colname), col2, col3, ...
FROM tbl
WHERE ...

Using a scalar function in a SELECT statement this way means that SQL Server will execute the code
inside your function once per row in the table (or result set, depending on where you use it). This
can be very bad for performance. But using it for variable assignment is just fine.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

"Ronald S. Cook" <rc...@westinis.com> wrote in message news:upxHAExw...@TK2MSFTNGP02.phx.gbl...

[Ronald S. Cook]

What's the difference between doing it this way vs a stored proc? I.e.
calling a proc from within the other proc.

Thanks.

"Tibor Karaszi" <tibor_please.n...@hotmail.nomail.com> wrote in
message news:23253762-F5F6-46AD...@microsoft.com...

[Tibor Karaszi]

> What's the difference between doing it this way vs a stored proc?

How you use it. A proc you execute, and it can return output param and a return code. A function you
use in some other statement.

EXEC procname

SET @a = dbo.myFunc(...)

SELECT dbo.myFunc(col1), col2
FROM tbl
WHERE ...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

"Ronald S. Cook" <rc...@westinis.com> wrote in message news:eaTiVa$wHHA...@TK2MSFTNGP04.phx.gbl...