Link to home
Start Free TrialLog in
Avatar of ukwebguy
ukwebguy

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of brad2575
brad2575
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ukwebguy
ukwebguy

ASKER

Bingo! That's the answer. Thank you.