Select Query in Function as per user requirement in SQL Server

Question

question

taj asked Feb 10, '15

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

10 |1200

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

Grant Fritchey ♦♦ commented · Feb 16, 2015 at 10:46 AM

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 ·

Answer 1 · 2015-02-10T08:50:34Z

@SQLShark answered Feb 10, '15

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

10 |1200

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

taj commented · Feb 10, 2015 at 09:02 AM

but i have to write function. and function does not support dynamic sql. then what should i do

0 ·

Kev Riley ♦♦ commented · Feb 10, 2015 at 09:22 AM

Same question here : https://ask.sqlservercentral.com/questions/117974/how-to-assign-stored-procedure-having-dynamic-quer.html

0 ·

@SQLShark commented · Feb 10, 2015 at 10:23 AM

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 ·

Answer 2 · 2015-02-10T10:37:00Z

@SQLShark answered Feb 10, '15

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

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

question