How do I get uppercase values returned only.

I have a table where inactive names are lower case and active names are
uppercase. Note: Not my design.

Anyways I want to select all names form this table where the name is
uppercase. I see collate and ASCII pop up in searches but the examples
don't seem usable in queries as much as they were for creating tables
and such.

Thanks,
Philselect * from mytable where name = upper(name)

Joe Weinstein at BEA|||No luck with that

Here is my query
SELECT *, last_nme AS Expr1, first_nme AS Expr2
FROM members
WHERE (last_nme = UPPER(last_nme))
ORDER BY last_nme, first_nme

I still see lower case names.|||I fixed it with this
where ASCII(last_nme) = (ASCII(UPPER(last_nme))

Thanks for the responses.
Phil|||As Phillip found out... this will only work if you have set your
instance of SQL Server set to be case-sensitive. The default
installation makes the instance NOT case-sensitive.

-Tom.|||Phillip (pputzback@.ECommunity.com) writes:
> No luck with that
> Here is my query
> SELECT *, last_nme AS Expr1, first_nme AS Expr2
> FROM members
> WHERE (last_nme = UPPER(last_nme))
> ORDER BY last_nme, first_nme
> I still see lower case names.

This should do it:

SELECT *, last_nme AS Expr1, first_nme AS Expr2
FROM members
WHERE last_nme COLLATE Finnish_Swedish_CS_AS =
UPPER(last_nme) COLLATE Finnish_Swedish_CS_AS
ORDER BY last_nme, first_nme

You may prefer to use something else than Finnish_Swedish. It's the
CS_AS part that is the important.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

how do i get the returned id from the store proceedure in asp c#?

my store proceedure gets the id:

CREATE PROCEDURE createpost(
@.userID integer,
@.categoryID integer,
@.title varchar(100),
@.newsdate datetime,
@.story varchar(250),
@.wordcount int
)
as
DECLARE @.newNewsID integer

Insert Into TB_News(UserID, CategoryID, title, newsdate, StoryText, wordcount)
Values (@.userID, @.categoryID, @.title, @.newsdate, @.story, @.wordcount)

SELECT @.newNewsID = @.@.IDENTITY

then im calling it in the asp:

con = new SqlConnection ("server=declt; uid=c1400046; pwd=c1400046; database=c1400046");
con.Open();

cmdselect = new SqlCommand("createpost", con);
cmdselect.CommandType = CommandType.StoredProcedure;

cmdselect.Parameters.Add("@.userID", userID);
cmdselect.Parameters.Add("@.categoryID", categoryID );
cmdselect.Parameters.Add("@.title", title.Text );
cmdselect.Parameters.Add("@.newsdate", newsdate.Text );
cmdselect.Parameters.Add("@.story", story.Text );
cmdselect.Parameters.Add("@.wordcount", "1" );


int valueinserted = cmdselect.ExecuteNonQuery();


Response.Redirect("http://declt/websites/c1400046/newpicture.aspx?id="+valueinserted);

con.Close();

as you can see im using the valueinserted but thats just returning 1, but im guessing that means it sucessful. but i want the id of the new record! any idea how ?

Hi,

you need to either specify a RETURN or make the @.newNewsID an output parameter.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconinputoutputparametersreturnvalues.asp

|||

Or use the SqlDataReader object like this:

1CREATE PROCEDURE createpost(2 @.userID integer,3 @.categoryID integer,4 @.titlevarchar(100),5 @.newsdatedatetime,6 @.storyvarchar(250),7 @.wordcountint8)9as1011Insert Into TB_News(UserID, CategoryID, title, newsdate, StoryText, wordcount)12Values (@.userID, @.categoryID, @.title, @.newsdate, @.story, @.wordcount)1314SELECT@.@.IDENTITY

and in  code:2con =new SqlConnection ("server=declt; uid=c1400046; pwd=c1400046; database=c1400046");3int valueinserted = 0;45try6 cmdselect =new SqlCommand("createpost", con);7 cmdselect.CommandType = CommandType.StoredProcedure;89 cmdselect.Parameters.Add("@.userID", userID);10 cmdselect.Parameters.Add("@.categoryID", categoryID );11 cmdselect.Parameters.Add("@.title", title.Text );12 cmdselect.Parameters.Add("@.newsdate", newsdate.Text );13 cmdselect.Parameters.Add("@.story", story.Text );14 cmdselect.Parameters.Add("@.wordcount","1" );1516 cmdselect.Connection.Open();17 SqlDataReader reader = command.ExecuteReader();18if( reader.HasRows() )19 {20 reader.Read();21 valueinserted = Convert.ToInt32( reader[0] );22 }23catch( Exception ex)24{25//handle the exception somehow26}27finally28{29if( con !=null and conn.State != ConnectionState.Closed )30 con.Close();31}3233Response.Redirect("http://declt/websites/c1400046/newpicture.aspx?id="+valueinserted);

sql

How do I get number of fields returned by query?

Hi.

I am trying to get the results of a dynamic sql statement into a #table, in order to filter them. Given that I don't know how many fields will be returned, how do I accomplish this?

I believe I need to create the #table in advance, and then run the dynamic string as part of an insert [eg 'insert into #table exec (@.sql)' ], but in order to do this I need to know how many fields are going to be returned.

The results might also be returned by a procedure rather than a simple SQL statement, so I can't just parse @.sql to get the fields.

As an example,

declare @.sql nvarchar(200)

select @.sql = 'select "a" as ColA, "b" as ColB'

exec sp_executesql @.sql

returns two columns of data.

I think I need to get the results, count the fields, create the table and finally re-run the query with an insert to poulate the table. So how do I count the columns? And for bonus points, how do I get the column names?

Many thanks,

Neil Jones

u can do this (though not advisable...)

select col1,col2.....

into #temp

from ...select condition...

this will create a table and insert into it at runtime....

or u can just create a temp table with 1 col and alter it dynamically as per ur requirement when u get the number of columns....

(@.@.ROWCOUNT is the system variable which returns the num of rows returned ny the query

select @.@.ROWNUM --after the query 'just in case ur pivoting the result and puttin in the temp table..)

|||

Thanks for your reply

Nitin Khurana wrote:

u can do this (though not advisable...)

select col1,col2.....

into #temp

from ...select condition...

this will create a table and insert into it at runtime....

The problem here is that I can't edit the query. All I know at runtime is that it's a dynamic sql statement, which could be a simple select, or an execution of a stored procedure

Nitin Khurana wrote:

or u can just create a temp table with 1 col and alter it dynamically as per ur requirement when u get the number of columns....

My question is, how do I get the number of columns?

Nitin Khurana wrote:

(@.@.ROWCOUNT is the system variable which returns the num of rows returned ny the query

select @.@.ROWNUM --after the query 'just in case ur pivoting the result and puttin in the temp table..

Unfortunately it's not the number of rows that is the problem.

Cheers,

Neil Jones

|||

hi
first use sys tables and fetch count of table.
then use count of column(s) in the dynamic query
good luck

|||

PersianAmir wrote:

hi
first use sys tables and fetch count of table.
then use count of column(s) in the dynamic query
good luck

Hi.

I don't know what columns are in the query, and I don't know which table(s) (if any) the query is looking at.

If I knew which columns were being returned, this wouldn't be a problem.

Regards,

Neil

|||

Try this:

SELECT * INTO #Temp
FROM (<Your SQL Query>) as T;

Note: If the columns returned by the query aren't properly named (no column name), this will not work. Otherwise the table will be created automatically with the column names from the query. Once the table ist created, you can access the Information about the columns from system views:

USE tempdb
GO
SELECT COUNT(*) FROM sys.columns WHERE object_id = (SELECT object_id FROM sys.tables WHERE name like '#Test%');

This will work with MS SQL Server 2005

Regards,

Paddy

|||

Here you go with a complete sample when having the statement as a variable:

USE AdventureWorks
GO

SET NOCOUNT ON;
GO

-- Create a Table for Testing
CREATE TABLE Test
( col1 INT NOT NULL
, col2 VARCHAR(20) NOT NULL
);
GO

-- Some useful Information
INSERT INTO Test VALUES (1, 'Test1');
INSERT INTO Test VALUES (2, 'Test2');
INSERT INTO Test VALUES (3, 'Test3');
INSERT INTO Test VALUES (4, 'Test4');
INSERT INTO Test VALUES (5, 'Test5');
INSERT INTO Test VALUES (6, 'Test6');
INSERT INTO Test VALUES (7, 'Test7');
INSERT INTO Test VALUES (8, 'Test8');
INSERT INTO Test VALUES (9, 'Test9');
INSERT INTO Test VALUES (10, 'Test10');
GO

-- The dynamic sql statement
DECLARE @.sql NVARCHAR(200);
SET @.sql = 'SELECT col2, col1 FROM Test';

-- Extend the statement for creating a temporary table (Note: Use Global Temporary Table (##TableName))
DECLARE @.sql2 NVARCHAR(250)
set @.sql2 = 'SELECT * INTO ##Test FROM (' + @.sql + ') AS T;';

-- Run the extended Statement
EXEC(@.sql2);
GO

-- View the result
SELECT * FROM ##Test;
GO

-- Get Information about the Temporary Table
USE tempdb
GO

-- Column Count
SELECT COUNT(*) FROM sys.columns
WHERE object_id = (SELECT object_id FROM sys.tables WHERE name LIKE '##Test%');

-- Names of Columns
SELECT name FROM sys.columns
WHERE object_id = (SELECT object_id FROM sys.tables WHERE name LIKE '##Test%');

-- CleanUp
USE AdventureWorks
GO

-- Drop the temporary Table
DROP TABLE ##Test;
GO

-- Drop the Test Data Table
DROP TABLE Test;
GO

SET NOCOUNT OFF;

|||

Neil_D_Jones wrote:

PersianAmir wrote:

hi
first use sys tables and fetch count of table.
then use count of column(s) in the dynamic query
good luck

Hi.

I don't know what columns are in the query, and I don't know which table(s) (if any) the query is looking at.

If I knew which columns were being returned, this wouldn't be a problem.

Regards,

Neil

hi
use this query for return columns of you table:

select dbo.syscolumns.name from dbo.syscolumns
inner join dbo.sysobjects on dbo.syscolumns.id = dbo.sysobjects.id
where dbo.sysobjects.name = 'TABLE_NAME'

and use this query for return count of your field:

select count(dbo.syscolumns.name) from dbo.syscolumns
inner join dbo.sysobjects on dbo.syscolumns.id = dbo.sysobjects.id
where dbo.sysobjects.name = 'TABLE_NAME'

good luck

|||

Neil_D_Jones wrote:

PersianAmir wrote:

hi
first use sys tables and fetch count of table.
then use count of column(s) in the dynamic query
good luck

Hi.

I don't know what columns are in the query, and I don't know which table(s) (if any) the query is looking at.

If I knew which columns were being returned, this wouldn't be a problem.

Regards,

Neil

hi
use this query for return columns of your table:

select dbo.syscolumns.name from dbo.syscolumns
inner join dbo.sysobjects on dbo.syscolumns.id = dbo.sysobjects.id
where dbo.sysobjects.name = 'TABLE_NAME'

and use this query for return count of your field:

select count(dbo.syscolumns.name) from dbo.syscolumns
inner join dbo.sysobjects on dbo.syscolumns.id = dbo.sysobjects.id
where dbo.sysobjects.name = 'TABLE_NAME'

good luck

|||

Lucky P wrote:

Here you go with a complete sample when having the statement as a variable:

That looks great, thanks!

I think I'm going to have to accept that it's not possible with all dynamic sql statements, such as "exec xp_proc1", and take what I can.

Cheers,

Neil

How do I get a count of each set of results?

My query returns a table of results, I would like to add a count column
that contains the number of each result type returned.

i.e.

Type Count
1 3
1 3
1 3
2 2
2 2
3 4
3 4
3 4
3 4
4 2
4 2

Because there are 3 of type 1, 2 of type 2, 4 of type 3 etc...

Is there straightforward way of doing this in SQL?

ThanksIn SQL Server 2005:

select
[Type],
count() over (partition by [Type]) as [Count]
from T

In SQL Server 2000:

select
[Type],
(select count(*)
from T as Tcopy
where Tcopy.[Type] = T.[Type]
) as [Count]
from T

(both solutions untested - for a better chance at tested
solutions, include create table and insert statements that
can be cut and pasted into a query editor.)

Steve Kass
Drew University

kasterborus@.yahoo.com wrote:

Quote:

Originally Posted by

My query returns a table of results, I would like to add a count column
that contains the number of each result type returned.
>
i.e.
>
Type Count
1 3
1 3
1 3
2 2
2 2
3 4
3 4
3 4
3 4
4 2
4 2
>
Because there are 3 of type 1, 2 of type 2, 4 of type 3 etc...
>
Is there straightforward way of doing this in SQL?
>
Thanks
>

How do I get a count of all records returned.

I'm trying to put the total number of records returned from from a query in the bottom of our report. I don't want to do a count(*) in my sql stmt.

thanks.

Hello,

Try this in your table footer:

=CountRows()

Hope this helps.

Jarret

|||putting =CountRows() in my footer give me 1. What may I be missing here?|||

Use =CountRows("DataSet1") where DataSet1 is the name of your dataset that is bound to your table.

Shyam

|||

Try this:

=countDistinct(Fields!name.Value)

It works for me.

How Do I Fix This Proc?

In a multi-user environment, this proc is expected to return distinct values
for concurrent users - but it doesn't, some duplicates are returned. I
thought that the 'BEGIN TRAN/COMMIT' would provide the required locking - I'
m
guessing that's where I went wrong. How do I fix this proc? The DDL for the
tables involved is included.
PROCEDURE procNextKey_Well
( @.NK int OUTPUT
) AS
BEGIN
DECLARE @.NK2 int
SELECT @.NK = COALESCE(MAX(wellId), 0) FROM dbo.well
SELECT @.NK2 = maxId FROM dbo.tbTableMaxId
WHERE (tableName = N'well')
BEGIN TRAN
IF @.NK2 IS NULL
BEGIN
SET @.NK = @.NK + 1
INSERT INTO dbo.tbTableMaxId
(tableName, maxId)
ELECT N'well', @.NK
END
ELSE
BEGIN
IF @.NK2 > @.NK
SET @.NK = @.NK2 + 1
ELSE
SET @.NK = @.NK + 1
UPDATE dbo.tbTableMaxId
SET maxId = @.NK
WHERE (tableName = N'well')
END
COMMIT
END
CREATE TABLE dbo.tbTableMaxId
( tableName nvarchar(50) NOT NULL,
maxId int NOT NULL,
PRIMARY KEY (tableName)
)
CREATE TABLE dbo.well
( wellId int NOT NULL,
wellName nvarchar(50) NULL,
PRIMARY KEY (wellId)
)
Thanks in advance for your help,
Hal Heinrich
VP Technology
Aralan Solutions Inc."Hal Heinrich" <HalHeinrich@.discussions.microsoft.com> wrote in message
news:3AF75018-7A96-403F-877E-8886D434975C@.microsoft.com...
> In a multi-user environment, this proc is expected to return distinct
> values
> for concurrent users - but it doesn't, some duplicates are returned. I
> thought that the 'BEGIN TRAN/COMMIT' would provide the required locking -
> I'm
> guessing that's where I went wrong. How do I fix this proc? The DDL for
> the
> tables involved is included.
>
Basically strict serialization of the whole procedure will be required to
make this correct, so I hope you don't have to support concurrent inserts.
Which is the main reason why IDENTITY columns exist and you shouldn't try to
emulate them with custom code.
David
Here is your fix:
PROCEDURE procNextKey_Well
( @.NK int OUTPUT
) AS
BEGIN
BEGIN TRAN
DECLARE @.NK2 int
SELECT @.NK = COALESCE(MAX(wellId), 0) FROM dbo.well (tablockx,holdlock)
SELECT @.NK2 = maxId FROM dbo.tbTableMaxId (tablockx,holdlock)
WHERE (tableName = N'well')
IF @.NK2 IS NULL
BEGIN
SET @.NK = @.NK + 1
INSERT INTO dbo.tbTableMaxId
(tableName, maxId)
ELECT N'well', @.NK
END
ELSE
BEGIN
IF @.NK2 > @.NK
SET @.NK = @.NK2 + 1
ELSE
SET @.NK = @.NK + 1
UPDATE dbo.tbTableMaxId
SET maxId = @.NK
WHERE (tableName = N'well')
END
COMMIT
END|||David,
Thank you for your response. I've modified your solution to avoid locking
the well table as follows:
PROCEDURE procNextKey_Well
( @.NK int OUTPUT
) AS
BEGIN
SET NOCOUNT ON
DECLARE @.NK2 int
SELECT @.NK = COALESCE(MAX(wellId), 0) FROM dbo.well
BEGIN TRAN
SELECT @.NK2 = maxId FROM dbo.tbTableMaxId (tablockx, holdlock)
WHERE (tableName = N'well')
IF @.NK2 IS NULL
BEGIN
SET @.NK = @.NK + 1
INSERT INTO dbo.tbTableMaxId
(tableName, maxId)
SELECT N'well', @.NK
END
ELSE
BEGIN
IF @.NK2 > @.NK
SET @.NK = @.NK2 + 1
ELSE
SET @.NK = @.NK + 1
UPDATE dbo.tbTableMaxId
SET maxId = @.NK
WHERE (tableName = N'well')
END
COMMIT
END
I'll be testing this next w and will post the results here.
Thanks again,
Hal Heinrich
VP Technology
Aralan Solutions Inc.
"David Browne" wrote:

> "Hal Heinrich" <HalHeinrich@.discussions.microsoft.com> wrote in message
> news:3AF75018-7A96-403F-877E-8886D434975C@.microsoft.com...
> Basically strict serialization of the whole procedure will be required to
> make this correct, so I hope you don't have to support concurrent inserts.
> Which is the main reason why IDENTITY columns exist and you shouldn't try
to
> emulate them with custom code.
> David
> Here is your fix:
> PROCEDURE procNextKey_Well
> ( @.NK int OUTPUT
> ) AS
> BEGIN
> BEGIN TRAN
> DECLARE @.NK2 int
> SELECT @.NK = COALESCE(MAX(wellId), 0) FROM dbo.well (tablockx,holdlock)
> SELECT @.NK2 = maxId FROM dbo.tbTableMaxId (tablockx,holdlock)
> WHERE (tableName = N'well')
>
> IF @.NK2 IS NULL
> BEGIN
> SET @.NK = @.NK + 1
> INSERT INTO dbo.tbTableMaxId
> (tableName, maxId)
> ELECT N'well', @.NK
> END
> ELSE
> BEGIN
> IF @.NK2 > @.NK
> SET @.NK = @.NK2 + 1
> ELSE
> SET @.NK = @.NK + 1
> UPDATE dbo.tbTableMaxId
> SET maxId = @.NK
> WHERE (tableName = N'well')
> END
> COMMIT
> END
>
>
>|||Hello Heinrich!
I think, it would a better idea if you try to lock only ,row for N'well'
table,
I thin you should use an rowlock hint
"Hal Heinrich" wrote:
> David,
> Thank you for your response. I've modified your solution to avoid locking
> the well table as follows:
> PROCEDURE procNextKey_Well
> ( @.NK int OUTPUT
> ) AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.NK2 int
> SELECT @.NK = COALESCE(MAX(wellId), 0) FROM dbo.well
> BEGIN TRAN
> SELECT @.NK2 = maxId FROM dbo.tbTableMaxId (tablockx, holdlock)
> WHERE (tableName = N'well')
> IF @.NK2 IS NULL
> BEGIN
> SET @.NK = @.NK + 1
> INSERT INTO dbo.tbTableMaxId
> (tableName, maxId)
> SELECT N'well', @.NK
> END
> ELSE
> BEGIN
> IF @.NK2 > @.NK
> SET @.NK = @.NK2 + 1
> ELSE
> SET @.NK = @.NK + 1
> UPDATE dbo.tbTableMaxId
> SET maxId = @.NK
> WHERE (tableName = N'well')
> END
> COMMIT
> END
> I'll be testing this next w and will post the results here.
> Thanks again,
> Hal Heinrich
> VP Technology
> Aralan Solutions Inc.
>
> "David Browne" wrote:
>|||"Fred" <Fred@.discussions.microsoft.com> wrote in message
news:8A55A9E5-C188-4DCF-BDD9-656AD810C31B@.microsoft.com...
> Hello Heinrich!
> I think, it would a better idea if you try to lock only ,row for N'well'
> table,
> I thin you should use an rowlock hint
>
Why? And what row do you propose locking?
David