Case statement

Is it possible to have multiple expressions, or dml expressions in a case
statement similar to the following?
DECLARE @.path_f1 nvarchar(200)
DECLARE @.path_f2 nvarchar(200)
SELECT
@.path_f1 = output_path_file1,
@.path_f2 = output_path_file2
FROM t1
WHERE t1id = 1
SELECT
CASE
WHEN f1_status = 0 THEN
UPDATE t1
SET f1_status = 1, f1_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f1, null
WHEN f1_status = 1 AND f2_status = 0 THEN
UPDATE t1
SET file2_status = 1, file2_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f2, null
ELSE
RAISERROR ('Error Message', 16,1)
END
FROM t1
WHERE t1id = 1
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1Not the way you're doing it. You can have something along the lines of:
UPDATE t1
SET
f1_status = CASE
WHEN f1_status = 0 THEN 1
ELSE f1_status END
, f1_created = CASE
WHEN f1_status = 0 THEN getdate()
ELSE f1_created END
WHERE t1id = 1
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:5df3ff92c5d28@.uwe...
Is it possible to have multiple expressions, or dml expressions in a case
statement similar to the following?
DECLARE @.path_f1 nvarchar(200)
DECLARE @.path_f2 nvarchar(200)
SELECT
@.path_f1 = output_path_file1,
@.path_f2 = output_path_file2
FROM t1
WHERE t1id = 1
SELECT
CASE
WHEN f1_status = 0 THEN
UPDATE t1
SET f1_status = 1, f1_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f1, null
WHEN f1_status = 1 AND f2_status = 0 THEN
UPDATE t1
SET file2_status = 1, file2_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f2, null
ELSE
RAISERROR ('Error Message', 16,1)
END
FROM t1
WHERE t1id = 1
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1|||The issue would become very straightforward and simple only if one stops
using the phrase 'a CASE statement' because there is no such a thing, and
starts using 'a CASE expression.'
Linchi
"cbrichards via SQLMonster.com" wrote:
> Is it possible to have multiple expressions, or dml expressions in a case
> statement similar to the following?
> DECLARE @.path_f1 nvarchar(200)
> DECLARE @.path_f2 nvarchar(200)
> SELECT
> @.path_f1 = output_path_file1,
> @.path_f2 = output_path_file2
> FROM t1
> WHERE t1id = 1
> SELECT
> CASE
> WHEN f1_status = 0 THEN
> UPDATE t1
> SET f1_status = 1, f1_created = getdate()
> WHERE t1id = 1
> EXEC usp_t1 @.path_f1, null
> WHEN f1_status = 1 AND f2_status = 0 THEN
> UPDATE t1
> SET file2_status = 1, file2_created = getdate()
> WHERE t1id = 1
> EXEC usp_t1 @.path_f2, null
> ELSE
> RAISERROR ('Error Message', 16,1)
> END
> FROM t1
> WHERE t1id = 1
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1
>

Case statement

Is it possible to have multiple expressions, or dml expressions in a case
statement similar to the following?
DECLARE @.path_f1 nvarchar(200)
DECLARE @.path_f2 nvarchar(200)
SELECT
@.path_f1 = output_path_file1,
@.path_f2 = output_path_file2
FROM t1
WHERE t1id = 1
SELECT
CASE
WHEN f1_status = 0 THEN
UPDATE t1
SET f1_status = 1, f1_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f1, null
WHEN f1_status = 1 AND f2_status = 0 THEN
UPDATE t1
SET file2_status = 1, file2_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f2, null
ELSE
RAISERROR ('Error Message', 16,1)
END
FROM t1
WHERE t1id = 1
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200603/1
Not the way you're doing it. You can have something along the lines of:
UPDATE t1
SET
f1_status = CASE
WHEN f1_status = 0 THEN 1
ELSE f1_status END
, f1_created = CASE
WHEN f1_status = 0 THEN getdate()
ELSE f1_created END
WHERE t1id = 1
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:5df3ff92c5d28@.uwe...
Is it possible to have multiple expressions, or dml expressions in a case
statement similar to the following?
DECLARE @.path_f1 nvarchar(200)
DECLARE @.path_f2 nvarchar(200)
SELECT
@.path_f1 = output_path_file1,
@.path_f2 = output_path_file2
FROM t1
WHERE t1id = 1
SELECT
CASE
WHEN f1_status = 0 THEN
UPDATE t1
SET f1_status = 1, f1_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f1, null
WHEN f1_status = 1 AND f2_status = 0 THEN
UPDATE t1
SET file2_status = 1, file2_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f2, null
ELSE
RAISERROR ('Error Message', 16,1)
END
FROM t1
WHERE t1id = 1
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200603/1
|||The issue would become very straightforward and simple only if one stops
using the phrase 'a CASE statement' because there is no such a thing, and
starts using 'a CASE expression.'
Linchi
"cbrichards via droptable.com" wrote:

