Kindly resolve some error in the below function

Question

Kindly resolve some error in the below function

DBA.k

Ten Centuries

Points: 1113
More actions
December 12, 2014 at 1:24 am

#313868

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'.

Viewing 5 posts - 1 through 4 (of 4 total)

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

HanShi SSC-Dedicated Points: 33507 More actions · Answer 1

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

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

DBA.k Ten Centuries Points: 1113 More actions · Answer 2

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

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/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

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.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2