CODEWITHSUNDEEP
sqlsql-serverstringparameter-passingstuff
jackstraw22
jackstraw22

Reputation: 641

Need to add double quotes to SQL SERVER string values

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?

Upvotes: 0

Views: 1509

Answers (2)

sacse
sacse

Reputation: 3872

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, ''))

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271231

I would suggest:

select string_agg(concat('"', right('000' + cast(ctl_num as varchar(255)), 7), '"', ',')
from @t;

string_agg() has been available since SQL Server 2017. Also note that you should always include a length when referring to strings in SQL Server.

Upvotes: 0

Related Questions