> Is it possible to have multiple expressions, or dml expressions in a case
> statement similar to the following?
> DECLARE @.path_f1 nvarchar(200)
> DECLARE @.path_f2 nvarchar(200)
> SELECT
> @.path_f1 = output_path_file1,
> @.path_f2 = output_path_file2
> FROM t1
> WHERE t1id = 1
> SELECT
> CASE
> WHEN f1_status = 0 THEN
> UPDATE t1
> SET f1_status = 1, f1_created = getdate()
> WHERE t1id = 1
> EXEC usp_t1 @.path_f1, null
> WHEN f1_status = 1 AND f2_status = 0 THEN
> UPDATE t1
> SET file2_status = 1, file2_created = getdate()
> WHERE t1id = 1
> EXEC usp_t1 @.path_f2, null
> ELSE
> RAISERROR ('Error Message', 16,1)
> END
> FROM t1
> WHERE t1id = 1
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200603/1
>

Case statement

Is it possible to have multiple expressions, or dml expressions in a case
statement similar to the following?
DECLARE @.path_f1 nvarchar(200)
DECLARE @.path_f2 nvarchar(200)
SELECT
@.path_f1 = output_path_file1,
@.path_f2 = output_path_file2
FROM t1
WHERE t1id = 1
SELECT
CASE
WHEN f1_status = 0 THEN
UPDATE t1
SET f1_status = 1, f1_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f1, null
WHEN f1_status = 1 AND f2_status = 0 THEN
UPDATE t1
SET file2_status = 1, file2_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f2, null
ELSE
RAISERROR ('Error Message', 16,1)
END
FROM t1
WHERE t1id = 1
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200603/1Not the way you're doing it. You can have something along the lines of:
UPDATE t1
SET
f1_status = CASE
WHEN f1_status = 0 THEN 1
ELSE f1_status END
, f1_created = CASE
WHEN f1_status = 0 THEN getdate()
ELSE f1_created END
WHERE t1id = 1
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:5df3ff92c5d28@.uwe...
Is it possible to have multiple expressions, or dml expressions in a case
statement similar to the following?
DECLARE @.path_f1 nvarchar(200)
DECLARE @.path_f2 nvarchar(200)
SELECT
@.path_f1 = output_path_file1,
@.path_f2 = output_path_file2
FROM t1
WHERE t1id = 1
SELECT
CASE
WHEN f1_status = 0 THEN
UPDATE t1
SET f1_status = 1, f1_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f1, null
WHEN f1_status = 1 AND f2_status = 0 THEN
UPDATE t1
SET file2_status = 1, file2_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f2, null
ELSE
RAISERROR ('Error Message', 16,1)
END
FROM t1
WHERE t1id = 1
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200603/1|||The issue would become very straightforward and simple only if one stops
using the phrase 'a CASE statement' because there is no such a thing, and
starts using 'a CASE expression.'
Linchi
"cbrichards via droptable.com" wrote:

> Is it possible to have multiple expressions, or dml expressions in a case
> statement similar to the following?
> DECLARE @.path_f1 nvarchar(200)
> DECLARE @.path_f2 nvarchar(200)
> SELECT
> @.path_f1 = output_path_file1,
> @.path_f2 = output_path_file2
> FROM t1
> WHERE t1id = 1
> SELECT
> CASE
> WHEN f1_status = 0 THEN
> UPDATE t1
> SET f1_status = 1, f1_created = getdate()
> WHERE t1id = 1
> EXEC usp_t1 @.path_f1, null
> WHEN f1_status = 1 AND f2_status = 0 THEN
> UPDATE t1
> SET file2_status = 1, file2_created = getdate()
> WHERE t1id = 1
> EXEC usp_t1 @.path_f2, null
> ELSE
> RAISERROR ('Error Message', 16,1)
> END
> FROM t1
> WHERE t1id = 1
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200603/1
>sql

