Kindly resolve some error in the below function

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

  • 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’! **
  • 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

  • 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/

  • 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

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

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