how to set variable in query

I have need to set a variable passed into this stored procedure to read differently than the application passes.
IF the app passes 'LIKE ''%'' '  I need it to change the % to 2 and then use substring(@searchString, 7, 9999) so that the query only utilizes the 2
IF it passes null I need the same thing as above to happen
however
IF it passes something like 'LIKE ''2'' '
I need it to send only the two.
I have what I thought would work, however the syntex for the if/else is not correct.
BEGIN
	DECLARE @report VARCHAR(4000),
			@orderby VARCHAR (4000)
			
	set @startDate = [dbo].[getAutoDateStart](@autodate, @startDate, getDate())
	set @endDate = [dbo].[getAutoDateEnd](@autodate, @endDate, getDate())
	IF @searchString = 'LIKE ''%'' ' 
	 SET @searchString = 'LIKE ''2'' ' 
	 	 SET @searchString = substring(@searchString, 7, 9999)
	  ELSE 
		IF @searchString != 'LIKE ''%'' '  SET @searchString = substring(@searchString, 7, 9999)
	
	BEGIN
	set @report = '
		SELECT CLE_EVID AS ''APPLICATION'',  COUNT(CLE_EVID) AS ''Count'',DATEPART(year, CLE_TIME) as [year],
			DATEPART(month, CLE_TIME) as [month],DATEPART(day, CLE_TIME) as [day],DATEPART(hour, CLE_TIME) as [hour],
			DATEPART(mi,CLE_TIME) AS [minute],DATEPART(ss,CLE_TIME) AS [Second]
		FROM CL_EVENTS_IDX  
			INNER JOIN CL_EVENTS  ON CLE_ID = CLE_IDX_KEY
		WHERE CLE_TYPE = 508 AND CLE_EVID = 1
			AND cle_idx_ucode =  ''' + @utilityKey + '''
			AND CLE_IDX_OFFHOOK  BETWEEN   ''' + CAST(@startDate AS VARCHAR(100)) + ''' AND ''' + CAST(@endDate AS VARCHAR(100)) + '''
		GROUP by CLE_EVID,DATEPART(year, CLE_TIME) ,DATEPART(month, CLE_TIME) ,DATEPART(day, CLE_TIME), DATEPART(hour, CLE_TIME),
		DATEPART(mi,CLE_TIME),DATEPART(ss,CLE_TIME)
		HAVING count(CLE_EVID) >' + @searchString
	set @orderby = '
		  ORDER BY [year],[month],[day],[hour],[minute],[Second]'
 

Open in new window

TagomAsked:
Who is Participating?
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.

TagomAuthor Commented:
I changed the procedure to
	set @startDate = [dbo].[getAutoDateStart](@autodate, @startDate, getDate())
	set @endDate = [dbo].[getAutoDateEnd](@autodate, @endDate, getDate())
	
	IF @searchString = NULL  
	 SET @searchString = 'LIKE ''2'' ' 
	 SET @searchString = substring(@searchString, 7, 9999)
	IF @searchString = 'LIKE ''%'' ' 
	 SET @searchString = 'LIKE ''2'' ' 
	 SET @searchString = substring(@searchString, 7, 9999)
	IF @searchString != 'LIKE ''%'' '  SET @searchString = substring(@searchString, 7, 9999)
	
	
	BEGIN
	
		SELECT CLE_EVID AS 'APPLICATION',  COUNT(CLE_EVID) AS 'Count',DATEPART(year, CLE_TIME) as [year],
			DATEPART(month, CLE_TIME) as [month],DATEPART(day, CLE_TIME) as [day],DATEPART(hour, CLE_TIME) as [hour],
			DATEPART(mi,CLE_TIME) AS [minute],DATEPART(ss,CLE_TIME) AS [Second]
		FROM CL_EVENTS_IDX  
			INNER JOIN CL_EVENTS  ON CLE_ID = CLE_IDX_KEY
		WHERE CLE_TYPE = 508 AND CLE_EVID = 1
			AND cle_idx_ucode =  @utilityKey 
			AND CLE_IDX_OFFHOOK  BETWEEN @startDate  AND @endDate 
		GROUP by CLE_EVID,DATEPART(year, CLE_TIME) ,DATEPART(month, CLE_TIME) ,DATEPART(day, CLE_TIME), DATEPART(hour, CLE_TIME),
		DATEPART(mi,CLE_TIME),DATEPART(ss,CLE_TIME)
		HAVING count(CLE_EVID) >  @searchString
	  ORDER BY [year],[month],[day],[hour],[minute],[Second]
 
	END
	END

Open in new window


but it is still not quite right. Takes a very long time to process.
suggestions?
0
TagomAuthor Commented:
This code is pretty close - however it is not utilizing the substring correctly
	DECLARE @report VARCHAR(4000)
			
			
	set @startDate = [dbo].[getAutoDateStart](@autodate, @startDate, getDate())
	set @endDate = [dbo].[getAutoDateEnd](@autodate, @endDate, getDate())
	IF @searchString = NULL  
	 SET @searchString = 'LIKE ''2'' ' 
	 SET @searchString = substring(@searchString, 8, 9999)
	IF @searchString = 'LIKE ''%'' ' 
	 SET @searchString = 'LIKE ''2'' ' 
	 SET @searchString = substring(@searchString, 8, 9999)
	IF @searchString != 'LIKE ''%'' '  SET @searchString = substring(@searchString, 8, 9999)
	
	
	BEGIN
	set @report = '
		SELECT CLE_EVID AS ''APPLICATION'',  COUNT(CLE_EVID) AS ''Count'',DATEPART(year, CLE_TIME) as [year],
			DATEPART(month, CLE_TIME) as [month],DATEPART(day, CLE_TIME) as [day],DATEPART(hour, CLE_TIME) as [hour],
			DATEPART(mi,CLE_TIME) AS [minute],DATEPART(ss,CLE_TIME) AS [Second]
		FROM CL_EVENTS_IDX  
			INNER JOIN CL_EVENTS  ON CLE_ID = CLE_IDX_KEY
		WHERE CLE_TYPE = 508 AND CLE_EVID = 1
			AND cle_idx_ucode =  ''' + @utilityKey + '''
			AND CLE_IDX_OFFHOOK  BETWEEN   ''' + CAST(@startDate AS VARCHAR(100)) + ''' AND ''' + CAST(@endDate AS VARCHAR(100)) + '''
		GROUP by CLE_EVID,DATEPART(year, CLE_TIME) ,DATEPART(month, CLE_TIME) ,DATEPART(day, CLE_TIME), DATEPART(hour, CLE_TIME),
		DATEPART(mi,CLE_TIME),DATEPART(ss,CLE_TIME)
		HAVING count(CLE_EVID) > ''' + @searchString + ''' '
	
 
	END
	PRINT @report
	EXEC (@report)

Open in new window


query executes as
		SELECT CLE_EVID AS 'APPLICATION',  COUNT(CLE_EVID) AS 'Count',DATEPART(year, CLE_TIME) as [year],
			DATEPART(month, CLE_TIME) as [month],DATEPART(day, CLE_TIME) as [day],DATEPART(hour, CLE_TIME) as [hour],
			DATEPART(mi,CLE_TIME) AS [minute],DATEPART(ss,CLE_TIME) AS [Second]
		FROM CL_EVENTS_IDX  
			INNER JOIN CL_EVENTS  ON CLE_ID = CLE_IDX_KEY
		WHERE CLE_TYPE = 508 AND CLE_EVID = 1
			AND cle_idx_ucode =  'B89153AC-1323-49F4-BCA4-F7BFC4E04592'
			AND CLE_IDX_OFFHOOK  BETWEEN   'Jan  1 2011 12:00AM' AND 'Dec 31 2011 11:59PM'
		GROUP by CLE_EVID,DATEPART(year, CLE_TIME) ,DATEPART(month, CLE_TIME) ,DATEPART(day, CLE_TIME), DATEPART(hour, CLE_TIME),
		DATEPART(mi,CLE_TIME),DATEPART(ss,CLE_TIME)
		HAVING count(CLE_EVID) > '' 