Case sensitivity

Hello,
I'm writing a function witch from an string (nvarchar) gets the characters
and converts them. I have the problem with case sensitivity. I get the
results in small letters but I need them to be in the exact case as the inpu
t
values, and must not change the SQL Server's settings. I've tryed with
char(number), nchar(number), but results are small letters. Can somebody
please help me with my problem?
ThanksHi,
see the following code.. use this logic in your function
DECLARE @.c CHAR
DECLARE @.d CHAR
SET @.c = 'a'
SET @.d = 'B'
DECLARE @.a VARCHAR(126)
SET @.a = ''
SET @.a = @.a + @.c + @.d
PRINT @.a
I hope that this will help you
Regards
Sivakumar
"RioDD" wrote:

> Hello,
> I'm writing a function witch from an string (nvarchar) gets the characters
> and converts them. I have the problem with case sensitivity. I get the
> results in small letters but I need them to be in the exact case as the in
put
> values, and must not change the SQL Server's settings. I've tryed with
> char(number), nchar(number), but results are small letters. Can somebody
> please help me with my problem?
> Thanks|||Check out COLLATE in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"RioDD" <RioDD@.discussions.microsoft.com> wrote in message
news:D1E3AE60-365A-4B64-8408-E7C118D9F84C@.microsoft.com...
Hello,
I'm writing a function witch from an string (nvarchar) gets the characters
and converts them. I have the problem with case sensitivity. I get the
results in small letters but I need them to be in the exact case as the
input
values, and must not change the SQL Server's settings. I've tryed with
char(number), nchar(number), but results are small letters. Can somebody
please help me with my problem?
Thanks|||Sorry it didn't help me. The problem is when I use
if @.c='a'
it returns true for both 'a' and 'A'
"Subramaniam Sivakumar" wrote:
> Hi,
> see the following code.. use this logic in your function
> DECLARE @.c CHAR
> DECLARE @.d CHAR
> SET @.c = 'a'
> SET @.d = 'B'
> DECLARE @.a VARCHAR(126)
> SET @.a = ''
> SET @.a = @.a + @.c + @.d
> PRINT @.a
> I hope that this will help you
> Regards
> Sivakumar
> "RioDD" wrote:
>|||try this
IF CONVERT(varbinary(64), @.c) = CONVERT(varbinary(64), 'A')
"RioDD" wrote:

> Hello,
> I'm writing a function witch from an string (nvarchar) gets the characters
> and converts them. I have the problem with case sensitivity. I get the
> results in small letters but I need them to be in the exact case as the in
put
> values, and must not change the SQL Server's settings. I've tryed with
> char(number), nchar(number), but results are small letters. Can somebody
> please help me with my problem?
> Thanks|||Thanks, this helped me
"Subramaniam Sivakumar" wrote:
> try this
> IF CONVERT(varbinary(64), @.c) = CONVERT(varbinary(64), 'A')
>
> "RioDD" wrote:
>|||This will only work if the string is less than 64 bits. You really should
look up the various collations in books online, that is the correct way to d
o
this.|||Hi,
no... you can use varbinary upto 8000.
"Scott Simons" wrote:

> This will only work if the string is less than 64 bits. You really should
> look up the various collations in books online, that is the correct way to
do
> this.

CASE Problem in Stored Procedure

Can anyone see a problem with this stored procedure.
When I do this one it works fine.

CREATE Procedure SS_SoftList
(
@.CompanyID nvarchar(10),
@.Order varchar(20)
)
As

SELECT
SS_Soft_deploy.Softwarename,
( COUNT(SS_Soft_deploy.Softwarename)) as recordcount

FROM
SS_Soft_deploy

WHERE
SS_Soft_deploy.CompanyID = @.CompanyID

GROUP BY
SS_Soft_deploy.Softwarename

ORDER BY recordcount
--CASE WHEN @.Order = 'softname' THEN Softwarename END,
--CASE WHEN @.Order = 'count' THEN recordcount END
GO

However when I un-rem the CASE statement
ORDER BY
CASE WHEN @.Order = 'softname' THEN Softwarename END,
CASE WHEN @.Order = 'count' THEN recordcount END
GO

It reports an error that says "Invalid Column Name recordcount"
Any Ideas??Recordcount is an alias. You cannot sort on that using the alias.

CASE WHEN @.Order = 'softname' THEN Softwarename END,
CASE WHEN @.Order = 'count' THEN ( COUNT(SS_Soft_deploy.Softwarename)) END

