CODEWITHSUNDEEP
sqlsql-serverstored-procedures
PigsIncorporated
PigsIncorporated

Reputation: 153

SQL Server stored procedure - parameters

I have a stored procedure that returns a grid result. I'm having some fundamental issues with transferring the @idCC parameter to the SQL query as shown. I know the SQL works if I remove the parameter and hard code in a number value. How do I allow the query to read @idCC to the query shown?

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    ALTER PROCEDURE [dbo].[sp_CC_ContactGroupList] 
        (@idCC int)
    AS
    BEGIN
        DECLARE @ListOfGroups VARCHAR(MAX) =
                (STUFF((SELECT DISTINCT ',' + QUOTENAME(GroupName) 
                        FROM dbo.CC_Groups
                        WHERE IsContactGroup = '1'
                        FOR XML PATH('')), 1, 1, ''));

        DECLARE @sql VARCHAR(MAX) = ('SELECT * 
                                      FROM
                                          (SELECT       
                                               dbo.CC_Contacts.idCC, 
                                               dbo.CC_Groups.GroupName AS gName, 
                                               ''X'' AS IsInGroup
                                           FROM          
                                               dbo.CC_ContactGroups 
                                           INNER JOIN
                                               dbo.CC_Groups ON dbo.CC_ContactGroups.idGroup = dbo.CC_Groups.idGroup 
                                           INNER JOIN
                                               dbo.CC_Contacts ON dbo.CC_ContactGroups.idCC = dbo.CC_Contacts.idCC
                                           WHERE 
                                               dbo.CC_Contacts.idCC = '''+@idCC+''') AS x
                                           PIVOT (MAX(IsInGroup) 
                                               FOR gName IN(' +  @ListOfGroups + ')) as p');

     EXEC(@sql);
END

Upvotes: 0

Views: 49

Answers (1)

Chintan Udeshi
Chintan Udeshi

Reputation: 362

In your stored procedure [dbo].[sp_CC_ContactGroupList], replace 

dbo.CC_Contacts.idCC = '''+@idCC+'''

with

dbo.CC_Contacts.idCC = '''+cast(@idCC as varchar)+'''

Now it will show you @idCC

Upvotes: 1

Related Questions