• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1045
  • Last Modified:

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
0
ukwebguy
Asked:
ukwebguy
1 Solution
 
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
 
ukwebguyAuthor Commented:
Bingo! That's the answer. Thank you.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now