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

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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

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

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

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.