Open in new window

this 'HAVING count(CLE_EVID) > '' '
should be
HAVING count(CLE_EVID) > 2
0
Chris__WCommented:
Hello,

Your IF statements for setting @searchstring are missing the usual formatting, which may be confisuing the system.  Try the following:

	IF @searchString = NULL  
                 BEGIN;
	 SET @searchString = 'LIKE ''2'' ' 
	 SET @searchString = substring(@searchString, 8, 9999)
                 END;
	IF @searchString = 'LIKE ''%'' ' 
                 BEGIN;
	 SET @searchString = 'LIKE ''2'' ' 
	 SET @searchString = substring(@searchString, 8, 9999)
                 END;
	IF @searchString != 'LIKE ''%'' '  SET @searchString = substring(@searchString, 8, 9999);

Open in new window


Thanks,
Chris
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Ephraim WangoyaCommented:
People, You can not compare a variable to null with '='

IF @searchString IS NULL  
       SET @searchString = 'LIKE ''2'' '
0
Ephraim WangoyaCommented:
Also I dont understand the meaning of this

       
         SET @searchString = 'LIKE ''2'' '
       SET @searchString = substring(@searchString, 8, 9999)

Wouldn't this just return an empty string, it does not make sense

What is the need for the substring

You just need to replace searchstring with 2, no need for substring


IF (@searchString is NULL) or (@searchString = 'LIKE ''2'' ') or (@searchString = 'LIKE ''%'' ')
  SET @searchString = '2'
0
TagomAuthor Commented:
That 2 could be any number, so you can not hardcode it to be = '2'

The application passes 'Like ''then the number given by end user in report application'' '

I was simply using 2 as an example.
the substring is to try and pull out just the 'then the number given by end user in report application'
0
Chris__WCommented:
Hello,
Nice catch on the IS NULL ewangoya.

The Substring function would need to start at 7, since the output does not include the ' at the begining nor the first of the 2 ' before the number: LIKE '2'  
You'll be picking up the last quote with 9999 in there.  It'd be cleaner to select between the 2 pieces you want, since you know the length of them:
part 1: LIKE '
6 chars from the left
part 2: '
1 char from the right
So here's the substring line:
SET @searchString = substring(LTRIM(RTRIM(@searchString)), 7, ((LEN(LTRIM(RTRIM(@searchString)))) - 1))

The last substring validation - IF @searchString != 'LIKE ''%'' - doesn't make sense to me that it would use the same action, as that comparison seems to indicate that you do not know the location in the string of the number you are looking for.  You may want to bounce it with a RETURN or GOTO command.  Otherwise, I'd use some logic that finds the numbers and just pull those.

To simplify this, if the 2 is a variable, it could be trimmed seperately from this process logic (and even cast as an Integer if desired).

Speaking of CAST, this item looks like a point of concern:
      HAVING count(CLE_EVID) > ''' + @searchString + '''
I'd compare apples to apples inplicitly.
      HAVING count(CLE_EVID) > CAST(ISNULL(' + @searchString + ',''0'') AS INT) '

 -  Replace the 0 with a number that makes sense for what you are working with.


Please give it a try and post the results.

Thanks,
Chris
0

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
TagomAuthor Commented:
The application has three choices when passing this variable
1. it can pass a null - leaving it null would be such a large return that the db would crash
2. it can pass 'LIKE ''%'' which is the default - if this happens - I need to replace the % with a reasonable int - to keep the db from crashing
3. it can pass 'Like ''then the number given by end user in report application'' ' which is preferable

SET @searchString = substring(LTRIM(RTRIM(@searchString)), 7, ((LEN(LTRIM(RTRIM(@searchString)))) - 1))  - is perfect as I was picking up the last ' - I did not even address this as I had settled on writing the query different - without the results I truly wanted.

will try this and let you know!
0
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.