CASE Statement

Hello,
I have a varchar field (ApprovalStatus) that can have 3 results (Approved,
Denied or NULL). On my web page, I have a dropdown box which the user can
select 3 items (Approved, Denied or Pending). When they choose "Pending",
I want to retrieve the fields that are NULL. I've tried the following WHERE
statement, but I can't capture the NULL fields.
@.strParm03 can equal "All, Approved, Denied or NULL)
WHERE
ApprovalStatus LIKE CASE @.strParm03 WHEN 'all' THEN '%'
WHEN 'Pending' THEN NULL
ELSE @.strParm03 END
Any help with this would be appreciated.
--
Thanks in advance,
sck10I would use a script like:
[code]
where @.strParm03 = 'All'
or (@.strParm03='Approved' and ApprovalStatus='Approved')
or (@.strParm03='Denied' and ApprovalStatus='Denied')
or (@.strParm03='Pending' and ApprovalStatus is null)
[/code]
or
[code]
where @.strParm03 = 'All'
or (@.strParm03='Approved' and ApprovalStatus='Approved')
or (@.strParm03='Denied' and ApprovalStatus='Denied')
or (@.strParm03='Pending' and isnull(ApprovalStatus,'') ='')
[/code]
HTH,
Cristian Lefter, SQL Server MVP
"sck10" <sck10@.online.nospam> wrote in message
news:Oafy0raXFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have a varchar field (ApprovalStatus) that can have 3 results (Approved,
> Denied or NULL). On my web page, I have a dropdown box which the user can
> select 3 items (Approved, Denied or Pending). When they choose
> "Pending",
> I want to retrieve the fields that are NULL. I've tried the following
> WHERE
> statement, but I can't capture the NULL fields.
> @.strParm03 can equal "All, Approved, Denied or NULL)
> WHERE
> ApprovalStatus LIKE CASE @.strParm03 WHEN 'all' THEN '%'
> WHEN 'Pending' THEN NULL
> ELSE @.strParm03 END
> Any help with this would be appreciated.
> --
> Thanks in advance,
> sck10
>

CASE Statement

Hello,
I have a varchar field (ApprovalStatus) that can have 3 results (Approved,
Denied or NULL). On my web page, I have a dropdown box which the user can
select 3 items (Approved, Denied or Pending). When they choose "Pending",
I want to retrieve the fields that are NULL. I've tried the following WHERE
statement, but I can't capture the NULL fields.
@.strParm03 can equal "All, Approved, Denied or NULL)
WHERE
ApprovalStatus LIKE CASE @.strParm03 WHEN 'all' THEN '%'
WHEN 'Pending' THEN NULL
ELSE @.strParm03 END
Any help with this would be appreciated.
Thanks in advance,
sck10
I would use a script like:
[code]
where @.strParm03 = 'All'
or (@.strParm03='Approved' and ApprovalStatus='Approved')
or (@.strParm03='Denied' and ApprovalStatus='Denied')
or (@.strParm03='Pending' and ApprovalStatus is null)
[/code]
or
[code]
where @.strParm03 = 'All'
or (@.strParm03='Approved' and ApprovalStatus='Approved')
or (@.strParm03='Denied' and ApprovalStatus='Denied')
or (@.strParm03='Pending' and isnull(ApprovalStatus,'') ='')
[/code]
HTH,
Cristian Lefter, SQL Server MVP
"sck10" <sck10@.online.nospam> wrote in message
news:Oafy0raXFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have a varchar field (ApprovalStatus) that can have 3 results (Approved,
> Denied or NULL). On my web page, I have a dropdown box which the user can
> select 3 items (Approved, Denied or Pending). When they choose
> "Pending",
> I want to retrieve the fields that are NULL. I've tried the following
> WHERE
> statement, but I can't capture the NULL fields.
> @.strParm03 can equal "All, Approved, Denied or NULL)
> WHERE
> ApprovalStatus LIKE CASE @.strParm03 WHEN 'all' THEN '%'
> WHEN 'Pending' THEN NULL
> ELSE @.strParm03 END
> Any help with this would be appreciated.
> --
> Thanks in advance,
> sck10
>

CASE Statement

Hello,
I have a varchar field (ApprovalStatus) that can have 3 results (Approved,
Denied or NULL). On my web page, I have a dropdown box which the user can
select 3 items (Approved, Denied or Pending). When they choose "Pending",
I want to retrieve the fields that are NULL. I've tried the following WHERE
statement, but I can't capture the NULL fields.
@.strParm03 can equal "All, Approved, Denied or NULL)
WHERE
ApprovalStatus LIKE CASE @.strParm03 WHEN 'all' THEN '%'
WHEN 'Pending' THEN NULL
ELSE @.strParm03 END
Any help with this would be appreciated.
--
Thanks in advance,
sck10I would use a script like:
[code]
where @.strParm03 = 'All'
or (@.strParm03='Approved' and ApprovalStatus='Approved')
or (@.strParm03='Denied' and ApprovalStatus='Denied')
or (@.strParm03='Pending' and ApprovalStatus is null)
[/code]
or
[code]
where @.strParm03 = 'All'
or (@.strParm03='Approved' and ApprovalStatus='Approved')
or (@.strParm03='Denied' and ApprovalStatus='Denied')
or (@.strParm03='Pending' and isnull(ApprovalStatus,'') ='')
[/code]
HTH,
Cristian Lefter, SQL Server MVP
"sck10" <sck10@.online.nospam> wrote in message
news:Oafy0raXFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have a varchar field (ApprovalStatus) that can have 3 results (Approved,
> Denied or NULL). On my web page, I have a dropdown box which the user can
> select 3 items (Approved, Denied or Pending). When they choose
> "Pending",
> I want to retrieve the fields that are NULL. I've tried the following
> WHERE
> statement, but I can't capture the NULL fields.
> @.strParm03 can equal "All, Approved, Denied or NULL)
> WHERE
> ApprovalStatus LIKE CASE @.strParm03 WHEN 'all' THEN '%'
> WHEN 'Pending' THEN NULL
> ELSE @.strParm03 END
> Any help with this would be appreciated.
> --
> Thanks in advance,
> sck10
>

Case Sensitive VarChar comparsion???

Hi all,
I wonder if it is possible to use case sensitive varchar comparsion in a query?
As I have tried and the varchar comparsion is non-case sensitive.
Thanks
MEif (convert (varbinary,'Enigma')= convert (varbinary,'enigma'))
Print 'Done'
else
Print 'Not Done'

if (convert (varbinary,'Enigma')= convert (varbinary,'Enigma'))
Print 'Done'
else
Print 'Not Done'|||For a case-sensitive compare, I'd use:IF 'PatP' = 'patp' COLLATE SQL_Latin1_General_CP1250_CS_AS
PRINT 'Match'
ELSE
PRINT 'Different'You can get a list of collations that a given server will support using:SELECT *
FROM ::fn_helpcollations()-PatP|||Pat ... any reason you would not recommend a cast to varbinary|||Two reasons.

First of all, if you want a string compare, I think you should do a string compare... There are lots of ways to compare strings, and keeping that flexibility is important to me. Today the users want case sensitivity, tomorrow they'll want the strings ordered using the Spanish dictionary, which is easy using a collation.

Second, casting to BINARY has a cost, and in large jobs that cost can get high. Why incur overhead that gives no real benefit?

-PatP|||Thanks ...
Learned something new again ...

case expression stor proc, need some help

ALTER PROCEDURE dbo.TEST_TOTALCALLS
(
@.varDate as varchar (255),
@.StartDate as datetime,
@.EndDate as datetime
)
AS

SELECT
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
WHEN 'Month' Then DATENAME(mm, CALLSTARTTIME)
END,
COUNT(*) as 'Total Calls'
FROM CALLMASTER
WHERE (COMMERCIALS = '1') AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)

GROUP BY
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
WHEN 'Month' Then DATEPART(mm, CALLSTARTTIME), DATENAME(mm, CALLSTARTTIME) ' <--this part gave me an error, because of the comma,
END
ORDER BY
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
WHEN 'Month' Then DATEPART(mm, CALLSTARTTIME)
END

The month case is giving me an error. I think it has to do with two expressions in one line.
Anyone know how to combine that into 1 expression? or is there away to work around it?
As I would like to display the month as Name, but group and sort by number.
Thx!~Are you saying you don't get the data you want if you remove the datename part from your group by clause? You are getting the month name out by the select part of your procedure and I don't see the need to also group by it if you only want to group by number.|||well, I am trying to get data displayed in the name of the month, but not in ABC order.
i.e.

January
Feb
March

instead of ABC order,
April
December
Febuary

I did the sql before grouping them together.
And this worked,

SELECT
DATENAME(mm, CALLSTARTTIME),
COUNT(*) as 'Total Calls'
FROM CALLMASTER
WHERE (COMMERCIALS = '1') AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)
GROUP BY
DATEPART(mm, CALLSTARTTIME), DATENAME(mm, CALLSTARTTIME)
ORDER BY
DATEPART(mm, CALLSTARTTIME)

Yet, the GROUP BY clause consist oftwoexpressions for it to function, (from my understanding)
and I don't know how to make that clause work in a CASE expression.

Thx in advance~|||oh yea, this doesn't work from my understanding:

SELECT
DATENAME(mm, CALLSTARTTIME),
COUNT(*) as 'Total Calls'
FROM CALLMASTER
WHERE (COMMERCIALS = '1') AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)
GROUP BY
DATENAME(mm, CALLSTARTTIME)
ORDER BY
DATEPART(mm, CALLSTARTTIME)|||How about this:-

GROUP BY
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
WHEN 'Month' Then DATEPART(mm, CALLSTARTTIME)
END,
CASE @.varDate
WHEN 'Year' Then ??
WHEN 'Quarter' Then ??
WHEN 'Month' Then DATENAME(mm, CALLSTARTTIME)
END

I think it would only work if you could put something in for the Year and Quarter too (where the ?? are). Might not work at all.
The only other thing would be to perhaps use a sql if to have 2 different selects, one for month with case no longer needed and one for the other 2 using case:-

if @.varDate='Month'
begin
SELECT DATENAME(mm, CALLSTARTTIME), COUNT(*) as 'Total Calls'
FROM CALLMASTER
WHERE (COMMERCIALS = '1') AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)
GROUP BY
DATEPART(mm, CALLSTARTTIME), DATENAME(mm, CALLSTARTTIME)
ORDER BY
DATEPART(mm, CALLSTARTTIME)
end
else
begin
SELECT
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
END,
COUNT(*) as 'Total Calls'
FROM CALLMASTER
WHERE (COMMERCIALS = '1') AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)

GROUP BY
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
END
ORDER BY
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
END
end|||Thank you Brian. That will work.

Side Question: is there a way to name the heading by case?

e.g.
SELECT
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME) <--This will have As 'Year'
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)<--This will have As 'Quarter'
WHEN 'Month' Then DATENAME(mm, CALLSTARTTIME) <--This will have as 'Month'
END,|||Yes there is:-

SELECT
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME) <--This will have As 'Year'
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)<--This will have As 'Quarter'
WHEN 'Month' Then DATENAME(mm, CALLSTARTTIME) <--This will have as 'Month'
END as myheadingname|||SELECT
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME) <--This will have As 'Year'
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)<--This will have As 'Quarter'
WHEN 'Month' Then DATENAME(mm, CALLSTARTTIME) <--This will have as 'Month'
END as myheadingname

Can myheadingname varies by case? like can it be a parameter/variable,and it will display different headings depending on the case selected.

e.g.
When @.varDate = 'Year', the column header will be 'Year'.
When @.varDate = 'Month', the column header will be 'Month'.
When @.varDate = 'Quarter', the column header will be 'Quarter'

the above example, as myheadingname is a generalized header. It will not differ no matter what @.varDate is.|||I don't think you can but no harm in trying something like that:-

SELECT
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME) As 'Year'
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME) As 'Quarter'
WHEN 'Month' Then DATENAME(mm, CALLSTARTTIME) as 'Month'
END

You will probably get an error if it doesn't work.|||yup, got an error.|||Well other than that you can expand the if for each possibility and do without the case.

case expression in where clause and null's

