Building a string in T-SQL

ukwebguy
ukwebguy used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
you may need to use a system stored procedure to EXEC your Statement.

Try replaceing your EXEC statement with

EXEC sp_executesql @SelectString

Author

Commented:
Bingo! That's the answer. Thank you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial