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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Pushpakumara MahagamageVPCommented:
WHERE
contains(Column1 , N'word1' )
OR contains(Column1 , N'word2' )
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.