Invalid Parameter Binding - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Invalid Parameter Binding

Author

Topic

bhavya.g22
Starting Member

2 Posts

Posted - 2014-11-11 : 04:16:49

Hi,

While trying to execute the below query I get an error "Invalid Paramter Binding. Please help me in fixing.

SELECT RELEASE_CYCLES.RCYC_NAME+' : '+ CYCL_FOLD.CF_ITEM_NAME /*Test Set Folder (CYCLE).Name*/ as "Cycle Name : Test Component",
SUM(case when t3.status='Passed' then 1 else 0 end) "Passed",
SUM(case when t3.status='Failed' then 1 else 0 end) "Failed",
SUM(case when t3.status='Not Completed' then 1 else 0 end) "Not Completed",
SUM(case when t3.status='No Run' then 1 else 0 end) "No Run",
SUM(case when t3.status in ('Passed','Failed','Not Completed','No Run') then 1 else 0 end) "Total",
convert(varchar(10),100*SUM(case when t3.status = 'Passed' then 1 else 0 end)/SUM(case when t3.status in ('Passed','Failed','Not Completed','No Run') then 1 else 0 end))+'%' "Passed %",
convert(varchar(10),100*SUM(case when t3.status in ('Passed','Failed') then 1 else 0 end)/SUM(case when t3.status in ('Passed','Failed','Not Completed','No Run') then 1 else 0 end))+'%' "Execution %",
NULL as "Expected Execution %",
NULL,
convert(varchar(10),RELEASE_CYCLES.RCYC_START_DATE /*Cycle.Start Date*/,120)"Start Date", convert(varchar(10),RELEASE_CYCLES.RCYC_END_DATE /*Cycle.End Date*/,120) "End Date"
FROM(SELECT TESTCYCL.TC_CYCLE_ID /*Test Instance.TestSet*/ as cycleid, TESTCYCL.TC_STATUS /*Test Instance.Status*/ as status, TESTCYCL.TC_ASSIGN_RCYC /*Test Instance.Target Cycle*/ as cycle, CYCLE.CY_FOLDER_ID /*Test Set.Test Set Folder*/ as folderid from TESTCYCL /*Test Instance*/ , CYCLE /*Test Set*/
where CYCLE.CY_CYCLE_ID= TESTCYCL.TC_CYCLE_ID /*Test Instance.TestSet*/ and TESTCYCL.TC_STATUS /*Test Instance.Status*/ in ('No Run','Passed','Failed','Not Completed') and TESTCYCL.TC_ASSIGN_RCYC /*Test Instance.Target Cycle*/ in ( select RELEASE_CYCLES.RCYC_ID /*Cycle.Cycle ID*/ from RELEASE_CYCLES /*Cycle*/ where RELEASE_CYCLES.RCYC_PARENT_ID /*Cycle.Release ID*/ in ( @ReleaseID@ )))t3, RELEASE_CYCLES /*Cycle*/ , CYCLE /*Test Set*/ , CYCL_FOLD /*Test Set Folder (CYCLE)*/
where RELEASE_CYCLES.RCYC_ID /*Cycle.Cycle ID*/ =t3.cycle
and t3.cycleid=CYCLE.CY_CYCLE_ID /*Test Set.Cycle ID*/
//and t3.cycle=CYCL_FOLD.CF_ASSIGN_RCYC /*Test Set Folder (CYCLE).Target Cycle*/
and t3.folderid= CYCL_FOLD.CF_ITEM_ID /*Test Set Folder (CYCLE).Test Set Folder ID*/
group by RELEASE_CYCLES.RCYC_NAME /*Cycle.Name*/ ,RELEASE_CYCLES.RCYC_START_DATE /*Cycle.Start Date*/, RELEASE_CYCLES.RCYC_END_DATE /*Cycle.End Date*/, CYCL_FOLD.CF_ITEM_NAME /*Test Set Folder (CYCLE).Name*/
//order by RELEASE_CYCLES.RCYC_END_DATE asc
UNION ALL
select 'Total Test Case Metrics',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL

Thanks!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-11 : 09:02:53

Your code is all but impossible to read as posted. Here it is reformatted (poorsql.com):


