How many quotation marks are needed to surround a variable in a dynamic SQL statement. I may be dealing with a type issue but I don't think so. If I run the following sql (this is a reduced version but the structure is the same), I get the following error: The thread '(local) ' (0x1254) has exited with code 0 (0x0). The field "Processed" is a type "tinyint". Also, no matter where I try to call @p I get the same error. For instance if I remove the sub-query and add it as a field anywhere in the query it chokes as well.
DECLARE @p tinyint
SET @p = 1
SELECT @SQL2 = '
INSERT INTO Table1(
Field1, Field2,' + @Cols +'
SELECT Field1, Field2 = ‘’ + @p + ’’, ' + @Cols +'
FROM Table1 A JOIN (SELECT fieldStart = DATEADD(day,7,[PeriodStart]), FROM Table1 WHERE Processed = '' + @p + '') C ON A.Id = C.Id