Workaround for executing dynamic sql in function

  • vpolasa

    Say Hey Kid

    Points: 683

    I'm trying to create a view which dynamically generates text from input parameters. But executing dynamic sql or stored procedure is not allowed in functions. Is there a work around for this?

    Below is sample script for: 1. Create tables, 2.Create function, 3.Create view, 4. Expected output

     ----------------TABLES-------------------------
    IF OBJECT_ID('dbo.emp_info', 'U') IS NOT NULL
    DROP TABLE dbo.emp_info;
    CREATE TABLE emp_info (emp_key INT, table_key INT, emp_fname NVARCHAR(100), emp_lname NVARCHAR(100), city_name NVARCHAR(100), city_zip BIGINT)
    INSERT INTO emp_info (emp_key, table_key, emp_fname, emp_lname, city_name, city_zip) VALUES (1, 3, 'Brad', 'Pitt', 'San Jose', 95670)
    INSERT INTO emp_info (emp_key, table_key, emp_fname, emp_lname, city_name, city_zip) VALUES (2, 3, 'Will', 'Smith', 'Kansas', 65870)

    IF OBJECT_ID('dbo.table_list', 'U') IS NOT NULL
    DROP TABLE dbo.table_list;
    CREATE TABLE table_list (table_key int, table_name NVARCHAR(100), table_template_id NVARCHAR(100))
    INSERT INTO table_list (table_key, table_name, table_template_id) VALUES (3, 'emp_info', '[emp_lname],[emp_fname]-[empkey]')
    ----------------TABLES-------------------------





    ----------------FUNCTION-------------------------
    IF OBJECT_ID(N'crt_emp_temp_id', N'FN') IS NOT NULL
    DROP FUNCTION crt_emp_temp_id
    GO
    CREATE FUNCTION crt_emp_temp_id (@table_key INT, @emp_key INT)
    RETURNS NVARCHAR(1000)
    AS
    BEGIN

    DECLARE @mi_table_id_tmp TABLE (
    table_key BIGINT,
    table_fld NVARCHAR(100),
    fld_order BIGINT
    )

    DECLARE @table_template_id NVARCHAR(100), @table_fld NVARCHAR(100), @table_flds NVARCHAR(100),
    @count INT = 0, @max_fld_order INT, @table_name NVARCHAR(100),
    @fld_order INT, @sql_stmt NVARCHAR(MAX), @out_value NVARCHAR(1000)

    SELECT @table_template_id = table_template_id
    ,@table_name = table_name
    FROM table_list
    WHERE table_key = @table_key

    SET @table_flds = @table_template_id

    WHILE (LEN(@table_flds) > 0)
    BEGIN
    SET @table_fld = SUBSTRING(@table_flds, CHARINDEX('[', @table_flds)+1, CHARINDEX(']', @table_flds)-2)
    SET @table_flds = SUBSTRING(@table_flds, CHARINDEX(@table_fld, @table_flds)+LEN(@table_fld)+1, LEN(@table_flds));
    SET @table_flds = SUBSTRING(@table_flds, CHARINDEX('[', @table_flds ), LEN(@table_flds))

    SET @count = @count + 1;
    INSERT INTO @mi_table_id_tmp (table_key, table_fld, fld_order) VALUES (@table_key, @table_fld, @count);
    END

    SELECT @max_fld_order = MAX(fld_order) FROM @mi_table_id_tmp

    SET @count = 1

    WHILE (@count <= @max_fld_order)
    BEGIN
    SELECT top(1) @table_fld = table_fld
    FROM @mi_table_id_tmp
    WHERE fld_order = @count

    IF (@count = 1)
    SET @table_template_id = REPLACE(@table_template_id, '['+@table_fld+']', @table_fld+'+''')
    ELSE IF (@count = @max_fld_order)
    SET @table_template_id = REPLACE(@table_template_id, '['+@table_fld+']', '''+'+@table_fld);
    ELSE
    SET @table_template_id = REPLACE(@table_template_id, '['+@table_fld+']', '''+'+@table_fld+'+''');

    SET @count = @count + 1
    END

    SET @sql_stmt = 'select @out = '+@table_template_id+' from '+@table_name+' where emp_key = @e_k'

    ------------This is where I'm stuck-------------------
    EXECUTE sp_executesql @sql_stmt, N'@e_k int,@out NVARCHAR(1000) OUTPUT', @e_k = @emp_key, @out = @out_value OUTPUT
    RETURN @out_value
    ------------This is where I'm stuck-------------------
    END
    GO
    ----------------FUNCTION-------------------------







    ----------------VIEW-------------------------
    IF object_id(N'emp_info_vw', N'V') IS NOT NULL
    DROP FUNCTION emp_info_vw
    GO
    CREATE VIEW emp_info_vw AS
    SELECT crt_emp_temp_id (table_key, emp_key) emp_id,
    city_name,
    city_zip
    FROM emp_info
    ----------------VIEW-------------------------

    /*
    I'd like to have my output of the view as below
    Pitt,Brad-1 San Jose 95670
    Smith,Will-2 Kansas 65870
    */
    GO
  • scdecade

    Say Hey Kid

    Points: 693

    select
    concat(emp_lname, ',', emp_fname, '-', emp_key) lf_name,
    city_name,
    city_zip
    from
    emp_info;

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • This was removed by the editor as SPAM

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply