Tuesday 6 September 2016

SQl server-Dynamic query for extracting data pass parameter as table,col,condition,id





create function udf_GetActorname(@ID varchar(10),@Colname varchar(50),@Con_Col varchar(100),@tabName varchar(100))
 returns varchar(100)
 As
 begin
 declare @QueryResult varchar(max)
 set  @QueryResult='select '+@Colname+' from '+@tabName+' where '+@Con_Col+'='+cast(@ID as varchar(10))+''
 return @QueryResult
 end
 declare @id varchar(10)
 declare @Colname varchar(50)
 declare @Con_Col varchar(50)
 declare @tabName varchar(50)
  set @id='1'
  set @Colname='ActorName'
  set @Con_Col='actorid'
  set @tabName='actor'
  
  declare @QueryResult nvarchar(max)
  --set @QueryResult =dbo.udf_GetActorname(@id,@Colname,@Con_Col,@tabName)
  set @QueryResult =dbo.udf_GetActorName('1','ActorName','Actorid','Actor')
  print @QueryResult
  exec sp_executesql @QueryResult
  select ActorName from actor where actorid=1

No comments:

Post a Comment