Sql Server-- Function

Question

Hi I have one table that having 3columns like.. id sub_id name 1 1 mark I wrote below code to implement function on sub_id column of table that user can see only Sub_id like K00ID1. create function [bdo].[sub_id](@id int) return char(12) as begin return 'K00ID'+id end I want like this my table. id sub_id name 1 K00ID1 mark i tried above function but it is not working... can u help me to sort out this problem

Jignesh Trivedi · Answer

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.

Dinesh Bhoje · Answer

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..........

Jignesh Trivedi · Answer

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.

Gurjeet Singh · Answer

Hi Jignesh It is working fine but i want to store same value also in table column.Is it possible to store value..in column?