Query

I have a table
ID Range
1 13-17
2 18-24
3 25-34
I want to find the ID by supplying the say 23. The output in this case will
be ID= 2. Can I achieve this with a single query?
Thanks.
DECLARE @.SearchFor INT
SET @.SearchFor =3D 23
Select ID from TableName
Where @.SearchFor BETWEEN
SUBSTRING(Range,1,CHARINDEX('-',Range)-1) AND
SUBSTRING(Range,CHARINDEX('-',Range)+1,(LEN(RANGE)-CHARINDEX('-',Range))
If you don=B4t post DDL, all samples are untested.
HTH (anyway), Jens Suessmeyer.
http://www.sqlserver2005.de
--|||If this is a one-time query, then try Jens's suggestion.
Otherwise, I would suggest you add columns to the table with the integer
value of the extrema. After that, your table + data could look like
this:
ID Range Low High
1 13-17 13 17
2 18-24 18 24
3 25-34 25 34
You could use a variation of Jens's query to update the table with the
Low and High value. After that, you simply use the query
SELECT ID FROM ... WHERE 23 BETWEEN Low AND High
Gert-Jan
XXX wrote:
> I have a table
> ID Range
> 1 13-17
> 2 18-24
> 3 25-34
> I want to find the ID by supplying the say 23. The output in this case wil
l
> be ID= 2. Can I achieve this with a single query?
> Thanks.|||Anyway if you will go these approach, I would get rid of the range
column, because the information would be stored redundant in the column
because it can be careted anytime from the new columns Low/High
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--

QRY question: If field1 is null then field2

Is there a way to create a column that will show the
value of field1 unless field1 is null in which case it
will show the value of field2? I do it all the time in
Access but can't seem to figure out if SQL Server can do
it as well. Any help or suggestions are geratly
appreciatd!
Here is how I do it in MS Access:
SELECT IIf([Field1] Is Null,[Field2],[Field1]) AS [Output]
FROM [Table];
Dan,
In SQL Server, you can write
CASE WHEN Field1 IS NULL THEN Field2 ELSE Field1 END AS [Output]
Since this particular need comes up often, there is a shorthand form:
COALESCE(Field1, Field2) AS [Output]
Steve Kass
Drew University
Dan wrote:

>Is there a way to create a column that will show the
>value of field1 unless field1 is null in which case it
>will show the value of field2? I do it all the time in
>Access but can't seem to figure out if SQL Server can do
>it as well. Any help or suggestions are geratly
>appreciatd!
>Here is how I do it in MS Access:
>SELECT IIf([Field1] Is Null,[Field2],[Field1]) AS [Output]
>FROM [Table];
>

q; using RAISERROR

In my Windows application I use sqlCmd.ExecuteNonQuery() to execute the
stored procedure, In case of an error in the stored procedure I need to
return an exception to application, will RAISERROR in stored procedure
accomplish that?JIM.H.,
Yes.
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"JIM.H." wrote:

> In my Windows application I use sqlCmd.ExecuteNonQuery() to execute the
> stored procedure, In case of an error in the stored procedure I need to
> return an exception to application, will RAISERROR in stored procedure
> accomplish that?|||For a full discussion of RAISERROR, refer to Books on Line.
You can use RAISERROR to 'thow' an error condition to the calling process.
Normally, you would have a condition to check, then use a BEGIN-END block to
both RAISERROR and then RETURN to the calling process. Without the RETURN,
any code after the RAISERROR will also execute.
i.e.,
IF {condition to check}
BEGIN
RAISERROR ("Error Occurred", 16, 1)
RETURN
END
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:116D7CFD-FE3D-432B-B5DA-9A0CD93F744A@.microsoft.com...
> In my Windows application I use sqlCmd.ExecuteNonQuery() to execute the
> stored procedure, In case of an error in the stored procedure I need to
> return an exception to application, will RAISERROR in stored procedure
> accomplish that?

q; using RAISERROR

In my Windows application I use sqlCmd.ExecuteNonQuery() to execute the
stored procedure, In case of an error in the stored procedure I need to
return an exception to application, will RAISERROR in stored procedure
accomplish that?JIM.H.,
Yes.
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server â' a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"JIM.H." wrote:
> In my Windows application I use sqlCmd.ExecuteNonQuery() to execute the
> stored procedure, In case of an error in the stored procedure I need to
> return an exception to application, will RAISERROR in stored procedure
> accomplish that?|||For a full discussion of RAISERROR, refer to Books on Line.
You can use RAISERROR to 'thow' an error condition to the calling process.
Normally, you would have a condition to check, then use a BEGIN-END block to
both RAISERROR and then RETURN to the calling process. Without the RETURN,
any code after the RAISERROR will also execute.
i.e.,
IF {condition to check}
BEGIN
RAISERROR ("Error Occurred", 16, 1)
RETURN
END
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:116D7CFD-FE3D-432B-B5DA-9A0CD93F744A@.microsoft.com...
> In my Windows application I use sqlCmd.ExecuteNonQuery() to execute the
> stored procedure, In case of an error in the stored procedure I need to
> return an exception to application, will RAISERROR in stored procedure
> accomplish that?