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
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 JayaramanPosted Aug 20, 2012, 9:26 AM
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)),empName,deptid from emptable
Raghu JanardhanPosted Aug 24, 2012, 8:59 AM
found this simple solution..
create table temp
( id int identity(1,1),
username varchar(20),
userid AS username +'_'+ CAST(ID AS VARCHAR(10)) PERSISTED
)
tahts all helped..me thanks every one..
Jignesh TrivediPosted Aug 21, 2012, 11:25 PM
LPAD and RPAD is supported in oracle.
Insted of this you can use LEFT and RIGHT function..
hope this will help you.
Raghu JanardhanPosted Aug 21, 2012, 8:15 AM
Is it possible to use LPAD or RPAD here.. for padding in DB
Jignesh TrivediPosted Aug 20, 2012, 11:48 PM
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 MarlaPosted Aug 20, 2012, 10:28 AM
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.