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_Ex tract_Non_ Group_Orig inal_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_u ser.u_addr 3, 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_c ountry, '+@sPostcode+@sPhone+@sMob ile+@sEmai l+@sDOB+'d bo.t_prize s.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='''+@ Competitio nCode+''')
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_N on_Group_O riginal_Re gs_No_Emai ls '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_N on_Group_O riginal_Re gs_No_Emai ls, Line 70
The name 'SELECT dbo.t_title.t_Description, dbo.t_user.u_firstname, dbo.t_user.u_lastname,dbo. t_user.u_a ddr1, dbo.t_user.u_addr2,dbo.t_u ser.u_addr 3, dbo.t_user.u_addr4, dbo.t_user.u_addr5,dbo.t_u ser.u_coun try, dbo.t_user.u_postcode,dbo. t_user.u_p hone,dbo.t _user.u_mo bile,dbo.t _user.u_em ail,dbo.t_ user.u_DOB ,dbo.t_pri zes.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
--------------------------
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_Ex
@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_u
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,
+@sAddress+'dbo.t_user.u_c
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='''+@
AND (dbo.t_user.u_reg_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_N
--------------------------
and the error message is like this...
Msg 203, Level 16, State 2, Procedure sp_Specific_Comp_Extract_N
The name 'SELECT dbo.t_title.t_Description,
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.
Rae MacLeman
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER