Need to add double quotes to SQL SERVER string values

Question

I have integer values that are being passed from a parameter that needed to be input as a string and padded so that they are 7 digits. This will then be passed into another query.

   declare @t table(ctl_num int)
   insert into @t values(5675, 45464, 2323)

   declare @control_num varchar(500)

   set @control_num = (select stuff((select ',' + right('000' + cast(ctl_num as varchar),7)
                      from @t
                      FOR XML PATH('')),1, 1', ''))

This codes sets @control_num as ('0005675, 0045464, 0002323'). I need this to be passed as ("0005675", "0045464", "0002323").

I've looked at other examples on-line but I can't seem to get this to work. Does anyone know how to get the double quotes around each value?

Answer 1

I think there is some issue in setting @control_num.

Please try the following:

set @control_num = (select stuff((select ',"' + right('000' + cast(ctl_num as varchar),7) + '"'
                      from @t
                      FOR XML PATH('')),1, 1, ''))