I have a SQL function in DB server?

Question

Hi All I have a SQL function in DB server created for padding with string with int. i have an identity int field in DB. My question is. how to get this identity field to pass to SQL Function where it padds and return string as + and display in front end. i want to know the logic.? Main thing is..i have sqlcommand class where i have writen insert query excluding identity field. question lies is how to pass the identity field to sql and padd to string and return the value and dispaly in VB/C#? whether Direction in C# to be used for input/output parameter.? Any suggestions or Ideas plese suggest to me.. thanks Raghu

Santhosh Kumar Jayaraman · Answer

you dont need function for that and my advice is dont use function for these simpler things In select query  you can say select 'ABC'+Cast(EMpid as varchar(100)) from emptable. Check this. create table emptable (EmpId int Primary key EmpName varchar(50) DeptId int) insert into emptable values(1'santhosh'101) insert into emptable values(2'Kumar'101) insert into emptable values(3'Amit'102) insert into emptable values(4'Vijay'102) insert into emptable values(5'Ajith'102) insert into emptable values(6'Ram'103) select 'abc'+Cast(EMpid as varchar(100))empNamedeptid from emptable

Raghu Janardhan · Answer

thanks all !! found this simple solution.. create table temp ( id int identity(11) username varchar(20) userid AS username +'_'+ CAST(ID AS VARCHAR(10)) PERSISTED ) tahts all helped..me thanks every one..

Jignesh Trivedi · Answer

hi LPAD and RPAD is supported in oracle. Insted of this you can use LEFT and RIGHT function.. hope this will help you.

Raghu Janardhan · Answer

Thanks!! Is it possible to use LPAD or RPAD here.. for padding in DB

Jignesh Trivedi · Answer

hi Raghu what you want to padd to identity field? zero or any other string? and yes you must mark this field as output parameter if you want it as out put from SQL Command. Please refer... http://weblogs.asp.net/andrewrea/archive/2008/02/19/examples-of-using-system-data-parameterdirection-with-sql-server.aspx hope this will help you.

Sukesh Marla · Answer

your question is not clear You want to create function whcih will take 1 string argument and returns String+Identity and you will call this function after insert If i am right Create Function as Create Function GetrString(@string varchar(1000)) as varchar Begin Declare @Int int set @Int=Scope_Identity() retutn @string + @Int end Create storedProcedure as Create Procedure Sp1 ( @Para1 varchar(100)...... ... @String1 varchar(1000) ) begin iSNert into t1... Select GetString(@string1 end Check this is correct answer id helped.