Using SQL Server 2005
I have a simple table example with two columns: FirstName varchar(20) and
LastName varchar(20)
I am doing something like this in a stored procedure where @.firstname and
@.lastname are passed in and @.lastname could be null
select * from table where FirstName = @.firstname and LastName =
COALESCE(@.LastName, LastName)
I am also using "set ansi_nulls off".
The query should give names where LastName is null if @.Lastname = null but
that's not happening. Why?
John DalbergBecause COALESCE returns the first non-NULL value. If @.LastName is null,
then it won't return @.LastName, it will Return LastName (the column, not the
variable).
Essentially making your stmt:
select * from table where FirstName = @.firstname and LastName = LastName
or rather
select * from table where FirstName = @.firstname
"John Dalberg" <nospam@.nospam.sss> wrote in message
news:20060405191639.387$oV@.newsreader.com...
> Using SQL Server 2005
> I have a simple table example with two columns: FirstName varchar(20) and
> LastName varchar(20)
> I am doing something like this in a stored procedure where @.firstname and
> @.lastname are passed in and @.lastname could be null
> select * from table where FirstName = @.firstname and LastName =
> COALESCE(@.LastName, LastName)
> I am also using "set ansi_nulls off".
> The query should give names where LastName is null if @.Lastname = null but
> that's not happening. Why?
> John Dalberg|||>The query should give names where LastName is null if @.Lastname = null but
>that's not happening. Why?
It sounds like this might be confusion over how NULL works.
If table.LastName IS NULL, and @.LastName IS NULL, then
COALESCE(@.LastName, LastName) will resolve to NULL. In that situation
the test:
LastName = COALESCE(@.LastName, LastName)
resolves to:
NULL = NULL
Which comparison will never resolve to TRUE. NULL is never equal to
anything, including another NULL. Consider these comparisons:
NULL = 'banana'
NULL <> 'banana'
NULL = NULL
NULL <> NULL
None of those can ever be resolved as TRUE, because equality (or
inequality) can only result from comparing something to something.
NULL is nothing, and can not be compared at all.
Roy Harvey
Beacon Falls, CT
On 05 Apr 2006 23:06:02 GMT, nospam@.nospam.sss (John Dalberg) wrote:

>Using SQL Server 2005
>I have a simple table example with two columns: FirstName varchar(20) and
>LastName varchar(20)
>I am doing something like this in a stored procedure where @.firstname and
>@.lastname are passed in and @.lastname could be null
>select * from table where FirstName = @.firstname and LastName =
>COALESCE(@.LastName, LastName)
>I am also using "set ansi_nulls off".
>The query should give names where LastName is null if @.Lastname = null but
>that's not happening. Why?
>John Dalberg|||Roy Harvey <roy_harvey@.snet.net> wrote:
> It sounds like this might be confusion over how NULL works.
> If table.LastName IS NULL, and @.LastName IS NULL, then
> COALESCE(@.LastName, LastName) will resolve to NULL. In that situation
> the test:
> LastName = COALESCE(@.LastName, LastName)
> resolves to:
> NULL = NULL
> Which comparison will never resolve to TRUE. NULL is never equal to
> anything, including another NULL. Consider these comparisons:
> NULL = 'banana'
> NULL <> 'banana'
> NULL = NULL
> NULL <> NULL
> None of those can ever be resolved as TRUE, because equality (or
> inequality) can only result from comparing something to something.
> NULL is nothing, and can not be compared at all.
But when you have set ansi_nulls off and run:
select * from table where lastname = null, it returns rows where lastname=
null
Doesn't that statement translate to:
select * from table where null = null ?
and when you set ansi_nulls on
one needs to write it as: select * from table where lastname is null ?
John Dalberg|||take a look at this
declare @.v1 int,@.v2 int
select @.v1 = null,@.v2 = null
if @.v1 =@.v2
print 'equal'
else
print 'not equal'
go
declare @.v1 int,@.v2 int
select @.v1 = null,@.v2 = null
if @.v1 is null and @.v2 is null
print 'Both null'
else
print 'both not null'
go
set ansi_nulls off
go
declare @.v1 int,@.v2 int
select @.v1 = null,@.v2 = null
if @.v1 =@.v2
print 'equal'
else
print 'not equal'
go
set ansi_nulls on
go
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||"Paul Wehland" <PaulWe@.REMOVE-ME.Avanade.com> wrote:
> Because COALESCE returns the first non-NULL value. If @.LastName is null,
> then it won't return @.LastName, it will Return LastName (the column, not
> the variable).
> Essentially making your stmt:
> select * from table where FirstName = @.firstname and LastName =
> LastName or rather
> select * from table where FirstName = @.firstname
What does COALESCE return in this case when there's no none null values?
(not that it makes sense)
select * from table where FirstName = @.firstname and LastName =
COALESCE(@.LastName, @.LastName)
Would that translate to:
select * from table where FirstName = @.firstname and LastName = null ?
Doesn't 'set ansi_null off' make 'null =null' evaluate to true?
Anyways, I need the where clause to include the lastname if @.lastname has a
value and return null lastnames rows if @.lastname is null. I couldn't find
a way to do it in a CASE expression. I can do it in a dynamic sql.
John Dalberg
> "John Dalberg" <nospam@.nospam.sss> wrote in message
> news:20060405191639.387$oV@.newsreader.com...|||>>What does COALESCE return in this case when there's no none null values?
(not that it makes sense)
COALESCE will return the first non NULL value
Here is an example
declare @.v1 int,@.v2 int,@.v3 int,@.v4 int
select @.v1 = null,@.v2 = null,@.v3 =4,@.v4 =8
select coalesce(@.v1,@.v2,@.v3,@.v4)
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||"SQL" <denis.gobo@.gmail.com> wrote:
> take a look at this
> declare @.v1 int,@.v2 int
> select @.v1 = null,@.v2 = null
> if @.v1 =@.v2
> print 'equal'
> else
> print 'not equal'
> go
> declare @.v1 int,@.v2 int
> select @.v1 = null,@.v2 = null
> if @.v1 is null and @.v2 is null
> print 'Both null'
> else
> print 'both not null'
> go
> set ansi_nulls off
> go
> declare @.v1 int,@.v2 int
> select @.v1 = null,@.v2 = null
> if @.v1 =@.v2
> print 'equal'
> else
> print 'not equal'
> go
> set ansi_nulls on
> go
I understand the principles. That's why I included set ansi_nulls off in my
clarification.|||It is easier to communicate using DDL. I've added DDL at the end of this tex
t so we can use that
from here on.
Let me see if I can re-phrase your question:
You have some rows which has NULL on the lastname column. You want to find t
hem using ANSI_NULLS OFF
and by passing in NULL in the @.lastname parameter of your stored procedure.
So, in the query, you
have the following condition:
AND LastName = COALESCE(@.LastName, LastName)
So, if if you pass NULL for the @.lastname parameter, the condition will tran
slate to:
AND LastName = LastName
And you wonder why that will not return the rows where you have NULL in the
lastname column. Is that
correct?
If so, read in Books Online about SET ANSI_NULLS OFF. It only comments about
comparsions between a
column and NULL, not between two columns where each has NULL. I guess that t
his is how Sybase
defined it some 20 years ago, and MS has kapt this behavior. You could do a
BOL feedback and ask
them to clarify this in the 2005 BOL.
DDL:
USE tempdb
CREATE TABLE t(firstname varchar(30) not null, lastname varchar(30) null)
insert into t (firstname, lastname)
VALUES('John', 'Dalberg')
insert into t (firstname, lastname)
VALUES('Franz', NULL)
SET ANSI_NULLS OFF
GO
CREATE PROC p
@.firstname varchar(30), @.lastname varchar(30)
AS
SELECT firstname, lastname
from t
where FirstName = @.firstname
AND LastName = COALESCE(@.LastName, LastName)
GO
EXEC p 'John', 'Dalberg'
EXEC p 'John', NULL
EXEC p 'Franz', NULL
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John Dalberg" <nospam@.nospam.sss> wrote in message news:20060406121819.765$iB@.newsreader.c
om...
> Roy Harvey <roy_harvey@.snet.net> wrote:
> But when you have set ansi_nulls off and run:
> select * from table where lastname = null, it returns rows where lastname=
> null
> Doesn't that statement translate to:
> select * from table where null = null ?
> and when you set ansi_nulls on
> one needs to write it as: select * from table where lastname is null ?
> John Dalberg|||"Paul Wehland" <PaulWe@.REMOVE-ME.Avanade.com> wrote:
> Because COALESCE returns the first non-NULL value. If @.LastName is null,
> then it won't return @.LastName, it will Return LastName (the column, not
> the variable).
> Essentially making your stmt:
> select * from table where FirstName = ffirstname and LastName =
> LastName or rather
Right and that's the way it should work so when you have ansi_nulls off,
lastname = lastname will return rows where lastname is null and it will not
return these rows if set ansi_nulls on. I am missing what was wrong in my
statement.
so:
set ansi_nulls off;select * from table where FirstName = firstname and
LastName =lastname
&
select * from table
both return the same # of rows regardless whether lastname is null or not.
John Dalbergsql

carrier return + line feed in a varchar

Hi

how can I split a line into 2 lines in t-sql. etc
Set @.text1 = 'here's line one' + (carriere return + line feed) + 'here's line two'

I will be using it to send a mail from sql server (2000 sp4), so i can build a nice looking mail

> Set @.text1 = 'here's line one' + (carriere return + line feed) + 'here's

> line two'

Set @.text1 = 'here''s one line' + CHAR(13) + CHAR(10) + 'here''s line two';

|||thanks a lot

Carriage Return Bug in Management Studio?

I'm using SQL Server 2005 Manage Studio to edit some varchar and text fields
I have in my database. I want to insert carriage returns in my text, and I
tried CTRL+Enter which works fine in SQL 2000, but it doesn't seem to work in
Management Studio. Further more, when I import data from my SQL 2000 and then
open the table for editing in Management Studio I find that all the carriage
returns are not visible. I say not visible, because I know they're still
there because my application reads them fine. Is this a UI bug in Management
Studio?!!! Anyone knows of a fix or work around? Thanks.
Hi, Waleed,
+char(13)+char(10) could insert a carriage return and line feed.
Ta,
Yifei
"Waleed Abdulla xrules org>" <waleed_abdulla <atdot> wrote in message
news:E59CC994-B548-45CE-837A-E6C71A366C5F@.microsoft.com...
> I'm using SQL Server 2005 Manage Studio to edit some varchar and text
fields
> I have in my database. I want to insert carriage returns in my text, and I
> tried CTRL+Enter which works fine in SQL 2000, but it doesn't seem to work
in
> Management Studio. Further more, when I import data from my SQL 2000 and
then
> open the table for editing in Management Studio I find that all the
carriage
> returns are not visible. I say not visible, because I know they're still
> there because my application reads them fine. Is this a UI bug in
Management
> Studio?!!! Anyone knows of a fix or work around? Thanks.
|||Yifei,
Thanks, but you're answering the wrong question. As I mentioned, reading
and inserting carriage returns programmatically (char 13 + char 10) is no
problem. My issue is in the UI when trying to type in carriage returns in the
data directly in the table view in Management Studio.
Waleed
"Yifei Jiang" wrote:

> Hi, Waleed,
> +char(13)+char(10) could insert a carriage return and line feed.
> Ta,
> Yifei
> "Waleed Abdulla xrules org>" <waleed_abdulla <atdot> wrote in message
> news:E59CC994-B548-45CE-837A-E6C71A366C5F@.microsoft.com...
> fields
> in
> then
> carriage
> Management
>
>

Carriage Return Bug in Management Studio?

I'm using SQL Server 2005 Manage Studio to edit some varchar and text fields
I have in my database. I want to insert carriage returns in my text, and I
tried CTRL+Enter which works fine in SQL 2000, but it doesn't seem to work in
Management Studio. Further more, when I import data from my SQL 2000 and then
open the table for editing in Management Studio I find that all the carriage
returns are not visible. I say not visible, because I know they're still
there because my application reads them fine. Is this a UI bug in Management
Studio?!!! Anyone knows of a fix or work around? Thanks.Hi, Waleed,
+char(13)+char(10) could insert a carriage return and line feed.
Ta,
Yifei
"Waleed Abdulla xrules org>" <waleed_abdulla <atdot> wrote in message
news:E59CC994-B548-45CE-837A-E6C71A366C5F@.microsoft.com...
> I'm using SQL Server 2005 Manage Studio to edit some varchar and text
fields
> I have in my database. I want to insert carriage returns in my text, and I
> tried CTRL+Enter which works fine in SQL 2000, but it doesn't seem to work
in
> Management Studio. Further more, when I import data from my SQL 2000 and
then
> open the table for editing in Management Studio I find that all the
carriage
> returns are not visible. I say not visible, because I know they're still
> there because my application reads them fine. Is this a UI bug in
Management
> Studio?!!! Anyone knows of a fix or work around? Thanks.|||Yifei,
Thanks, but you're answering the wrong question. As I mentioned, reading
and inserting carriage returns programmatically (char 13 + char 10) is no
problem. My issue is in the UI when trying to type in carriage returns in the
data directly in the table view in Management Studio.
Waleed
"Yifei Jiang" wrote:
> Hi, Waleed,
> +char(13)+char(10) could insert a carriage return and line feed.
> Ta,
> Yifei
> "Waleed Abdulla xrules org>" <waleed_abdulla <atdot> wrote in message
> news:E59CC994-B548-45CE-837A-E6C71A366C5F@.microsoft.com...
> > I'm using SQL Server 2005 Manage Studio to edit some varchar and text
> fields
> > I have in my database. I want to insert carriage returns in my text, and I
> > tried CTRL+Enter which works fine in SQL 2000, but it doesn't seem to work
> in
> > Management Studio. Further more, when I import data from my SQL 2000 and
> then
> > open the table for editing in Management Studio I find that all the
> carriage
> > returns are not visible. I say not visible, because I know they're still
> > there because my application reads them fine. Is this a UI bug in
> Management
> > Studio?!!! Anyone knows of a fix or work around? Thanks.
>
>

Carriage Return Bug in Management Studio?

I'm using SQL Server 2005 Manage Studio to edit some varchar and text fields
I have in my database. I want to insert carriage returns in my text, and I
tried CTRL+Enter which works fine in SQL 2000, but it doesn't seem to work i
n
Management Studio. Further more, when I import data from my SQL 2000 and the
n
open the table for editing in Management Studio I find that all the carriage
returns are not visible. I say not visible, because I know they're still
there because my application reads them fine. Is this a UI bug in Management
Studio?!!! Anyone knows of a fix or work around? Thanks.Hi, Waleed,
+char(13)+char(10) could insert a carriage return and line feed.
Ta,
Yifei
"Waleed Abdulla xrules org>" <waleed_abdulla <atdot> wrote in message
news:E59CC994-B548-45CE-837A-E6C71A366C5F@.microsoft.com...
> I'm using SQL Server 2005 Manage Studio to edit some varchar and text
fields
> I have in my database. I want to insert carriage returns in my text, and I
> tried CTRL+Enter which works fine in SQL 2000, but it doesn't seem to work
in
> Management Studio. Further more, when I import data from my SQL 2000 and
then
> open the table for editing in Management Studio I find that all the
carriage
> returns are not visible. I say not visible, because I know they're still
> there because my application reads them fine. Is this a UI bug in
Management
> Studio?!!! Anyone knows of a fix or work around? Thanks.|||Yifei,
Thanks, but you're answering the wrong question. As I mentioned, reading
and inserting carriage returns programmatically (char 13 + char 10) is no
problem. My issue is in the UI when trying to type in carriage returns in th
e
data directly in the table view in Management Studio.
Waleed
"Yifei Jiang" wrote:

> Hi, Waleed,
> +char(13)+char(10) could insert a carriage return and line feed.
> Ta,
> Yifei
> "Waleed Abdulla xrules org>" <waleed_abdulla <atdot> wrote in message
> news:E59CC994-B548-45CE-837A-E6C71A366C5F@.microsoft.com...
> fields
> in
> then
> carriage
> Management
>
>

Cant write longer string in the record

I have a field which was varchar before but I changed it to text.

But i can't write in it enough text as i wish. This field is important becouse it holds SQL senences which i parse latter in my application and than i execute it.

it says <Long Text> and i can't enter any more characters.You got more than 8000 bytes of data?

Look up READTEXT WRITETEXT.

Welcome to a whole new world of hurt.

should've stayed with varchar(8000)|||Each "statement" should easily fit into an 8000-character field. Add a table that would organize the statements into batches, then restructure your original table by adding FK from your BatchMaster, and a Statement Sequence field (what statement comes first, second, etc. in each batch) Ability to read/write text fields is good, but when there is no need...there is no need ;)

Cant use cursor with SP

Hi

I have a SP and in it I call another SP which returns one row in one column, I need to concatenate the value (varchar) to the query in the first SP.
I tried to use cursor with FAST_FORWARD to fetch the result and concatenate it, but I get an error, here is what I tried:

DECLARE Cur CURSOR FAST_FORWARD
FOR SP_Something @.SomeValue

So is it possible to use cursor on SP ? And if it's possible so how ?

Thanks,

Inon.I don't think so...why not put the result set into a table variable?|||I don't think so...why not put the result set into a table variable?

This also don't work, I get an error when I try to INSERT the result from the inner SP to a table var... so is THIS option possible ??

I just want to add that I have several ways to make this work, but I'm trying to be efficient, this is why I want to use an inner SP and not just make the process in the same big SP, I can also make changes in the code (PHP) but I'm trying to make it work this way.

Thanks,

Inon.|||doooh

Yup...can't do that...but you can do

USE Northwind
GO

SET NOCOUNT ON
GO

CREATE PROC mySproc99
AS
SELECT OrderId FROM Orders
GO

CREATE TABLE #myTemp99(OrderId int)

INSERT INTO #myTemp99(OrderId) EXEC mySproc99

SELECT * FROM #myTemp99
GO

SET NOCOUNT ON
DROP TABLE #myTemp99
DROP PROC mySproc99
GO|||doooh

Yup...can't do that...but you can do

USE Northwind
GO

SET NOCOUNT ON
GO

CREATE PROC mySproc99
AS
SELECT OrderId FROM Orders
GO

CREATE TABLE #myTemp99(OrderId int)

INSERT INTO #myTemp99(OrderId) EXEC mySproc99

SELECT * FROM #myTemp99
GO

SET NOCOUNT ON
DROP TABLE #myTemp99
DROP PROC mySproc99
GO

Hmmm... there goes the efficient part... :)

Inon.|||Well...

I don't think (I hate when that happens) that I would ever use effecient and cursor in the same sentence...

Unless it was like

"I wish the developer wrote effecient code instead of using a cursor"

:D|||Well...

I don't think (I hate when that happens) that I would ever use effecient and cursor in the same sentence...

Unless it was like

"I wish the developer wrote effecient code instead of using a cursor"

:D

I agree, didn't mean to express discontent of your solution BTW...

You see, the DB is for a web page, that SP will be called many times.

Thanks for the help,

Inon.|||I'm not sure what you're trying to do, but it looks like you want to use a SP to return a varchar that contains a query that you then want to execute - am I right ?

If so, try making the SP a function, viz:

create function dbo.some_func (@.inp_value char(?))
returns varchar(100)
as
begin
declare @.temp_var varchar(100)

set @.temp_var = 'select * from ' + @.inp_value
return @.temp_var

end

then in your calling sp:

select @.mysql = some_func(@.parm)
execute(@.mysql)

HTH|||I'm not sure what you're trying to do, but it looks like you want to use a SP to return a varchar that contains a query that you then want to execute - am I right ?

Almost right, the inner SP returns only a string that will be concatenate to the query in the outer SP, meaning, the inner SP returns only a part of a query, not entire query.

I have a SP that returns a result, I want to use this result in another SP, I didn't want to run both queries because (and correct me if I'm wrong) I know that for optimal performance it's not recommended to run two separated queries on two table in one SP, because the optimizer will confuse with the best execution plans for each query...

Anyway, this is what I finally did, ran both in one SP... is it as bad as I think ?? And should I just make some process in the code and just run two separated SPs? I wanted to make it in one connection session since it will run many many times and by using one SP I cut traffic (for this option only of course) in 50%...

So what do you say is better? One SP with two queries or two SP's with two connections? (But each has its own optimal execution plan).

Thanks,

Inon.