MS SQL Server 2008 - Proper syntax for a @Sql par...

Ok this is the complete code:
USE [MVP_Demo]
GO
/****** Object:  StoredProcedure [dbo].[TEMP_PWC_VALIDATIONSP]    Script Date: 01/04/2013 10:04:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            
-- Create date: 11/15/2012
-- Description:      Creates a temporary table for Validations
-- =============================================
ALTER PROCEDURE [dbo].[TEMP_PWC_VALIDATIONSP]
AS
BEGIN
IF  EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[TEMP_PWC_VALTEMP]') AND type in (N'U'))
DROP TABLE [dbo].[TEMP_PWC_VALTEMP]

CREATE TABLE [dbo].[TEMP_PWC_VALTEMP]
(
 mpropertynumber varchar(255),
 Valnum varchar(255),
 )

DECLARE @sql nvarchar(4000)
DECLARE @Valnum nvarchar(4000)
DECLARE @Valid1 nvarchar(4000)
DECLARE @Valid2 nvarchar(4000)

DECLARE main_cursor CURSOR FOR
SELECT Valid1, Valid2, Valnum FROM dbo.TEMP_PWC_ValTable
Where CAST(Active as varchar(10)) = '1'
--Where CAST(Valnum as varchar(10)) in ('1', '25')

OPEN main_cursor
SET @sql = ''
SET @Valnum = ''
SET @Valid1 = ''
SET @Valid2 = ''
-- Perform the first fetch.
FETCH NEXT FROM main_cursor
INTO @Valid1, @Valid2, @Valnum


-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
      IF @sql = ''
      BEGIN

            IF @Valid2 = ''
                  SET @sql = 'SELECT mpropertynumber, ' + @Valnum + ' from [TEMP_PWC_Report_SP_MAIN]' Join '[TEMP_PWC_Report_SP_CHARACTERISTICS]' ON '[TEMP_PWC_Report_SP_CHARACTERISTICS].[mpropertynumber] = [TEMP_PWC_Report_SP_MAIN].[mpropertynumber] where' + @Valid1
            ELSE
                  SET @sql = 'SELECT mpropertynumber, ' + @Valnum + ' from [TEMP_PWC_Report_SP_MAIN]' Join '[TEMP_PWC_Report_SP_CHARACTERISTICS]' ON '[TEMP_PWC_Report_SP_CHARACTERISTICS].[mpropertynumber] = [TEMP_PWC_Report_SP_MAIN].[mpropertynumber] where ' + @Valid1 + ' and ' + @Valid2
      END
      ELSE
      BEGIN
            IF @Valid2 = ''
                          SET @sql = @sql +' union ' + 'SELECT mpropertynumber, ' + @Valnum + ' from [TEMP_PWC_Report_SP_MAIN]' Join '[TEMP_PWC_Report_SP_CHARACTERISTICS]' ON '[TEMP_PWC_Report_SP_CHARACTERISTICS].[mpropertynumber] = [TEMP_PWC_Report_SP_MAIN].[mpropertynumber] where ' + @Valid1
            ELSE
                    SET @sql = @sql +' union ' + 'SELECT mpropertynumber, ' + @Valnum + ' from [TEMP_PWC_Report_SP_MAIN]' Join '[TEMP_PWC_Report_SP_CHARACTERISTICS]' ON '[TEMP_PWC_Report_SP_CHARACTERISTICS].[mpropertynumber] = [TEMP_PWC_Report_SP_MAIN].[mpropertynumber] where ' + @Valid1 + ' and ' + @Valid2
      END
   

   -- This is executed as long as the previous fetch succeeds.
   FETCH NEXT FROM main_cursor
      INTO @Valid1, @Valid2, @Valnum
     
     
END

CLOSE main_cursor
DEALLOCATE main_cursor

   INSERT [TEMP_PWC_VALTEMP] EXEC sp_executesql @sql
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

END


As you can see I am working on the Join section of the @Sql and again this is not correct.

the fields Valid1 and Valid2 are both functions that check table(s) for conditions example:
Valid1:
[TEMP_PWC_Report_SP_MAIN].[mclassificationid] = '011'

Valid2:
[TEMP_PWC_Report_SP_CHARACTERISTICS].[ExteriorWallCodeCode] Not Between '00' And '72'

What this does is checks the Main table for id to = 011
And
Checks Characteristics for exteriorwallcodecode not between 00 and 72

If both of the conditions are true then a record is added to the Table (ValTemp) using fields: propertyNumbers, and Valnum.


Thanks

The keywords JOIN and ON have to be inside the single quotes


ALTER PROCEDURE [dbo].[TEMP_PWC_VALIDATIONSP]
AS
BEGIN
IF  EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[TEMP_PWC_VALTEMP]') AND type in (N'U'))
DROP TABLE [dbo].[TEMP_PWC_VALTEMP]

CREATE TABLE [dbo].[TEMP_PWC_VALTEMP]
(
 mpropertynumber varchar(255),
 Valnum varchar(255),
 )

DECLARE @sql nvarchar(4000)
DECLARE @Valnum nvarchar(4000)
DECLARE @Valid1 nvarchar(4000)
DECLARE @Valid2 nvarchar(4000)

DECLARE main_cursor CURSOR FOR
SELECT Valid1, Valid2, Valnum FROM dbo.TEMP_PWC_ValTable
Where CAST(Active as varchar(10)) = '1'
--Where CAST(Valnum as varchar(10)) in ('1', '25')

OPEN main_cursor
SET @sql = ''
SET @Valnum = ''
SET @Valid1 = ''
SET @Valid2 = ''
-- Perform the first fetch.
FETCH NEXT FROM main_cursor
INTO @Valid1, @Valid2, @Valnum


-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
      IF @sql = ''
      BEGIN

            IF @Valid2 = ''
                  SET @sql = 'SELECT mpropertynumber, ' + @Valnum + ' from [TEMP_PWC_Report_SP_MAIN] Join [TEMP_PWC_Report_SP_CHARACTERISTICS] ON [TEMP_PWC_Report_SP_CHARACTERISTICS].[mpropertynumber] = [TEMP_PWC_Report_SP_MAIN].[mpropertynumber] where' + @Valid1
            ELSE
                  SET @sql = 'SELECT mpropertynumber, ' + @Valnum + ' from [TEMP_PWC_Report_SP_MAIN] Join [TEMP_PWC_Report_SP_CHARACTERISTICS] ON [TEMP_PWC_Report_SP_CHARACTERISTICS].[mpropertynumber] = [TEMP_PWC_Report_SP_MAIN].[mpropertynumber] where ' + @Valid1 + ' and ' + @Valid2
      END
      ELSE
      BEGIN
            IF @Valid2 = ''
                          SET @sql = @sql +' union ' + 'SELECT mpropertynumber, ' + @Valnum + ' from [TEMP_PWC_Report_SP_MAIN] Join [TEMP_PWC_Report_SP_CHARACTERISTICS] ON [TEMP_PWC_Report_SP_CHARACTERISTICS].[mpropertynumber] = [TEMP_PWC_Report_SP_MAIN].[mpropertynumber] where ' + @Valid1
            ELSE
                    SET @sql = @sql +' union ' + 'SELECT mpropertynumber, ' + @Valnum + ' from [TEMP_PWC_Report_SP_MAIN] Join [TEMP_PWC_Report_SP_CHARACTERISTICS] ON [TEMP_PWC_Report_SP_CHARACTERISTICS].[mpropertynumber] = [TEMP_PWC_Report_SP_MAIN].[mpropertynumber] where ' + @Valid1 + ' and ' + @Valid2
      END
   

   -- This is executed as long as the previous fetch succeeds.
   FETCH NEXT FROM main_cursor
      INTO @Valid1, @Valid2, @Valnum
     
     
END

CLOSE main_cursor
DEALLOCATE main_cursor

   INSERT [TEMP_PWC_VALTEMP] EXEC sp_executesql @sql
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

END

It looks like it is working correctly now, (but I have not tested all conditions yet)

I changed it back to the Join and On inside the quotes

Also add the space after the Where (inside the quote)

and changed to max


Thanks

Great thanks