PREFIX - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

PREFIX

Author

Topic

Arun.G
Yak Posting Veteran

81 Posts

Posted - 2010-06-28 : 02:37:44

I HAVE ONE TABLE LIKE BELOW:

EMP_PREFIX ORG_LOGO

TATA SOME LOGO
TATASTEEL SOME LOGO

like that

And

i have EMP table which contains

emp_id fname lname position function

1 aa bb mgr IT
2 cc dd unithead production

like that

Now in front end they are stroing the empid as

for example:

emp_id :TATA1, TATA2, TATASTEEL1,TATASTEEL2 LIKE THAT USING EMP-PREFIX FROM PREFIX TABLE

Now, in sdearch criterial, they will select : TATA1 means, it should display the related details of that employee,how do do it in stored procedure:

i did like this:

SELECT

NAME=UPPER((PI_FIRSTNAME + ' ' + PI_MIDDLENAME + ' ' + PI_LASTNAME)),
FUNCTION,
POSITION

FROM
EMP
WHERE
emp_id=1

but i have to pass arguement as TATA1 instead of 1 in emp_id

how to do it?

Sachin.Nand

2937 Posts

Posted - 2010-06-28 : 03:06:55

Something like this



Create Procedure Search
@emp_id as varchar(20)
AS
SELECT

NAME=UPPER((PI_FIRSTNAME + ' ' + PI_MIDDLENAME + ' ' + PI_LASTNAME)),
FUNCTION,
POSITION 

FROM 
EMP 
WHERE 
emp_id=@emp_id

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Arun.G
Yak Posting Veteran

81 Posts

Posted - 2010-06-28 : 04:31:40

hi thx for ur reply, but its not the result what i expect
,

if i pass @emp_id=TATA1 means , its will not execute, bcoz v r not storing anywhere emp_id as TATA1,
only emp_id=1, by displaying in front end only the emp_prefix (TATA) is taken from emp_prefix table and displayed as TATA1

so in stored procedure , i have to take care emp_id and concatenated with emp_prefix and based on that related info should display

should (emp_id +emp_prefix )can v store in one parameter and can v pass that parameter as input arguement?

Sachin.Nand

2937 Posts

Posted - 2010-06-28 : 05:47:54

Well you will have to write a function that will return the emp_id concatenated to the emp_prefix provided the emp_prefix does not contain any numeric values in itself.You can convert the following query to a UDF to return the emp_id from the emp_prefix parameter passed to the UDF.


declare @dt as varchar(50)='TATA123'
declare @col as varchar(max)=''
select @col=@col+ col from
(
select SUBSTRING(@dt,number,1)as col from master.dbo.spt_values where type='p'
and number<=len(@dt)

)t where col like '[0-9]'

select @col

However I wonder why are you not using a split character that can differentiate between emp_prefix and emp_id when you concatenate them.Something like this TATA_123

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources