Click here to Skip to main content
14,880,336 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello,

SQL
DECLARE @AssignedGroupIDs AS NVARCHAR(4000)

    SELECT
            @AssignedGroupIDs=  COALESCE(CONVERT(NVARCHAR, '1470, 1471, 1472, 1474, 1475, 1477, 1478, 1480') + ', ', '') + Convert(NVARCHAR,tbGroup.GroupID )
        FROM
            tbGroup,tbAssignedGroups
        WHERE
         tbGroup.GroupID = tbAssignedGroups.GroupID
            AND tbAssignedGroups.UserID = 2
            AND tbGroup.IsDeleted = 0

        select @AssignedGroupIDs




My output:

1470, 1471, 1472, 1474, 1475, , 1480


actual output must be:
1470, 1471, 1472, 1474, 1475, 1477, 1478, 1480


why output is not getting right ids.1477,1478 this ids skip why
i dont kw please how can i get right ids




Regrads
Sadhana.R.Belge
Posted

1 solution

I can see several issues in this code, but I'll just deal with the question at hand:

For a start, NVARCHAR(4000) has not been the standard since SQL 2000 (maybe 2005). Use NVARCHAR(MAX) instead. It is variable length so is more efficient for strings of unknown length.

Without (4000) or (MAX) the default for NVARCHAR is (30). Hence CONVERT(NVARCHAR, '1470, 1471, 1472, 1474, 1475, 1477, 1478, 1480') = '1470, 1471, 1472, 1474, 1475, '

set the size of the NVARCHAR to fix the issue, but it is already a string so you don't even need to convert it.


Also: Coalesce is used to ignore null values. Your code excludes the possibility of nulls so this is pointless

This is what your code should look like to do what it is written to do:
SQL
DECLARE @AssignedGroupIDs AS NVARCHAR(4000)
 
    SELECT
            @AssignedGroupIDs=  '1470, 1471, 1472, 1474, 1475, 1477, 1478, 1480, ' + Convert(NVARCHAR,tbGroup.GroupID )
        FROM
            tbGroup,tbAssignedGroups
        WHERE
         tbGroup.GroupID = tbAssignedGroups.GroupID
            AND tbAssignedGroups.UserID = 2
            AND tbGroup.IsDeleted = 0
 
        select @AssignedGroupIDs


Perhaps this is not what you intended, but it's what you wrote.

If you need help using COALESCE then let mw know and I'll help you put together what you need.

Hope that helps ^_^
Andy
   
Comments
[no name] 11-Sep-15 5:29am
   
Helped me a lot, thanks and 5.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900