SELECT RELEASE_CYCLES.RCYC_NAME + ' : ' + CYCL_FOLD.CF_ITEM_NAME /*Test Set Folder (CYCLE).Name*/ AS "Cycle Name : Test Component"
    , SUM(CASE 
            WHEN t3.STATUS = 'Passed'
                THEN 1
            ELSE 0
            END) "Passed"
    , SUM(CASE 
            WHEN t3.STATUS = 'Failed'
                THEN 1
            ELSE 0
            END) "Failed"
    , SUM(CASE 
            WHEN t3.STATUS = 'Not Completed'
                THEN 1
            ELSE 0
            END) "Not Completed"
    , SUM(CASE 
            WHEN t3.STATUS = 'No Run'
                THEN 1
            ELSE 0
            END) "No Run"
    , SUM(CASE 
            WHEN t3.STATUS IN (
                    'Passed'
                    , 'Failed'
                    , 'Not Completed'
                    , 'No Run'
                    )
                THEN 1
            ELSE 0
            END) "Total"
    , convert(VARCHAR(10), 100 * SUM(CASE 
                WHEN t3.STATUS = 'Passed'
                    THEN 1
                ELSE 0
                END) / SUM(CASE 
                WHEN t3.STATUS IN (
                        'Passed'
                        , 'Failed'
                        , 'Not Completed'
                        , 'No Run'
                        )
                    THEN 1
                ELSE 0
                END)) + '%' "Passed %"
    , convert(VARCHAR(10), 100 * SUM(CASE 
                WHEN t3.STATUS IN (
                        'Passed'
                        , 'Failed'
                        )
                    THEN 1
                ELSE 0
                END) / SUM(CASE 
                WHEN t3.STATUS IN (
                        'Passed'
                        , 'Failed'
                        , 'Not Completed'
                        , 'No Run'
                        )
                    THEN 1
                ELSE 0
                END)) + '%' "Execution %"
    , NULL AS "Expected Execution %"
    , NULL
    , convert(VARCHAR(10), RELEASE_CYCLES.RCYC_START_DATE /*Cycle.Start Date*/, 120) "Start Date"
    , convert(VARCHAR(10), RELEASE_CYCLES.RCYC_END_DATE /*Cycle.End Date*/, 120) "End Date"
FROM (
    SELECT TESTCYCL.TC_CYCLE_ID /*Test Instance.TestSet*/ AS cycleid
        , TESTCYCL.TC_STATUS /*Test Instance.Status*/ AS STATUS
        , TESTCYCL.TC_ASSIGN_RCYC /*Test Instance.Target Cycle*/ AS cycle
        , CYCLE.CY_FOLDER_ID /*Test Set.Test Set Folder*/ AS folderid
    FROM TESTCYCL /*Test Instance*/
        , CYCLE /*Test Set*/
    WHERE CYCLE.CY_CYCLE_ID = TESTCYCL.TC_CYCLE_ID /*Test Instance.TestSet*/
        AND TESTCYCL.TC_STATUS /*Test Instance.Status*/ IN (
            'No Run'
            , 'Passed'
            , 'Failed'
            , 'Not Completed'
            )
        AND TESTCYCL.TC_ASSIGN_RCYC /*Test Instance.Target Cycle*/ IN (
            SELECT RELEASE_CYCLES.RCYC_ID /*Cycle.Cycle ID*/
            FROM RELEASE_CYCLES /*Cycle*/
            WHERE RELEASE_CYCLES.RCYC_PARENT_ID /*Cycle.Release ID*/ IN (@ReleaseID@)
            )
    ) t3
    , RELEASE_CYCLES /*Cycle*/
    , CYCLE /*Test Set*/
    , CYCL_FOLD /*Test Set Folder (CYCLE)*/
WHERE RELEASE_CYCLES.RCYC_ID /*Cycle.Cycle ID*/ = t3.cycle
    AND t3.cycleid = CYCLE.CY_CYCLE_ID /*Test Set.Cycle ID*/
    //and t3.cycle=CYCL_FOLD.CF_ASSIGN_RCYC /*Test Set Folder (CYCLE).Target Cycle*/
    AND t3.folderid = CYCL_FOLD.CF_ITEM_ID /*Test Set Folder (CYCLE).Test Set Folder ID*/
GROUP BY RELEASE_CYCLES.RCYC_NAME /*Cycle.Name*/
    , RELEASE_CYCLES.RCYC_START_DATE /*Cycle.Start Date*/
    , RELEASE_CYCLES.RCYC_END_DATE /*Cycle.End Date*/
    , CYCL_FOLD.CF_ITEM_NAME /*Test Set Folder (CYCLE).Name*/
//order by RELEASE_CYCLES.RCYC_END_DATE asc

UNION ALL

SELECT 'Total Test Case Metrics'
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL;

about half-way down, you can see this line:


WHERE RELEASE_CYCLES.RCYC_PARENT_ID /*Cycle.Release ID*/ IN (@ReleaseID@)

which has an extra @ on the end. Remove that and see if you get past your error.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-11 : 09:16:26

Oh I forgot to mention, you have double slashes in a couple of places. Those are invalid

bhavya.g22
Starting Member

2 Posts

Posted - 2014-11-12 : 03:37:50

