Sql Server-- Function

You have multiple options
1) While inserting record into table use following query:
 
 insert into TableName(ID, Sub_id, name) values(idValue, 'K00ID' + convert(varchar, Sub_idValue), 'Name')


2) create procedure As follow (this will change value in table)
Alter Proc Proc_Temp
(
@SubId int,
@OutPut varchar(20) output
)
As
Begin
update TableName set Sub_id= 'K00ID' + Convert(varchar, Sub_id) where Sub_id=@SubId 


Set @OutPut='K00ID' + Convert(varchar, @SubId)
End

3) Normal select Statement ( this will not change value in table)

create function [bdo].[sub_id](@id int)
return Table
as
begin
Select Id, 'K00ID' + Convert(varchar, Sub_Id) [sub_ID] , Name from TableName where Sub_Id=@Id
end

If datatype of column sub_Id is int then this will not work .. just change it to varchar if any.

will rock..........

Hi Mark,

no it is not possible.
User Defined Functions cannot be used to modify base table information. The DML statements INSERT, UPDATE, and DELETE cannot be used on base tables.

Instead of Function you may use Store Procedure Or Trigger
ALTER PROCEDURE [dbo].[sub_id]
@id int,
@newValue varchar(20) output
AS
BEGIN
 set @newValue = 'K00ID'+ cast(@id as varchar)
 insert into test1 values(@newValue)
END
Declare @outputparametersp varchar(20) 
exec [dbo].[sub_id] 1,@outputparametersp output
print @outputparametersp
hope this will help you.

hi,

try...

ALTER FUNCTION [dbo].[sub_id](
@id int
)
RETURNS varchar(12)
AS
BEGIN
return 'K00ID'+ cast(@id as varchar)
END

  
select [dbo].[sub_id](1)

hope this will help you.