|||Why does it work when I do the
ORDER BY recordcount
??|||Hmm. I did not realize that would work using the alias in the ORDER BY, but apparently it does. It does not work inside the case statement because that is a different expression, and that expression is unaware of the alias. Repeating the expression, however, works fine. If the expression were terribly complex, you could alternately create a user defined function and use that for the row and the ORDER BY (knowing that performance would likely be less than great).

Cant use nvarchar in BETWEEN condition

Greetings all,

I am trying to do an SQL query with BETWEEN in my where condition. Problem is the values I am checking are nvarchar since they also have letters in them. This results in a query that produces nothing. Here is my query:

CREATE PROCEDURE dbo.qryComplete
(
@.From nvarchar(50),
@.To nvarchar(50)
)

AS SELECT dbo.[tbl Object].[Object ID],
dbo.[tbl Object].[Object Name], dbo.[tbl Object].[Object Type],
dbo.[tbl Object].Category, dbo.[tbl Object].Subcategory,
dbo.[tbl Object].[Group], dbo.[tbl Object].[Accession Number],
dbo.[tbl Object].[Physical Description],
dbo.[tbl Object].[Acquisition Method],
dbo.[tbl Object].[Acquisition Date],
dbo.[tbl Object].[Acquisition Source], dbo.[tbl Object].Status,
dbo.[tbl Object].[Number of Parts or Components],
dbo.[tbl Object].[Description of Parts or Components],
dbo.[tbl Object].Title, dbo.[tbl People].[Persons Name],
dbo.[tbl People].Address, dbo.[tbl People].City, dbo.[tbl People].Province, dbo.[tbl People].Country,
dbo.[tbl People].[Postal Code], dbo.[tbl People].Biography,
dbo.[tbl People].Status AS Dead, dbo.[tbl People].Phone
FROM dbo.[tbl Object] LEFT OUTER JOIN
dbo.[tbl People] ON [tbl Object].[Acquisition Source] = [tbl People].[Persons Name]
WHERE (dbo.[tbl Object].[Accession Number] BETWEEN @.From AND @.To) AND (dbo.[tbl Object].Status LIKE 'A%' OR
dbo.[tbl Object].Status LIKE 'B%' OR
dbo.[tbl Object].Status IS NULL)
ORDER BY dbo.[tbl Object].[Accession Number]
GO

If I replace Accession Number with Object ID in the WHERE condition, this query works fine. With the accession number however it returns no results. I have also tried using convert and cast functions to try to convert the values to numeric but I get an error saying it can't convert to type numeric. I've also tried replacing the BETWEEN with comparison operators (>=) but it still doesn't work. Some examples of the Accession numbers we are using:
CF1968.5
CF1968.7A-E
CS1986.2.2

Any suggestions?Can you post @.FROM and @.To as well and then show us what you think the result set should be|||Check out this example, and let me know what you think

USE Northwind
GO
sp_help Orders
GO
DECLARE @.From char(1), @.To char(1)
SELECT @.From = 'A', @.To = 'B'
SELECT * FROM Orders WHERE ShipName BETWEEN @.From AND @.To
GO

But, really BETWEEN with chars?|||Yes I agree that doing it with nvarchars is not a very good way of doing it. What I am working with is a hand-me down of a hand-me down :).

As a bit more background this file is an ADP accessing a MS SQL Server database. It was originally done in Access but we converted it so that we can eventually use it on the web. I've gotten rid of most of the conversion issues including getting the forms to work properly and all I have left is these pesky queries/reports. Apparently access had no problem using BETWEEN with two nvarchar values but MS SQL seems to be a bit pickier.

Examples of @.From and @.To I've been trying to use are:
@.From : CF1968.5
@.To : CF1968.20

I would expect this to return records with the following Accession numbers:

Awwww crap!

Okay now I feel really stupid. When I did the sort I noticed that CF1968.20 actually comes before CF1968.5. It puts it in alphabetical order after all, not numerical. So when I redid the query between CF1968.5 and CF1968.505 the query returned several values.

Well thanks a lot for your help. Your requests were instrumental in solving my problem for me.

Have a great day.|||glad I could help...

Oh, and I feel for you...just got 1 dumped on me...luckily they don't want to convert...

touching any code in this thing would cause MAJOR explosions...

Keeping it afloat...that's another matter...