smares32371
asked on
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.
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.
Last Comment
ASKER
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>
ASKER
come on someone
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
//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
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)
//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)
ASKER
Why the double "" will that work
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Microsoft SQL Server 2005
Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.
72K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
select *
from table1
where field1 = @Parameter / 100