Solved

how to set variable in query

Posted on 2012-04-04
8
249 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

910 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

18 Experts available now in Live!

Get 1:1 Help Now