question

taj avatar image
taj asked

Select Query in Function as per user requirement in SQL Server

**user defined function** alter function USF_GET_CONFIG(@p_config varchar(4000), @p_valType varchar(4000)) returns nvarchar(max) AS BEGIN DECLARE @config_value nvarchar(500)=''; set @config_value = (select @p_valType from dev_common.dbo.ok where id=@p_config) RETURN @config_value END; **calling function** select dbo.USF_GET_CONFIG(10,'id') select dbo.USF_GET_CONFIG(10,'name') **select query within function work like below** select id from dev_common.dbo.ok where id=10 select name from dev_common.dbo.ok where id=10 **here *id* and *name* are field names of *table ok*** **is it possible in udf**
sqlserver 2008
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This web site runs based on your votes. Please indicate all the helpful answers below by clicking on the thumbs up next to them. If any one answer lead to a solution, please indicate this by clicking on the check mark next to that answer.
0 Likes 0 ·
@SQLShark avatar image
@SQLShark answered
You could use some dynamic TSQL. If you do, you should read the "Curse and Blessing of Dynamic SQL" [ http://www.sommarskog.se/dynamic_sql.html][1] DROP TABLE #CONFIG CREATE TABLE #Config ( id INT , [name] VARCHAR(50) , [type] VARCHAR(10) ) INSERT INTO #Config ( id, name, type ) VALUES ( 1, 'Table', 'Database' ) , ( 2, 'Object', 'Server' ) DECLARE @ID VARCHAR(10) = '2' DECLARE @Item VARCHAR(50) = 'name' DECLARE @DynamicTSQL VARCHAR(2000) = 'SELECT top 1 [' + @Item + '] FROM #CONFIG WHERE ID = ''' + @ID + ''''; EXEC (@DynamicTSQL) [1]: http://www.sommarskog.se/dynamic_sql.html
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

but i have to write function. and function does not support dynamic sql. then what should i do
0 Likes 0 ·
Ok, As you need a UDF an alternative would be to unpivot the table and query the columns as rows. I will write an example in a few minutes.
0 Likes 0 ·
@SQLShark avatar image
@SQLShark answered
Example of using Unpivot. *Edited* to include the function DROP TABLE Config GO CREATE TABLE Config ( [ID] INT , [ConfigItem1] VARCHAR(50) , [ConfigItem2] VARCHAR(50) , [ConfigItem3] VARCHAR(50) ); INSERT INTO Config ( ID, ConfigItem1, ConfigItem2, ConfigItem3 ) VALUES ( 1, 'C:', '100', '700' ) , ( 2, 'D:', '110', '800' ) , ( 3, 'E:', '120', '900' ) GO /*####################################*/ ALTER FUNCTION USF_GET_CONFIG ( @p_config INT , @p_valType VARCHAR(50) ) RETURNS @Config TABLE ( ConfigItem VARCHAR(50) ) AS BEGIN ; WITH Configs AS ( SELECT ID , Config_item , Config_Result FROM Config UNPIVOT ( Config_Result FOR Config_item IN ( ConfigItem1, ConfigItem2, ConfigItem3 ) ) u ) INSERT INTO @Config ( ConfigItem ) SELECT Config_Result FROM Configs WHERE Config_item = @p_valType AND id = @p_config RETURN END SELECT * FROM dbo.USF_GET_CONFIG(1, 'ConfigItem1')
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.