CODEWITHSUNDEEP
sqlstored-proceduresstringuser-defined-functions
dtaylo04
dtaylo04

Reputation: 203

SQL string manipulation

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!

Upvotes: 0

Views: 991

Answers (3)

Jon Erickson
Jon Erickson

Reputation: 114916

You can use two functions that will help you out: RIGHT and REPLICATE

SELECT RIGHT(REPLICATE('0', 9) + '123456789', 9)    -- Result: 123456789
SELECT RIGHT(REPLICATE('0', 9) + '255', 9)          -- Result: 000000255
SELECT RIGHT(REPLICATE('0', 9) + '12', 9)           -- Result: 000000012
SELECT RIGHT(REPLICATE('0', 9) + '1', 9)            -- Result: 000000001

Upvotes: 2

Eric
Eric

Reputation: 95153

RIGHT('000000000' + CAST(@user_id as varchar(9)), 9)

This way, you aren't doing multiple casts, and you don't have a LEN to worry about.

Upvotes: 0

Gandalf
Gandalf

Reputation: 9855

Any reason you can't just store the userid that way to begin with?

Upvotes: 2

Related Questions