Building a string in T-SQL

I'm trying to build a stored procedure which will accept a number of parameters and use these to build a SELECT string dynamically, to vary the columns returned in the resulting recordset. My stored procedure looks like this...
---------------------------------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- ========================================================================
-- Author            : Rae MacLeman
-- Create date      : 11 October 2008
-- Amended            : 11 October 2008
-- Description      : Extract Entry Data for a specific non-grouped competition
-- ========================================================================
ALTER PROCEDURE [dbo].[sp_Specific_Comp_Extract_Non_Group_Original_Regs_No_Emails]
      @CompetitionCode char(10),
      @Brand char(3),
      @incEmail char(1),            -- include the email field in output (Y/N)
      @incPhone char(1),            -- include the phone field in output (Y/N)
      @incMobile char(1),            -- include the mobile field in output (Y/N)
      @incDOB char(1),            -- include the DoB field in output (Y/N)
      @incAddress char(1),      -- include the address fields in output (Y/N)
      @incPostcode char(1)      -- include the postcode field in output (Y/N)
      
AS

DECLARE @sAddress NVARCHAR(500)
DECLARE @sDOB NVARCHAR(17)
DECLARE @sMobile NVARCHAR(20)
DECLARE @sPhone NVARCHAR(19)
DECLARE @sEmail NVARCHAR(19)
DECLARE @sPostcode NVARCHAR(22)

BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here

IF @incAddress = 'Y' SET @sAddress = 'dbo.t_user.u_addr1, dbo.t_user.u_addr2,dbo.t_user.u_addr3, dbo.t_user.u_addr4, dbo.t_user.u_addr5,'
      ELSE SET @sAddress = ''

IF @incDOB = 'Y' SET @sDOB = 'dbo.t_user.u_DOB,'
      ELSE SET @sDOB = ''

IF @incMobile = 'Y' SET @sMobile = 'dbo.t_user.u_mobile,'
      ELSE SET @sMobile = ''

IF @incPhone = 'Y' SET @sPhone = 'dbo.t_user.u_phone,'
      ELSE SET @sPhone = ''

IF @incEmail = 'Y' SET @sEmail = 'dbo.t_user.u_email,'
      ELSE SET @sEmail = ''

IF @incPostcode = 'Y' SET @sPostcode = 'dbo.t_user.u_postcode,'
      ELSE SET @sPostcode = ''

DECLARE @SelectString NVARCHAR(4000)

SET @SelectString = 'SELECT dbo.t_title.t_Description, dbo.t_user.u_firstname, dbo.t_user.u_lastname,'
+@sAddress+'dbo.t_user.u_country, '+@sPostcode+@sPhone+@sMobile+@sEmail+@sDOB+'dbo.t_prizes.p_Code,
dbo.t_prizes.p_Header,
(SELECT answer FROM  dbo.t_New_Answers WHERE (magazine = '''+@Brand+''') AND (question_id = 21) AND (user_id = dbo.t_user.u_id)) AS EmailOptin,
(SELECT answer FROM  dbo.t_New_Answers WHERE (magazine = '''+@Brand+''') AND (question_id = 22) AND (user_id = dbo.t_user.u_id)) AS SMSOptin,
(SELECT answer FROM  dbo.t_New_Answers WHERE (magazine = '''+@Brand+''') AND (question_id = 23) AND (user_id = dbo.t_user.u_id)) AS PostEmapOptout,
(SELECT answer FROM  dbo.t_New_Answers WHERE (magazine = '''+@Brand+''') AND (question_id = 24) AND (user_id = dbo.t_user.u_id)) AS PostThirdOptout
FROM dbo.t_entries
INNER JOIN dbo.t_prizes ON dbo.t_entries.e_prizeid = dbo.t_prizes.p_ID
INNER JOIN dbo.t_user ON dbo.t_entries.e_userid = dbo.t_user.u_id
INNER JOIN dbo.t_title ON dbo.t_user.u_title = dbo.t_title.t_ID
WHERE (dbo.t_prizes.p_code='''+@CompetitionCode+''')
AND (dbo.t_user.u_reg_brand='''+@Brand+''')
AND (DATEDIFF ( second , dbo.t_user.u_createDate , dbo.t_entries.e_date ) < 120)'

EXEC @SelectString
END
---------------------------------------------------------

... and I'm running it like this...

---------------------------------------------------------
exec sp_Specific_Comp_Extract_Non_Group_Original_Regs_No_Emails 'YRS0807042', 'YRS','Y','Y','Y','Y','Y','Y'
---------------------------------------------------------

and the error message is like this...

Msg 203, Level 16, State 2, Procedure sp_Specific_Comp_Extract_Non_Group_Original_Regs_No_Emails, Line 70
The name 'SELECT dbo.t_title.t_Description, dbo.t_user.u_firstname, dbo.t_user.u_lastname,dbo.t_user.u_addr1, dbo.t_user.u_addr2,dbo.t_user.u_addr3, dbo.t_user.u_addr4, dbo.t_user.u_addr5,dbo.t_user.u_country, dbo.t_user.u_postcode,dbo.t_user.u_phone,dbo.t_user.u_mobile,dbo.t_user.u_email,dbo.t_user.u_DOB,dbo.t_prizes.p_Code,
dbo.t_prizes.p_Header,
(SELECT answer FROM  dbo.t_New_Answers WHERE (magazine = 'YRS') AND (question_id = 21) AND (user_id = dbo.t_user.u_id)) AS EmailOptin,
(SELECT answer FROM  dbo.t_New_Answers WHERE (magazine = 'YRS') AND (question_id = 22) AND (user_id = dbo.t_user.u_id)) AS SMSOptin,
(SELECT answer FROM  dbo.t_Ne' is not a valid identifier.

I think the clue may be in the words "The name 'SELECT dbo.t_title.t_Description..." in other words, the whole string is being taken as the variable name, but I'm stumped as to why that is the case. Can you help, please?

Rae MacLeman
LVL 1
ukwebguyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

brad2575Commented:
you may need to use a system stored procedure to EXEC your Statement.

Try replaceing your EXEC statement with

EXEC sp_executesql @SelectString
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ukwebguyAuthor Commented:
Bingo! That's the answer. Thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.