using EXEC in dynamic query

[havey]

Hi i'm getting incorrect syntax near 'SELECT' and '@TblName2'

any thoughts, BTW this is my first attempt at EXEC in a stored Procedure so please be patient and kind to my wrong doings, thanks


CREATE PROCEDURE dbo.getMCpage5all
(
@MemberID int
)
AS
SET NOCOUNT ON

declare @countr1 int
declare @countr2 int
declare @TblName varchar(12)
declare @Colm1 varchar(7) --surgT12
declare @Colm2 varchar(7) --surgD12
declare @Colm3 varchar(7) --surgN12
declare @Colm4 varchar(7) --surgH12

set @countr1 = 0

while @countr1 < 6
begin

set @TblName = 'MCPage5_S' + @countr1 + '.surgT' + @countr1 + ', '+
'MCPage5_S' + @countr1 + '.surgD' + @countr1 + ', '+
'MCPage5_S' + @countr1 + '.surgN' + @countr1 + ', '+
'MCPage5_S' + @countr1 + '.surgH' + @countr1 + ', '+CHAR(13)


set @countr1 = @countr1 + 1

set @countr2 = 0

end


set @countr2 = 1
declare @TblName2 varchar(12)

while @countr2 < 6
begin

set @TblName2 = 'INNER JOIN MCPage5_S' + @countr2 + ' ON MCPage5_S' + @countr2 + '.MemberID=MCPage5_S0.MemberID '
set @Colm1 = 'surgT' + @countr2 + ', '
set @Colm2 = 'surgD' + @countr2 + ', '
set @Colm3 = 'surgN' + @countr2 + ', '
set @Colm4 = 'surgH' + @countr2 + ', '

set @countr2 = @countr2 + 1
end

set @TblName2 = Left(@TblName2,Len(@TblName2)-1) --remove trailing comma
set @TblName = Left(@TblName,Len(@TblName)-1) --remove trailing comma


EXEC(Select @TblName FROM CPage5_S0 @TblName2 WHERE MCPage5_S0.MemberID=@MemberID

RETURN

GO

[chazzy]

you're missing a parenthesis at the end of the line.

the only obvious issue i see.

[russell]

you have lots wrong here...

exec executes a string, so it should look like
EXEC('Select ' + @TblName + ' FROM CPage5_S0 ' + @TblName2 + ' WHERE MCPage5_S0.MemberID=' + @MemberID)

also, as Chazzy said, u r missing closing parentheses.

what do u want the final SQL string to look like?

u r executing a couple of while loops, but only the last value will be set in the variables.

comment out the exec line and PRINT the results:
PRINT('Select ' + @TblName + ' FROM CPage5_S0 ' + @TblName2 + ' WHERE MCPage5_S0.MemberID=' + @MemberID) to see what u are building...