Syntax error in Stored Procedure...

Hi there.

Probably a simple thing, but I can't figure it out... desperate! The procedure looks like this, and the problem is the SELECT statement in the last part of the code. I inserted a "WHERE" clause with some fixed criteria, but the syntax is wrong:

USE [mcd]
GO
/****** Object:  StoredProcedure [dbo].[proc_q3TreatmentRelevant_Crosstab]    Script Date: 12/18/2017 10:47:22 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[proc_q3TreatmentRelevant_PD1_inhib] 
AS

/*Declare Variables*/
DECLARE @vals AS VARCHAR(MAX) = ''
DECLARE @Maxvals AS VARCHAR(MAX) = ''

/*Get names of dynamic columns*/
SELECT 
  @vals = @vals+ '['+ [TreatDrugText] +']' + ',' 
, @Maxvals = @Maxvals + 'MAX(['+ [TreatDrugText] +']) ' + '['+ [TreatDrugText] +']' + ',' 
FROM [dbo].[view_3TreatmentRelevant]
GROUP BY [TreatDrugText]

/*Remove last comma*/
SELECT @vals = SUBSTRING(@vals,1,LEN(@vals)-1)
SELECT @Maxvals = ',' + SUBSTRING(@Maxvals,1,len(@Maxvals)-1)

--PRINT @vals
--PRINT @MaxVals

/**Generate dynamic SQL*/
DECLARE @SQL AS VARCHAR(MAX) = '

SELECT 
      CoursePatId 
      ,MIN(FirstTreatStart1) FirstTreatStart
      ,SUM(NumberOfTreatments) NumberOfTreatments
      ' + @MaxVals + '
FROM
(
      SELECT 
      dbo.view_3TreatmentRelevant.CoursePatID, 
      MIN(dbo.view_3TreatmentRelevant.TreatStart) AS FirstTreatStart, 
      MIN(dbo.view_3TreatmentRelevant.TreatStart) AS FirstTreatStart1,
      COUNT(dbo.view_3TreatmentRelevant.PatSiteID) as NumberOfTreatments,
      dbo.view_3TreatmentRelevant.[TreatDrugText] 
	  FROM 
            dbo.view_3TreatmentRelevant
      WHERE [TreatDrugText]='pembrolizumab' OR [TreatDrugText]='nivolumab'  
      GROUP BY
            dbo.view_3TreatmentRelevant.CoursePatID, dbo.view_3TreatmentRelevant.[TreatDrugText]
) AS BaseData
PIVOT 
(
      MIN(BaseData.FirstTreatStart) FOR [TreatDrugText] IN (' + @Vals + ')      
) AS PivotTable
GROUP BY CoursePatId
ORDER BY CoursePatID'

/*Execute the dynamic string*/
EXEC ( @SQL )

Open in new window


What am I doing wrong??

Best regards

Ulrich
ullenulleAsked:
Who is Participating?
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
you need to double up on those single quotes when already inside a string variable

      WHERE [TreatDrugText]=''pembrolizumab'' OR [TreatDrugText]=''nivolumab''

note they are two single quotes each side of the literal.
1
 
Pushpakumara MahagamageVPCommented:
Hi,

Can you use like instead of =

WHERE column1 LIKE '%word1%'
   OR column1 LIKE '%word2%'
0
 
ullenulleAuthor Commented:
UPDATE:

I  moced the "WHERE" clause up to the  first SELECT statement, and the result is closer to what I want. Only thing left is that the result shows NULL for some of the rows in the two columns "pembrolizumab" and "nivolumab" that is the result from the first SELECT and used in the PIVOT.
How can I change the script to avoid NULL values for these columns only?
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Pushpakumara MahagamageVPCommented:
WHERE
contains(Column1 , N'word1' )
OR contains(Column1 , N'word2' )
0
 
ullenulleAuthor Commented:
That gives me a syntax error... both of your suggestions.
0
 
Mark WillsTopic AdvisorCommented:
it is the single quotes - thinks you are prematurely finishing the string used to build the @sql variable.

I will have a look...
0
 
Pushpakumara MahagamageVPCommented:
by the way are you getting
1. both "pembrolizumab" and "nivolumab" Columns NULL result
OR
2. One Colomn is NULL
0
 
Mark WillsTopic AdvisorCommented:
for example if we are to normally say

select 'a' as Aletter

-- if inside a string we would

select 'select''a'' as Aletter'

if you run the above you will see what I mean

--because essentially you are doing

exec('select''a'' as Aletter')

-- or more accurately

declare @sql varchar(max) = 'select''a'' as Aletter'
exec(@sql)

Does that make sense ?
1
 
Olaf DoschkeSoftware DeveloperCommented:
Mark Wilis has spotted this correctly.

You're having an assignment starting here:

DECLARE @SQL AS VARCHAR(MAX) = '...

Open in new window

Side note, this is declaration and assignment at the same time.
The next lines all belong to this assignment, which ends at
...
ORDER BY CoursePatID'

Open in new window


You added your where clause within this multiline string. What editor do you use? Did you notice syntax coloring? Did you notice it change when you added your clause? That's a sign you broke this string.

When you want to write a string within a string, you have put them in double single quotes (meaning two ' instead of one ").

Bye, Olaf.
2
 
ullenulleAuthor Commented:
Thank you Mark! You're my HERO! :-))))))
0
 
Mark WillsTopic AdvisorCommented:
*laughing* not a hero - just been there, done that myself (a few times)

But, a pleasure to have been able to help you resolve your problem.

And thanks Olaf for your support and explanations. Appreciated.

Cheers,
Mark Wills
1
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.

All Courses

From novice to tech pro — start learning today.