I have a table that stores user information. The table has a userid (identity) column in it. The table data is queried by a view which is referenced by a lot of sprocs, but we reference something called auid which is created by the UDF below. The UDF is called in the view and sprocs then join or query from the view based on auid. It appears that this routine is being called A LOT (thousands of times) and is causing some unnecessary load on our SQL Server. Is there a better way to take a user_id = 255 and turn it into a varchar = 000000255 (9 characters long)?
UDF syntax:
ALTER FUNCTION [dbo].[udf_AUID] (@user_id int)
RETURNS char(9)
with schemabinding
AS
BEGIN
DECLARE @user_id_string varchar(9)
DECLARE @rval char(9)
SELECT @user_id_string=CAST(@user_id as varchar(9))
SELECT @rval=LEFT('000000000',9-len(@user_id_string))+@user_id_string
RETURN @rval
END
Basic syntax from the view is:
ALTER VIEW [dbo].[v_users]
AS
SELECT
dbo.udf_AUID(dbo.users.user_id) AS auid,
user_id,
dbo.users.username
FROM dbo.users
Example call in a sproc looks like this:
DECLARE @auid CHAR(9)
SET @auid = '000002444' --normally passed in, but set here for example
SELECT dealer_range FROM users WHERE auid = @auid
DECLARE @cat_access TINYINT, @mit_access TINYINT
SELECT @cat_access = chan_access & 1, @mit_access = chan_access & 2
FROM appian.dbo.v_users
WHERE auid = @auid
Thanks in advance!