Stored procedure : getting error "Must declare the scalar variable "@id". " when executing procedure

Question

I need some help creating a stored procedure in SQL Server.

This is my code

CREATE PROCEDURE [dbo].[PROC_TABLE_ELEMENTS] 
    @id nvarchar(max)
AS
    DECLARE @sql varchar(max) = '', @col_list varchar(max) = ''

    SET @col_list = (SELECT DISTINCT QOUTENAME(columnName) + ',' 
                     FROM VW_FORM_ELEMENTS_DATA
                     FOR XML PATH(''))

    SET @col_list = LEFT(@col_list, LEN(@col_list) - 1)

    SET @sql = N'SELECT [newId], [modifiedDate], [modifiedBy], [modifiedBy]' + @col_list + 
                ' FROM (SELECT newId, value, columnName, modifiedDate, modifiedBy 
                        FROM VW_FORM_ELEMENTS_DATA 
                        WHERE newId = @id) a
                  PIVOT
                      (MAX([value]) FOR [columnName] IN (' + @col_list + ')) pv'

    EXEC (@sql)

I get this error:

Must declare the scalar variable "@id".

Add `@id` as a parameter to the [`execute`](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/execute-transact-sql?view=sql-server-ver15) statement. Otherwise the value isn't available to the dynamic SQL. — HABO, Jan 29 '21 at 23:16

score 0 · Answer 1 · edited Jan 29 '21 at 21:17

0

This should work in SQL Server.

edited Jan 29 '21 at 21:17

Dale K

25,246
15
42
71

answered Jan 29 '21 at 19:14

Kazi Mohammad Ali Nur Romel

15,301
2
13
26

big thanks. kazi that was really helpful. – Christian Lacuesta Jan 29 '21 at 19:51
there was a problem running it – Christian Lacuesta Jan 29 '21 at 20:03
@ChristianLacuesta you might have accidentally cancelled accepted result. – Kazi Mohammad Ali Nur Romel Jan 29 '21 at 20:11
That's ok. no problem at all. But in screen shot you can see that it worked. best of luck. – Kazi Mohammad Ali Nur Romel Jan 29 '21 at 20:54
1

Please don't use images to display text. For code and results/errors just show the formatted text. Easier for everyone. – Dale K Jan 29 '21 at 21:17

score 0 · Answer 2 · answered Jan 29 '21 at 20:02

this works:

CREATE PROCEDURE [dbo].[PROC_TABLE_ELEMENTS] 
@id nvarchar(max)
AS

declare @sql varchar(max)='',@col_list varchar(max)=''


set @col_list = (select distinct quotename(columnName)+',' from VW_FORM_ELEMENTS_DATA
for xml path(''))

set @col_list = left (@col_list,len(@col_list)-1)


set @sql = 'select [screenId],[newId],[modifiedDate],[modifiedBy],[modifiedBy]'+@col_list+' from
(SELECT screenId, newId, value, columnName, modifiedDate, modifiedBy FROM VW_FORM_ELEMENTS_DATA where screenId = ''' + @id + ''') a
pivot (max([value]) for [columnName] in ('+@col_list+'))pv'

exec (@sql)

this solution will only work if there is only 1 result set – Christian Lacuesta Jan 29 '21 at 22:58 — Christian Lacuesta, Jan 29 '21 at 22:58

Stored procedure : getting error "Must declare the scalar variable "@id". " when executing procedure

2 Answers2