Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

SQL 2005

I am going to be passing a parameter over in the value of one of these numbers 1200, 1300 ,1400
WHERE if its 1200 I want to set it 12 and 1300 to 13 and 1400 to 14 etc. What would be the most efficent way to perform this in a where clause. I am confussed on how to set it to a two digit number.
0
smares32371
Asked:
smares32371
2 Solutions
 
Ephraim WangoyaCommented:


select *
from table1
where field1 = @Parameter / 100
0
 
smares32371Author Commented:
The SQL below actually works but in Coldfusion but i'm trying to convert it to SQL store procdure. The only thing confused with is setting the sectionID.
SELECT * 
FROM tads INNER JOIN tAdSections ON tAdSections.SectionID = tAdAds.sectionCode
WHERE (CAD = 1)  
<cfif SectionID lt 1200>
  <cfset SectionID = 11>
and
sectionCode like '#sectionID#%'

<cfelseif SectionID lt 1300>
  <cfset SectionID = 12>
and
sectionCode like '#sectionID#%'
	   
<cfelseif SectionID lt 1500>
  <cfset SectionID = 14>
and
sectionCode like '#sectionID#%'

<cfelseif SectionID lt 1700>
  <cfset SectionID = 16>
and
sectionCode like '#sectionID#%'

<cfelseif SectionID eq 1625>
  <cfset SectionID = 1625>
and
sectionCode like '#sectionID#%'

<cfelseif SectionID eq 1616>
  <cfset SectionID = 1616>
and
sectionCode like '#sectionID#%'

<cfelseif SectionID lt 1800>
  <cfset SectionID = 17>
and
sectionCode like '#sectionID#%'
</cfif>

Open in new window

0
 
smares32371Author Commented:
come on someone
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
devilJinKazamaCommented:
yep u can do that in a stored procedure as well:

//input parameter
@sectionID int

Declare @qry = nvarchar(5000)

//build ur sql statement
Set @qry = 'SELECT * FROM tads INNER JOIN tadSections ON tadSections.sectionID = tads.sectionCode
WHERE (CAD = 1)'

if @sectionID < 1200
   Set @qry = @qry + 'AND sectionCode LIKE ''11%'''
if @sectionID < 1300
  Set @qry = @qry + 'AND sectionCode LIKE ''12%'''
if @sectionID < 1500
  Set @qry = @qry + 'AND sectionCode LIKE ''14%'''
if @sectionID < 1700
   Set @qry = @qry + 'AND sectionCode LIKE ''16%'''
if @sectionID = 1625
   Set @qry = @qry + 'AND sectionCode LIKE ''1625%'''
if @sectionID = 1616
   Set @qry = @qry + 'AND sectionCode LIKE ''1616%'''
if @sectionID < 1800
   Set @qry = @qry + 'AND sectionCode LIKE ''17%'''

EXECUTE(@qry)


just be aware that sectionID meets one of the conditions else ur query will return everything where CAD = 1 and that may not be what you want.

Good luck
0
 
devilJinKazamaCommented:
should be using else if actually

//input parameter
@sectionID int

Declare @qry = nvarchar(5000)

//build ur sql statement
Set @qry = 'SELECT * FROM tads INNER JOIN tadSections ON tadSections.sectionID = tads.sectionCode
WHERE (CAD = 1)'

if @sectionID < 1200
   Set @qry = @qry + 'AND sectionCode LIKE ''11%'''
else if @sectionID < 1300
  Set @qry = @qry + 'AND sectionCode LIKE ''12%'''
else if @sectionID < 1500
  Set @qry = @qry + 'AND sectionCode LIKE ''14%'''
else if @sectionID = 1625
   Set @qry = @qry + 'AND sectionCode LIKE ''1625%'''
else if @sectionID = 1616
   Set @qry = @qry + 'AND sectionCode LIKE ''1616%'''
else if @sectionID < 1700
   Set @qry = @qry + 'AND sectionCode LIKE ''16%'''
else if @sectionID < 1800
   Set @qry = @qry + 'AND sectionCode LIKE ''17%'''

EXECUTE(@qry)
0
 
smares32371Author Commented:
Why the double ""  will that work
0
 
devilJinKazamaCommented:
you need the double quotes to escape the quotes as it is being declared inside single quotes.

yes it will work , just need to add a space before declaring your where clause additions

alter procedure [pr_procName]

--input parameter
@sectionID int

AS

Declare @qry nvarchar(MAX)

//build ur sql statement
Set @qry = 'SELECT * FROM tads INNER JOIN tadSections ON tadSections.sectionID = tads.sectionCode
WHERE (CAD = 1) '

if @sectionID < 1200
   Set @qry = @qry + 'AND sectionCode LIKE ''11%'''
else if @sectionID < 1300
  Set @qry = @qry + 'AND sectionCode LIKE ''12%'''
else if @sectionID < 1500
  Set @qry = @qry + 'AND sectionCode LIKE ''14%'''
else if @sectionID = 1625
   Set @qry = @qry + 'AND sectionCode LIKE ''1625%'''
else if @sectionID = 1616
   Set @qry = @qry + 'AND sectionCode LIKE ''1616%'''
else if @sectionID < 1700
   Set @qry = @qry + 'AND sectionCode LIKE ''16%'''
else if @sectionID < 1800
   Set @qry = @qry + 'AND sectionCode LIKE ''17%'''

EXECUTE(@qry)

try that, that should work for you . lemme know if you have issues.
0
 
_agx_Commented:
   >> EXECUTE(@qry)
Dynamic sql isn't needed for this and it should only require 3 conditions.

CREATE procedure yourProcedureName
   @sectionID INT
AS
BEGIN
   DECLARE @filter varchar(10)
   SET @filter = CASE WHEN @sectionID = 1625 THEN '1625'
                      WHEN @sectionID = 1616 THEN '1616'
                      WHEN @sectionID < 1800 THEN LEFT(CAST(@sectionID AS VARCHAR), 2)
                      ELSE ''
                 END

   
   SELECT   *   ---  use column list 
   FROM   tads INNER JOIN tadSections ON tadSections.sectionID = tads.sectionCode
   WHERE   CAD = 1
   AND     tads.sectionCode LIKE @filter +'%'
END

Open in new window

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now