December 12, 2014 at 1:24 am
I want to convert this code into User defined function that returns a table, I tried doing it but not able to make it work
DECLARE @columns AS NVARCHAR(MAX);
DECLARE @Pivot AS NVARCHAR(MAX);
select @columns = STUFF((SELECT ',' +
QUOTENAME(ncvReportLabel)
FROM (select distinct iQuestionID,ncvReportLabel,SubHierarchy from dbo.VIEW_NICEEvaluations_New where iFormID = @iFormID ) sub order by SubHierarchy,iQuestionID
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
, 1, 1, '') ;
SELECT @Pivot =
'select * from (
select
iFormID,
iEvalID,
iEvaluatedObjectID,
AgentUserID,
ncvReportLabel,
nvcQuestionAnswer,
EvalUserID,
dtCreationTime,
CallStartTime,
flMaxScore,
flscore / flMaxScore as [Score%],
Batchdate
from
dbo.VIEW_NICEEvaluations_New where iFormID = (' + @iFormID + ' )
) TMP
PIVOT
(
MAX([nvcQuestionAnswer])
FOR [ncvReportLabel] IN (' + @columns + ' )' + '
) as pvt ;';
I got the below error:
Msg 156, Level 15, State 1, Procedure fxnTS_CallmonitoringView, Line 7
Incorrect syntax near the keyword 'DECLARE'.
December 12, 2014 at 5:38 am
Will this work for you? I created a stored procedure instead of a function.
The first part creates a (simplified) sample object and fills it with some data. The last line executes the created stored procedue, so you can see the result. You should offcourse modify the code to fit your environment.
if not object_id('tempdb..#VIEW_NICEEvaluations_New') is null
drop table #VIEW_NICEEvaluations_New
go
create table #VIEW_NICEEvaluations_New
(iQuestionID int
, ncvReportLabel nvarchar(10)
, SubHierarchy int
, iFormID int
, nvcQuestionAnswer int)
insert into #VIEW_NICEEvaluations_New
values (1, 'a', 1, 1, 3)
, (2, 'b', 2, 1, 5)
, (3, 'c', 3, 1, 3)
, (4, 'd', 1, 1, 7)
, (5, 'e', 3, 1, 2)
, (6, 'f', 2, 1, 1)
, (7, 'g', 1, 1, 0)
, (8, 'h', 2, 1, 9)
go
CREATE PROCEDURE [dbo].[usp_Pivot_Evaluation] (@iFormID INT)
AS
BEGIN
DECLARE @columns AS NVARCHAR(MAX);
DECLARE @Pivot AS NVARCHAR(MAX);
select @columns = STUFF((SELECT ',' +
QUOTENAME(ncvReportLabel)
FROM (select distinct iQuestionID,ncvReportLabel,SubHierarchy
from #VIEW_NICEEvaluations_New
where iFormID = @iFormID ) sub
order by SubHierarchy,iQuestionID
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
, 1, 1, '') ;
--select @columns
SELECT @Pivot =
'SELECT *
FROM (
SELECT iFormID
--, iEvalID
--, iEvaluatedObjectID
--, AgentUserID
, ncvReportLabel
, nvcQuestionAnswer
--, EvalUserID
--, dtCreationTime
--, CallStartTime
--, flMaxScore
--, flscore / flMaxScore AS [Score%]
--, Batchdate
FROM #VIEW_NICEEvaluations_New
WHERE iFormID = (' + CAST(@iFormID as varchar(15)) + ')
) TMP
PIVOT
(
MAX([nvcQuestionAnswer])
FOR [ncvReportLabel] IN (' + @columns + ' )' + '
) as pvt ;';
--select @Pivot
exec sp_executesql @pivot
return
END
GO
EXEC usp_Pivot_Evaluation 1
December 12, 2014 at 8:14 am
Thanks for your reply,
i have been created procedure but i want data in select statements, because in front end code we have to pass optinal perameters something, s that i need to put that query into view or function only
December 12, 2014 at 8:32 am
mahi123 (12/12/2014)
Thanks for your reply,i have been created procedure but i want data in select statements, because in front end code we have to pass optinal perameters something, s that i need to put that query into view or function only
You can't use dynamic sql in a view or a function.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 12, 2014 at 9:34 am
mahi123 (12/12/2014)
Thanks for your reply,i have been created procedure but i want data in select statements, because in front end code we have to pass optinal perameters something, s that i need to put that query into view or function only
Front end should be able to receive a result set from a stored procedure.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply