SQL Stored procedure and coding language

Question

So I am working on editing an existing stored procedure on a SQL server(2008 R2). I am running into some issues though due to my entry level knowledge of SQL. The issue is that the requirements for the report this generates have changed and the value of a field has changed from 2 to 3. The field length on the report was changed by the previous user, but they never accounted for instances where the data in the field only equals 2 characters. I.E. 20 or 03 instead of 100. This is causing the report to generate the values as such: '03 XX' instead of 003XX this is a problem because the system that receives this reads it from right to left so it will see these Null values and reject the whole report.

What I want to do is change the existing SET statement to a conditional statement that adds a padding '0' when the field value is only 2 char in length.

what I have currently is this:

SET @SupplyQuan = ISNULL((SELECT Unit FROM dbo.Supplyvalue(@UserID)
                              WHERE Number = 3),'')

What I need is a statement that reads similar to the following. The term I am having trouble with is the “Charactervalue”. I cannot find the SQL term for the value of a “cell” or field. I was thinking possibly “Columnvalue”, but that doesn’t seem to be correct. I am sure the answer is a very elementary one and I am probably overthinking this. Here is the code.

SET @SupplyQuan = ISNULL((SELECT Unit FROM dbo.Supplyvalue(@UserID)
                              WHERE Number = 3),'') IF “Charactervalue=3” ELSE IF “Charactervalue=2” THEN
SET @SupplyQuan = ISNULL('0'+(SELECT Unit FROM dbo.Supplyvalue(@UserID)
                              WHERE Number = 3),'')

Are you looking for the `replicate` function in SQL server as in [this example](http://stackoverflow.com/questions/9520661/formatting-numbers-by-padding-with-leading-zeros-in-sql-server)? take note you have to pass in a string, not int, int or numeric would truncate the leading zeros! — xQbert, May 21 '15 at 16:30
I can try the replicate function. I had not considered that yet. — Jared H, May 21 '15 at 16:44

score 1 · Answer 1 · answered May 21 '15 at 16:49

1

RIGHT('0'+Unit,3) will prefix Unit with '0' and return the rightmost 3 characters.

SET @SupplyQuan = ISNULL((SELECT RIGHT('0'+Unit,3) FROM dbo.Supplyvalue(@UserID) WHERE Number = 3),'')

answered May 21 '15 at 16:49

Anon

10,660
1
29
31

yes, that is definitely more efficient, but it may also cause an index scan due to the RIGHT() if that column is indexed. I would set a variable to the result of the query first, then apply the functions. – Greg May 21 '15 at 20:11
Given that `dbo.Supplyvalue(@UserID)` is not a table, but a table-valued function, and that `Unit` is not a predicate column, your "optimizations" have no effect. – Anon May 21 '15 at 20:33

score -1 · Accepted Answer · answered May 21 '15 at 16:42

You can use the CASE statement and LEN() function.

SELECT @SupplyQuan = case 
    when (len(Unit) = 3) then Unit
    when (len(Unit) = 2) then '0' + Unit
    else ''
END
FROM dbo.Supplyvalue(@UserID)
WHERE Number = 3

However I would first check the query plan, and if you see too many scans due to the function on the column name, i'd break it up.

declare @x varchar(3)
select @x = Unit FROM dbo.Supplyvalue(@UserID) WHERE Number = 3
select @SupplyQuan = CASE
    WHEN (len(@x) = 3) then Unit
    WHEN (len(@x) = 2) then '0' + Unit
    ELSE ''
end

That's IT!!! I had read on CASE and LEN, but had no idea how to use then in the proper context. Thank you again to you and to xQbert. This information is all very useful! — Jared H, May 21 '15 at 17:05

SQL Stored procedure and coding language

2 Answers2