Must declare the scalar variable error

问题:

This is in connection with my last question. I have this code:

DECLARE @PatientId UNIQUEIDENTIFIER
DECLARE @FormId NCHAR(16)
DECLARE @columns VARCHAR(8000)
DECLARE @query VARCHAR(8000)

SET @PatientId = '3387F956-EA0A-4660-8647-1096E25A3C6F'
SET @FormId = 'form111'

SELECT @columns = COALESCE(
        @columns + ',[' + RTRIM(AttributeGroup) + '_' + RTRIM(AttributeId) + ']',
        '[' + RTRIM(AttributeGroup) + '_' + RTRIM(AttributeId) + ']'
    )
FROM DVItems
WHERE PatientId = @PatientId AND FormId = @FormId
GROUP BY AttributeGroup, AttributeId

SET @query = '
SELECT * 
FROM( 
    SELECT
        PatientId,
        RTRIM(AttributeGroup) + ''_'' + RTRIM(AttributeId) AS ColName,
        FormId,
        Description
    FROM DVItems
    WHERE PatientId = @PatientId AND FormId = @FormId
) p
PIVOT
(
    MAX(Description)
    FOR [ColName]
    IN (' + @columns + ')
) AS p'

EXECUTE (@query)

When I execute this. It says "Must declare the scalar variable "@PatientId"." But when I commented out the lines where I SET value for @PatientId and @FormId and execute it, it says "Command(s) completed successfully." Any help? Thank you. Try this fiddle.

回答1:

Try change this statement -

SELECT @query = '
SELECT * 
FROM( 
    SELECT
        PatientId,
        RTRIM(AttributeGroup) + ''_'' + RTRIM(AttributeId) AS ColName,
        FormId,
        Description
    FROM dbo.DVItems
    WHERE PatientId = ''' + CAST(@PatientId AS VARCHAR(36)) + ''' 
        AND FormId = ''' + @FormId + '''
) p
PIVOT
(
    MAX(Description)
    FOR [ColName] IN (' + @columns + ')
) AS p'

Full example -

DECLARE 
      @PatientId UNIQUEIDENTIFIER
    , @FormId NCHAR(16)
    , @columns VARCHAR(MAX)
    , @query NVARCHAR(MAX)

SELECT 
      @PatientId = '3387F956-EA0A-4660-8647-1096E25A3C6F'
    , @FormId = 'form111'

SELECT @columns = STUFF((
    SELECT DISTINCT ', [' + RTRIM(AttributeGroup) + '_' + RTRIM(AttributeId) + ']'
    FROM dbo.DVItems
    WHERE PatientId = @PatientId
        AND FormId = @FormId
    FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 2, '')

SELECT @query = '
SELECT * 
FROM( 
    SELECT
        PatientId,
        RTRIM(AttributeGroup) + ''_'' + RTRIM(AttributeId) AS ColName,
        FormId,
        Description
    FROM dbo.DVItems
    WHERE PatientId = ''' + CAST(@PatientId AS VARCHAR(36)) + ''' 
        AND FormId = ''' + @FormId + '''
) p
PIVOT
(
    MAX(Description)
    FOR [ColName] IN (' + @columns + ')
) AS p'

EXEC sys.sp_executesql @query

Output -

PatientId                            FormId           test_01       test_02      test_03
------------------------------------ ---------------- ------------- ------------ ------------
3387F956-EA0A-4660-8647-1096E25A3C6F form111          Test only     Test only    Test only

回答2:

Like this

SqlCommand comb = new SqlCommand("INSERT INTO [Bolum] ([Id],[BolumId],[FakulteId], [Ad]) VALUES ('"+id+"','"+bolumid+"','"+fakulteid+"','"+txbAd.Text+"')", conb);