Thanks Britton! I tried the below but it still does not work. Can you please resolve this for me?
SELECT RELEASE_CYCLES.RCYC_NAME + ' : ' + CYCL_FOLD.CF_ITEM_NAME /*Test Set Folder (CYCLE).Name*/ AS "Cycle Name : Test Component"
, SUM(CASE
WHEN t3.STATUS = 'Passed'
THEN 1
ELSE 0
END) "Passed"
, SUM(CASE
WHEN t3.STATUS = 'Failed'
THEN 1
ELSE 0
END) "Failed"
, SUM(CASE
WHEN t3.STATUS = 'Not Completed'
THEN 1
ELSE 0
END) "Not Completed"
, SUM(CASE
WHEN t3.STATUS = 'No Run'
THEN 1
ELSE 0
END) "No Run"
, SUM(CASE
WHEN t3.STATUS IN (
'Passed'
, 'Failed'
, 'Not Completed'
, 'No Run'
)
THEN 1
ELSE 0
END) "Total"
, convert (VARCHAR(10), 100) * SUM(CASE
WHEN t3.STATUS = 'Passed'
THEN 1
ELSE 0
END) / SUM(CASE
WHEN t3.STATUS IN (
'Passed'
, 'Failed'
, 'Not Completed'
, 'No Run'
)
THEN 1
ELSE 0
END)) + '%' "Passed %"
, convert (VARCHAR(10), 100) * SUM(CASE
WHEN t3.STATUS IN (
'Passed'
, 'Failed'
)
THEN 1
ELSE 0
END) / SUM(CASE
WHEN t3.STATUS IN (
'Passed'
, 'Failed'
, 'Not Completed'
, 'No Run'
)
THEN 1
ELSE 0
END)) + '%' "Execution %"
, NULL AS "Expected Execution %"
, NULL
, convert(VARCHAR(10), RELEASE_CYCLES.RCYC_START_DATE /*Cycle.Start Date*/, 120) "Start Date"
, convert(VARCHAR(10), RELEASE_CYCLES.RCYC_END_DATE /*Cycle.End Date*/, 120) "End Date"
FROM (
SELECT TESTCYCL.TC_CYCLE_ID /*Test Instance.TestSet*/ AS cycleid
, TESTCYCL.TC_STATUS /*Test Instance.Status*/ AS STATUS
, TESTCYCL.TC_ASSIGN_RCYC /*Test Instance.Target Cycle*/ AS cycle
, CYCLE.CY_FOLDER_ID /*Test Set.Test Set Folder*/ AS folderid
FROM TESTCYCL /*Test Instance*/
, CYCLE /*Test Set*/
WHERE CYCLE.CY_CYCLE_ID = TESTCYCL.TC_CYCLE_ID /*Test Instance.TestSet*/
AND TESTCYCL.TC_STATUS /*Test Instance.Status*/ IN (
'No Run'
, 'Passed'
, 'Failed'
, 'Not Completed'
)
AND TESTCYCL.TC_ASSIGN_RCYC /*Test Instance.Target Cycle*/ IN (
SELECT RELEASE_CYCLES.RCYC_ID /*Cycle.Cycle ID*/
FROM RELEASE_CYCLES /*Cycle*/
WHERE RELEASE_CYCLES.RCYC_PARENT_ID /*Cycle.Release ID*/ IN (@ReleaseID)
)
) t3
, RELEASE_CYCLES /*Cycle*/
, CYCLE /*Test Set*/
, CYCL_FOLD /*Test Set Folder (CYCLE)*/
WHERE RELEASE_CYCLES.RCYC_ID /*Cycle.Cycle ID*/ = t3.cycle
AND t3.cycleid = CYCLE.CY_CYCLE_ID /*Test Set.Cycle ID*/
ANd t3.cycle=CYCL_FOLD.CF_ASSIGN_RCYC /*Test Set Folder (CYCLE).Target Cycle*/
AND t3.folderid = CYCL_FOLD.CF_ITEM_ID /*Test Set Folder (CYCLE).Test Set Folder ID*/
GROUP BY RELEASE_CYCLES.RCYC_NAME /*Cycle.Name*/
, RELEASE_CYCLES.RCYC_START_DATE /*Cycle.Start Date*/
, RELEASE_CYCLES.RCYC_END_DATE /*Cycle.End Date*/
, CYCL_FOLD.CF_ITEM_NAME /*Test Set Folder (CYCLE).Name*/
order by RELEASE_CYCLES.RCYC_END_DATE asc

UNION ALL

SELECT 'Total Test Case Metrics'
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL;

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-12 : 07:06:00

What is the error message?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-12 : 09:07:18

There are still many problems with your syntax. For one thing, your parentheses are not balanced. Spend sometime counting your parentheses to make sure they are balanced. e.g.


convert(VARCHAR(10), 100) * SUM(CASE 
               WHEN t3.STATUS = 'Passed'
                    THEN 1
               ELSE 0
               END) / SUM(CASE 
               WHEN t3.STATUS IN (
                         'Passed'
                         , 'Failed'
                         , 'Not Completed'
                         , 'No Run'
                         )
                    THEN 1
               ELSE 0
               END) ) + '%' "Passed %"  --<<<-- extra closing parenthesis after END).

For another, in the above snipped, why are you converting 100 to a varchar and THEN trying to multiply the result by a SUM? Also, it is better form to use the keyword AS before your aliases. Again from the above, this is better:

[code]
+ '%' AS "Passed %" --<<<<-- use keyword AS before the alias.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources