CODEWITHSUNDEEP
sql-server-2008t-sqldynamic-sql
codingManiac
codingManiac

Reputation: 1700

Invalid Column Name NA

I am trying to get the following insert statement to work but I have had no luck. I've researched and tried many things but haven't found a solution. Is there anyway to assign the literal string NA to the column?

qryALL (@pPK BIGINT,@pAL FLOAT,@pFK BIGINT)
AS
DECLARE @query  AS NVARCHAR(MAX)

SET @query = 'INSERT INTO tblTPF ( fldFPK, fldPK, fldDTA, fldCI, fldMD, fldME, 
fldIQ, fldSD, fldND, fldNI, fldNL, fldMI, fldMA, fldUN, fldAV )
SELECT '+ Convert(varchar,@pFK) +' AS sFPK, '+ Convert(varchar,@pPK) +' AS sPK, 
GETDATE() AS sDate, '+ Convert(varchar,@pAL) +' AS sAL, -254 AS sMD, -254 AS sME,
-254 AS sIQ, -254 AS sSD, 0 AS sND, 0 AS sNI, 0 AS sNL, -254 AS sMI, -254 AS sMA, 
NA AS sUN, 0 AS sAV;'

execute sp_executesql @query

Upvotes: 0

Views: 352

Answers (1)

i-one
i-one

Reputation: 5120

Literal strings should be enclosed into single quotation marks, e.g. select 'NA' as sUN, and literal string inside string should be enclosed into doubled single quotation marks:

SET @query = 'INSERT INTO tblTPF ( fldFPK, fldPK, fldDTA, fldCI, fldMD, fldME, 
fldIQ, fldSD, fldND, fldNI, fldNL, fldMI, fldMA, fldUN, fldAV )
SELECT '+ Convert(varchar,@pFK) +' AS sFPK, '+ Convert(varchar,@pPK) +' AS sPK, 
GETDATE() AS sDate, '+ Convert(varchar,@pAL) +' AS sAL, -254 AS sMD, -254 AS sME,
-254 AS sIQ, -254 AS sSD, 0 AS sND, 0 AS sNI, 0 AS sNL, -254 AS sMI, -254 AS sMA, 
''NA'' AS sUN, 0 AS sAV;'

Upvotes: 1

Related Questions