February 14, 2020 at 9:03 pm
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
February 15, 2020 at 1:19 pm
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
February 20, 2020 at 10:59 am
This was removed by the editor as SPAM
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply