SQLTeam.com | Weblogs | Forums

Two part question


#1

This is a two part question. Part one i am certain is simple and i am just over looking it. maybe from staring at the computer screen all day.

Anyway i have this column in a database, it is currently nvarchar(5), inside this are 4 digit numbers, what i want to do is create a view that will take this column, pull out the first two digits of every number in that column, turn it into an INT to make it easier to check the value then depending on the value create a string. Here is one thing i have been trying and it keeps saying there is an error in my syntax.

Select case cast(left(empdept,2) as INT) WHEN 21 then 'Engineering' when 23 then 'Maintenance' else 'Production' from prodemptable

the second question pertains to locked stored proceedures. No one here locked them and well we would like to take a peek inside and see what is in there, we think they are old reminants of a long gone time clock system. But no one know for certain. Is there anyway to find out why the lock is there and how to at least view what is within the stored proceedure?


#2

The procedures could have passwords and/or be encrypted.


#3
Select case cast(left(empdept,2) as INT) 
    WHEN 21 then 'Engineering' 
    when 23 then 'Maintenance' 
    else 'Production' 
    END -- <<< THIS !!
from prodemptable

Not sure what you mean by "locked". What happens if you do this in SSMS:

Navigate to the Procedure (depends on the version of SSMS but perhaps:

[YourDatabaseName]
... Programability
... ... Stored Procedures
... ... ... [YourProcedureName]

RightClick the procedure and choose MODIFY

If MODIFY is greyed-out then try scripting instead:

RightClick : Script Stored Procedure as : CREATE to : New Query Window

You will probably get a pop-up error message. If the Procedure is Encrypted that message will include "The text is encrypted", but its also possible that you lack Permission

RightClick : Properties will also indicate, under "Options", if the procedure is encrypted


#4

@mrtweaver,
Why do you want to compare as an int?

SELECT
    CASE WHEN empdept LIKE '21%' THEN 'Engineering'
         WHEN empdept LIKE '23%' THEN 'Maintenance'
         ELSE 'Production'
    END
FROM
    prodemptable;