Solved

how to set variable in query

Posted on 2012-04-04
8
248 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:Tagom
  • 4
  • 2
  • 2
8 Comments
 

Author Comment

by:Tagom
ID: 37806456
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
 

Author Comment

by:Tagom
ID: 37806641
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
 
LVL 3

Expert Comment

by:Chris__W
ID: 37808145
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
 
LVL 32

Expert Comment

by:ewangoya
ID: 37809965
People, You can not compare a variable to null with '='

IF @searchString IS NULL  
       SET @searchString = 'LIKE ''2'' '
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 32

Expert Comment

by:ewangoya
ID: 37810000
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
 

Author Comment

by:Tagom
ID: 37810685
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
 
LVL 3

Accepted Solution

by:
Chris__W earned 500 total points
ID: 37811927
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
 

Author Comment

by:Tagom
ID: 37812004
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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQl query 19 14
Mssql SQL query 14 28
Haw to apply join on 2 tables with this scenario 4 8
sql calculate averages 18 30
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now