The following table when using a cursor gives 0 for decimals where the value
s
are below 1. I am trying to use this to apply a factor and always get a zero
if the factor below 1, as a result my logic fails.
Can some one help me on this?
-- ========== Table & data =============
CREATE Table TestFactor (TestFactorId int, FactorValue decimal(5,2))
INSERT INTO TestFactor VALUES (1, 0.25)
INSERT INTO TestFactor VALUES (2, 0.50)
INSERT INTO TestFactor VALUES (3, 0.75)
INSERT INTO TestFactor VALUES (4, 0.125)
INSERT INTO TestFactor VALUES (5, 0.25)
INSERT INTO TestFactor VALUES (6, 2)
INSERT INTO TestFactor VALUES (7, 1)
-- ========== Table & data =============
DECLARE @.Factor decimal
DECLARE my CURSOR
FOR SELECT FactorValue from TestFactor
OPEN my
FETCH NEXT FROM my INTO
@.Factor
SELECT @.Factor
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM my INTO
@.Factor
SELECT @.Factor
END
CLOSE my
DEALLOCATE my
Thankschange DECLARE @.Factor decimal to
DECLARE @.Factor decimal (5,2) -- same as in the table
http://sqlservercode.blogspot.com/|||Change
DECLARE @.Factor decimal
to
DECLARE @.Factor decimal(5,2)
"Ram" wrote:
> The following table when using a cursor gives 0 for decimals where the val
ues
> are below 1. I am trying to use this to apply a factor and always get a ze
ro
> if the factor below 1, as a result my logic fails.
> Can some one help me on this?
> -- ========== Table & data =============
> CREATE Table TestFactor (TestFactorId int, FactorValue decimal(5,2))
> INSERT INTO TestFactor VALUES (1, 0.25)
> INSERT INTO TestFactor VALUES (2, 0.50)
> INSERT INTO TestFactor VALUES (3, 0.75)
> INSERT INTO TestFactor VALUES (4, 0.125)
> INSERT INTO TestFactor VALUES (5, 0.25)
> INSERT INTO TestFactor VALUES (6, 2)
> INSERT INTO TestFactor VALUES (7, 1)
> -- ========== Table & data =============
>
> DECLARE @.Factor decimal
> DECLARE my CURSOR
> FOR SELECT FactorValue from TestFactor
> OPEN my
> FETCH NEXT FROM my INTO
> @.Factor
> SELECT @.Factor
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> FETCH NEXT FROM my INTO
> @.Factor
> SELECT @.Factor
> END
> CLOSE my
> DEALLOCATE my
> Thanks
>|||Thanks Mark & SQL
I was going crazy on this. I think I need a vacation
"Mark Williams" wrote:
> Change
> DECLARE @.Factor decimal
> to
> DECLARE @.Factor decimal(5,2)
> "Ram" wrote:
>
Showing posts with label values. Show all posts
Showing posts with label values. Show all posts
Thursday, March 29, 2012
Help with cross products and functions
Hello,
I have two tables that i need to join. The first table has a column which
is an encoded addition of one or more values in a second table.
table 1:
col1 col2 col3
1 a b 12
...
table 2:
col1 col2
1 x 4
2 y 8
3 z 16
...
the record for table 1, maps to records 1 and 2 from table 2. I know that
the value 12 in table 1 (col3) mapps to rows 1,2 in table 2 because of this
math function that decodes the encryption.
I have a function that is able to return a table that lists all the values
in table 2 that correspond to the supplied value in table 1..ie if i call
function fn_mappings(12) i get a table called @.result:
table2value
1 4
2 8
what i need to do is then combine these records together to get a view as
follows
col1 col2 table2value
1 a b 4
2 a b 8
...
is this possible to do? i need this to happen for EACH row in table 1 to
find corresponding records in table 2.
thanks for any and all help!
BenIn order to link both tables, I took the liberty of "re-create" a convenient
environment to get the results you asked. I hope it is ok.
Let me know if it works for you
-- Begin Script
create table tbl1
( id int primary key
, col1 varchar(10)
, col2 varchar(10)
, col3 int
)
create table tbl2
(
id int primary key
, fkid int
, col1 varchar(10)
, col2 int
)
insert into tbl1
values (1, 'a', 'b', 12)
insert into tbl2
values (1, 1, 'x', 4)
insert into tbl2
values (2, 1, 'x', 8)
insert into tbl2
values (3, 2, 'x', 16)
-- View the output of both tables
select * from tbl1
select * from tbl2
go
create function dbo.fn_mappings(@.in int)
returns table
as
return (select id, col2 from tbl2 where fkid = (select id from tbl1 where
col3 = @.in))
go
-- View the dbo.fn_mappings() output
select * from dbo.fn_mappings(12)
-- Output requested
select t1.id
, t1.col1
, t1.col2
, t2.col2
from tbl1 t1
inner join tbl2 t2
on t1.id = t2.fkid
-- Drop all objects
drop function dbo.fn_mappings
drop table tbl1
drop table tbl2
"Ben" wrote:
> Hello,
> I have two tables that i need to join. The first table has a column which
> is an encoded addition of one or more values in a second table.
> table 1:
> col1 col2 col3
> 1 a b 12
> ...
> table 2:
> col1 col2
> 1 x 4
> 2 y 8
> 3 z 16
> ...
> the record for table 1, maps to records 1 and 2 from table 2. I know that
> the value 12 in table 1 (col3) mapps to rows 1,2 in table 2 because of thi
s
> math function that decodes the encryption.
> I have a function that is able to return a table that lists all the values
> in table 2 that correspond to the supplied value in table 1..ie if i call
> function fn_mappings(12) i get a table called @.result:
> table2value
> 1 4
> 2 8
> what i need to do is then combine these records together to get a view as
> follows
> col1 col2 table2value
> 1 a b 4
> 2 a b 8
> ...
> is this possible to do? i need this to happen for EACH row in table 1 to
> find corresponding records in table 2.
> thanks for any and all help!
> Ben|||Edgardo,
Thank you for your response...however, I do not have the liberty of changing
table2's structure. The data is provided by an external source and I am
creating custom reporting for it. Therefor i cant give it a foreign key typ
e
column.
do you have any other ideas?
Thanks again
Ben
"Edgardo Valdez, MCSD, MCDBA" wrote:
> In order to link both tables, I took the liberty of "re-create" a convenie
nt
> environment to get the results you asked. I hope it is ok.
> Let me know if it works for you
> -- Begin Script
> create table tbl1
> ( id int primary key
> , col1 varchar(10)
> , col2 varchar(10)
> , col3 int
> )
> create table tbl2
> (
> id int primary key
> , fkid int
> , col1 varchar(10)
> , col2 int
> )
> insert into tbl1
> values (1, 'a', 'b', 12)
> insert into tbl2
> values (1, 1, 'x', 4)
> insert into tbl2
> values (2, 1, 'x', 8)
> insert into tbl2
> values (3, 2, 'x', 16)
> -- View the output of both tables
> select * from tbl1
> select * from tbl2
> go
> create function dbo.fn_mappings(@.in int)
> returns table
> as
> return (select id, col2 from tbl2 where fkid = (select id from tbl1 where
> col3 = @.in))
> go
> -- View the dbo.fn_mappings() output
> select * from dbo.fn_mappings(12)
> -- Output requested
> select t1.id
> , t1.col1
> , t1.col2
> , t2.col2
> from tbl1 t1
> inner join tbl2 t2
> on t1.id = t2.fkid
> -- Drop all objects
> drop function dbo.fn_mappings
> drop table tbl1
> drop table tbl2
>
> "Ben" wrote:
>
I have two tables that i need to join. The first table has a column which
is an encoded addition of one or more values in a second table.
table 1:
col1 col2 col3
1 a b 12
...
table 2:
col1 col2
1 x 4
2 y 8
3 z 16
...
the record for table 1, maps to records 1 and 2 from table 2. I know that
the value 12 in table 1 (col3) mapps to rows 1,2 in table 2 because of this
math function that decodes the encryption.
I have a function that is able to return a table that lists all the values
in table 2 that correspond to the supplied value in table 1..ie if i call
function fn_mappings(12) i get a table called @.result:
table2value
1 4
2 8
what i need to do is then combine these records together to get a view as
follows
col1 col2 table2value
1 a b 4
2 a b 8
...
is this possible to do? i need this to happen for EACH row in table 1 to
find corresponding records in table 2.
thanks for any and all help!
BenIn order to link both tables, I took the liberty of "re-create" a convenient
environment to get the results you asked. I hope it is ok.
Let me know if it works for you
-- Begin Script
create table tbl1
( id int primary key
, col1 varchar(10)
, col2 varchar(10)
, col3 int
)
create table tbl2
(
id int primary key
, fkid int
, col1 varchar(10)
, col2 int
)
insert into tbl1
values (1, 'a', 'b', 12)
insert into tbl2
values (1, 1, 'x', 4)
insert into tbl2
values (2, 1, 'x', 8)
insert into tbl2
values (3, 2, 'x', 16)
-- View the output of both tables
select * from tbl1
select * from tbl2
go
create function dbo.fn_mappings(@.in int)
returns table
as
return (select id, col2 from tbl2 where fkid = (select id from tbl1 where
col3 = @.in))
go
-- View the dbo.fn_mappings() output
select * from dbo.fn_mappings(12)
-- Output requested
select t1.id
, t1.col1
, t1.col2
, t2.col2
from tbl1 t1
inner join tbl2 t2
on t1.id = t2.fkid
-- Drop all objects
drop function dbo.fn_mappings
drop table tbl1
drop table tbl2
"Ben" wrote:
> Hello,
> I have two tables that i need to join. The first table has a column which
> is an encoded addition of one or more values in a second table.
> table 1:
> col1 col2 col3
> 1 a b 12
> ...
> table 2:
> col1 col2
> 1 x 4
> 2 y 8
> 3 z 16
> ...
> the record for table 1, maps to records 1 and 2 from table 2. I know that
> the value 12 in table 1 (col3) mapps to rows 1,2 in table 2 because of thi
s
> math function that decodes the encryption.
> I have a function that is able to return a table that lists all the values
> in table 2 that correspond to the supplied value in table 1..ie if i call
> function fn_mappings(12) i get a table called @.result:
> table2value
> 1 4
> 2 8
> what i need to do is then combine these records together to get a view as
> follows
> col1 col2 table2value
> 1 a b 4
> 2 a b 8
> ...
> is this possible to do? i need this to happen for EACH row in table 1 to
> find corresponding records in table 2.
> thanks for any and all help!
> Ben|||Edgardo,
Thank you for your response...however, I do not have the liberty of changing
table2's structure. The data is provided by an external source and I am
creating custom reporting for it. Therefor i cant give it a foreign key typ
e
column.
do you have any other ideas?
Thanks again
Ben
"Edgardo Valdez, MCSD, MCDBA" wrote:
> In order to link both tables, I took the liberty of "re-create" a convenie
nt
> environment to get the results you asked. I hope it is ok.
> Let me know if it works for you
> -- Begin Script
> create table tbl1
> ( id int primary key
> , col1 varchar(10)
> , col2 varchar(10)
> , col3 int
> )
> create table tbl2
> (
> id int primary key
> , fkid int
> , col1 varchar(10)
> , col2 int
> )
> insert into tbl1
> values (1, 'a', 'b', 12)
> insert into tbl2
> values (1, 1, 'x', 4)
> insert into tbl2
> values (2, 1, 'x', 8)
> insert into tbl2
> values (3, 2, 'x', 16)
> -- View the output of both tables
> select * from tbl1
> select * from tbl2
> go
> create function dbo.fn_mappings(@.in int)
> returns table
> as
> return (select id, col2 from tbl2 where fkid = (select id from tbl1 where
> col3 = @.in))
> go
> -- View the dbo.fn_mappings() output
> select * from dbo.fn_mappings(12)
> -- Output requested
> select t1.id
> , t1.col1
> , t1.col2
> , t2.col2
> from tbl1 t1
> inner join tbl2 t2
> on t1.id = t2.fkid
> -- Drop all objects
> drop function dbo.fn_mappings
> drop table tbl1
> drop table tbl2
>
> "Ben" wrote:
>
Tuesday, March 27, 2012
Help with column Insert
Hi,
I have some values I want put into a table, but the values are from other sources and I dont know how to retrieve them..
I'll show my code, and the bold is explaining what I want inserted and where from. I'd apprechiate if someone could help me with syntax etc. There are 2 about getting value from another table and one about just putting in straight forward text..:
command.CommandText = "INSERT INTO Messages (sendername,recievername,message,Date,subject) VALUES (@.sendername,@.recievername,@.message,@.date,@.subject)";
command.Parameters.Add("@.sendername", System.Web.HttpContext.Current.User.Identity.Name)
command.Parameters.Add("@.recievername",every value of column named Usersname of the Transactions table, WHERE Itemid=Itemid in the gridview on this page);
command.Parameters.Add("@.message",the value of items table - column 'paymentinstructions' WHERE Username=System.Web.HttpContext.Current.User.Identity.Name);
command.Parameters.Add("@.subject",some text: IMPORTANT - Payment Required);
command.Parameters.Add("@.date", DateTime.Now.ToString());
command.ExecuteNonQuery();
Thanks alot if anyone can help me with those three things..
Jon
If 10 people are to receive the message, are there 10 message records or one?
If one, how are the 10 usernames supposed to be formatted? comma-separated values, separated by semi-colons?
|||I was hoping to have 1 record, with a large list of usernames. I assume it will work if they are seperated by anything, but with a space - peoples messages are called up if the recievername column has their username in it. Will it work if it also has other writing (i.e. other peoples usernames)?
Thanks
Jon
|||
jbear123:
command.Parameters.Add("@.recievername",every value of column named Usersname of the Transactions table, WHERE Itemid=Itemid in the gridview on this page);
command.Parameters.Add("@.message",the value of items table - column 'paymentinstructions' WHERE Username=System.Web.HttpContext.Current.User.Identity.Name);
command.Parameters.Add("@.subject",some text: IMPORTANT - Payment Required);
I just took the time toreally pay attention to what you are doing in business terms instead of just the coding issues.
This is a collection's application? If you sent out a message with my name on it, telling hundreds of other people I hadn't paid up, I would be really angry.
Angry enough to tell you impolite things and never do business with you again.
Depending upon what country you live in or are sending the messages to, it might even be illegal. Particularly if you made a mistake and they did not owe you anything - that would be libel under US law.
That said, I have one other question. The payment instructions are tied to the user who is logged into the page, not the user receiving the message? That was a bit surprising. Or are they tied to the user receiving the message?
As for filling in the values, you just need a few strings to store them in.
Issue a query to get the list of usernames and loop thru them. Use the StringBuilder to concatenate them.
Issue another query to get the payment instructions.
|||
As for filling in the values, you just need a few strings to store them in.
Issue a query to get the list of usernames and loop thru them. Use the StringBuilder to concatenate them.
Issue another query to get the payment instructions.
This is what I was after. Would you mind explaining this a bit more throughly for someone of a lower coding ability..? i.e. how do I issue the query to get the list of usernames? Compared to the code I posted, how would it be structured? Concatenate them?
Re: Legal issues - Thank you for your concern, but you shouldn't worry about it.
Thanks for your help!
Jon
As for filling in the values, you just need a few strings to store them in.
Issue a query to get the list of usernames and loop thru them. Use the StringBuilder to concatenate them.
Issue another query to get the payment instructions.
This is what I was after. Would you mind explaining this a bit more throughly for someone of a lower coding ability..? i.e. how do I issue the query to get the list of usernames? Compared to the code I posted, how would it be structured? Concatenate them?
Re: Legal issues - Thank you for your concern, but you shouldn't worry about it.
Thanks for your help!
Jon
You already know how to issue a sql command via the SqlCommand object. Instead of an update command, you need to issue a query:
My guess as to your query would be "select distinct usersname from transactions where itemid = @.itemid"
You will have to pass in the itemid as a parameter, and issue an ExecuteQuery instead of an ExecuteNonQuery. You will be putting the results of the query into a DataReader and looping thru it. Just google or look it up any pretty much any asp book. This is basic, beginner level stuff and it's well documented all over the place - so I'm not going to do it again. :)
Inside the DataReader loop, you can concatenate the usernames you return into a string. It's best to use the StringBuilder class when you are looping. Again, google or look up StringBuilder in the manual.
I think you would be better served slogging thru this step yourself rather than having someone hand it to you. You'll learn it better, and this is bread-and-butter code that you'll use all the time, so it needs to be second nature. I'll keep an eye out on this thread in case you get stuck someplace.
|||
Hi,
I think I have the idea - could you confirm this for me:
I keep the insert commands, but before them I create a command to select all the info that I want inserted (from different tables etc), then using the insert command reference the results of the select command info?
If thats not what yopu meant, would that work anyway?
Thanks,
Jon
|||I hope it will work as I have done it now..I created datalists showing the data I want to be inserted.
The only thing I cant do is link the results of the datalist (where i selected the data I want(visible=false)) etc to the add parameter section.
E.g:
command.Parameters.AddWithValue("@.sendername", System.Web.HttpContext.Current.User.Identity.Name);
command.Parameters.Add("@.recievername",how do I link to results of the datalist here? I'd have to have a comma in between each result);
command.Parameters.AddWithValue("@.message", TextBox2.Text);
command.Parameters.AddWithValue("@.subject", TextBox3.Text);
command.Parameters.AddWithValue("@.date", DateTime.Now.ToString());
Please could you briefly explain, as soon as I know that I can apply it to problemsall over the page and my site will be done!
Thanks alot
Jon
|||Before you get to this point, create a string to hold the receivername values.
Loop thru the datalist, and for each entry in the datalist, append the next receiver.
|||
I'll give it a go. Just briefly before I leave you alone for a while (sorry!)-
If I create a string, how do I do that for many values (i.e. many usernames),
By loop thru, you mean just use find the string on the datalist?
And what do you mean by append the next receiver? use: + 'next receiver'?
Cheers,
Jon
|||Hi,
I have:
SqlCommand command = new SqlCommand();
command.Connection = con;
command.CommandText = "INSERT INTO Messages (sendername,recievername,message,Date,subject) VALUES (@.sendername,@.recievername,@.message,@.date,@.subject)";
command.Parameters.AddWithValue("@.sendername", System.Web.HttpContext.Current.User.Identity.Name);
DataView dv = SqlDataSource2.Select(DataSourceSelectArguments.Empty) as DataView;
string receivername = dv[0]["receivername"].ToString();
command.Parameters.AddWithValue("@.recievername", receivername);
command.Parameters.AddWithValue("@.message", TextBox2.Text);
command.Parameters.AddWithValue("@.subject", TextBox3.Text);
command.Parameters.AddWithValue("@.date", DateTime.Now.ToString());
command.ExecuteNonQuery();
con.Close();
command.Dispose();
return true;
so far. How can I make values seperate by commas?
Thanks,
Jon
sqlhelp with clr trigger
hi all.
i'm writing a simple clr trigger.
the trigger reads values from the INSERTED table.
and i use try - catch statement.
in the catch () i want to inesrt a new row to my Logtbl Table.
how can i insert row to a table in another tables trigger?
can you please give me a simple example?
thanks.
How about: "insert into Logtbl values(your values)" as a SqlCommand.Niels
|||
should i use the same connection object for this sqlCommand?
|||Sure.
Niels
Monday, March 26, 2012
Help with assigning values to variables
With some help Jacco Schalkwijk (SQL Server MVP), I've been able to query
accurate data from my shopping cart database. This query collects customer
purchase information based on the current session and userID. The query
Dim objCommand as SqlCommand = New SqlCommand("Select orderID, modelNumber from orderDetails where CustomerID = " &
User.Identity.Name & " And orderid = (SELECT MAX(orderid) FROM orderDetails where CustomerID = " &
User.Identity.Name & ")"
Now that I've collected all the orderIDâ's associated with the current customer,
I would like to assign specific values to variables based on this query. I would
like to create something like the following
Dim orderItem as String = (all of the modelNumbers from the query)
Dim orderIdItem as String = (all of the orderIDs from the query)
How do I do this' Any help is much appreciated! Thanks in advance.
Ro=?Utf-8?B?Um9u?= (ron.r.williams@.comcast.net) writes:
> I would like to assign specific values to variables based on this query.
> I would like to create something like the following:
> Dim orderItem as String = (all of the modelNumbers from the query)
> Dim orderIdItem as String = (all of the orderIDs from the query)
> How do I do this' Any help is much appreciated! Thanks in advance.
You would have to iterate over the dataset to build this string. So this
is more of a VB .Net question than an SQL question. I should add that
doing this in SQL is possible, but it is not a good idea.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
accurate data from my shopping cart database. This query collects customer
purchase information based on the current session and userID. The query
Dim objCommand as SqlCommand = New SqlCommand("Select orderID, modelNumber from orderDetails where CustomerID = " &
User.Identity.Name & " And orderid = (SELECT MAX(orderid) FROM orderDetails where CustomerID = " &
User.Identity.Name & ")"
Now that I've collected all the orderIDâ's associated with the current customer,
I would like to assign specific values to variables based on this query. I would
like to create something like the following
Dim orderItem as String = (all of the modelNumbers from the query)
Dim orderIdItem as String = (all of the orderIDs from the query)
How do I do this' Any help is much appreciated! Thanks in advance.
Ro=?Utf-8?B?Um9u?= (ron.r.williams@.comcast.net) writes:
> I would like to assign specific values to variables based on this query.
> I would like to create something like the following:
> Dim orderItem as String = (all of the modelNumbers from the query)
> Dim orderIdItem as String = (all of the orderIDs from the query)
> How do I do this' Any help is much appreciated! Thanks in advance.
You would have to iterate over the dataset to build this string. So this
is more of a VB .Net question than an SQL question. I should add that
doing this in SQL is possible, but it is not a good idea.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Friday, March 23, 2012
Help with an error I don''t understand
Hey everyone,
I'm trying to make an image appear differently depending on the values of certain data. The statement that I am using is:
= IIF(DATEDIFF("dd",Fields!LastBackupDate.Value,Now)>15,"redball",IIF(DATEDIFF("dd",Fields!LastBackupDate.Value,Now)<8,"greenball","yellowball"))
And the error that I get is:
[rsRuntimeErrorInExpression] The Value expression for the image ‘image4’ contains an error: Argument 'Interval' is not a valid value.
Preview complete -- 0 errors, 1 warnings
When I preview the report I just get the typical white box with a red x where the image should be. I don't understand.
Thanks in advance,
Keith
Nevermind, argument needs to be "d". Thanks anyway!
-Keith
Help with an error I don''t understand
Hey everyone,
I'm trying to make an image appear differently depending on the values of certain data. The statement that I am using is:
= IIF(DATEDIFF("dd",Fields!LastBackupDate.Value,Now)>15,"redball",IIF(DATEDIFF("dd",Fields!LastBackupDate.Value,Now)<8,"greenball","yellowball"))
And the error that I get is:
[rsRuntimeErrorInExpression] The Value expression for the image ‘image4’ contains an error: Argument 'Interval' is not a valid value.
Preview complete -- 0 errors, 1 warnings
When I preview the report I just get the typical white box with a red x where the image should be. I don't understand.
Thanks in advance,
Keith
Nevermind, argument needs to be "d". Thanks anyway!
-Keith
sqlHelp with a SQL Union query please. Conversion errors!
Hi, I've created a SQL union query that grabs the top three values of a test and puts the results together on one line item by a recordnumber.
The only problem is, one value is generating the error: " Syntax error converting the varchar value '=' to a column of data type int."
This field is an operator field, so it will only display operator values ( <, >, = ). Everything seems to work BUT the union join on that column, which is a varchar data type. I need this union to work, and Im frustrated that I can't seem to figure out why I can't get the logic to work. Can someone take a look at this and help me with it?
The union query looks like this:
exec('
selectRecordNumber,
Sum(rank1) as HbA1CRank1,
max(Operator1) as HbA1COperator1,
sum(contentValue1) as HbA1CContentvalue1,
max(dos1) as HbA1CDOS1,
Sum(rank2) as HbA1CRank2,
max(Operator2) as HbA1COperator2,
sum(contentValue2) as HbA1CContentvalue2,
max(dos2) as HbA1CDOS2,
Sum(rank3) as HbA1CRank3,
max(Operator3) as HbA1COperator3,
sum(contentValue3) as HbA1CContentvalue3,
max(dos3) as HbA1CDOS3from
(SELECT DISTINCT
TOP 100 PERCENT recordnumber, Rank AS rank1, cast(Operator as varchar) as Operator1, contentValue AS contentvalue1, DOS AS DOS1, 0 AS rank2, 0 as Operator2, 0 AS contentvalue2, 0 AS DOS2,
0 AS rank3, 0 as Operator3, 0 AS contentvalue3, 0 AS DOS3
FROM (SELECT (SELECT COUNT(*)
FROM tblDiabetic_HgbA1C_Total vw1
WHERE vw1.rECORDnUMBER = vw2.rECORDnUMBER AND vw1.ItemCode = vw2.ItemCode AND vw1.Operator = vw2.Operator AND vw1.DOS > vw2.DOS) AS Rank, *
FROM tblDiabetic_HgbA1C_Total vw2) vw3
WHERE (Rank = 0)
ORDER BY RecordNumberUNION
SELECT DISTINCT
TOP 100 PERCENT recordnumber, 0 AS rank1, 0 as Operator1, 0 AS contentvalue1, 0 AS DOS1, Rank AS rank2, cast(Operator as varchar) as Operator2, contentValue AS contentvalue2, DOS AS DOS2,
0 AS rank3, 0 as operator3, 0 AS contentvalue3, 0 AS DOS3
FROM (SELECT (SELECT COUNT(*)
FROM tblDiabetic_HgbA1C_Total vw1
WHERE vw1.rECORDnUMBER = vw2.rECORDnUMBER AND vw1.ItemCode = vw2.ItemCode AND vw1.Operator = vw2.Operator AND vw1.DOS > vw2.DOS) AS Rank, *
FROM tblDiabetic_HgbA1C_Total vw2) vw3
WHERE (Rank = 1)
ORDER BY RecordNumber
UNIONSELECT DISTINCT
TOP 100 PERCENT recordnumber, 0 AS rank1, 0 as Operator1, 0 AS contentvalue1, 0 AS DOS1, 0 AS rank2, 0 as operator2, 0 AS contentvalue2, 0 AS DOS2, rank AS rank3, cast(Operator as varchar) as operator3,
contentvalue AS contentvalue3, DOS AS DOS3
FROM (SELECT (SELECT COUNT(*)
FROM tblDiabetic_HgbA1C_Total vw1
WHERE vw1.rECORDnUMBER = vw2.rECORDnUMBER AND vw1.ItemCode = vw2.ItemCode AND vw1.Operator = vw2.Operator AND vw1.DOS > vw2.DOS) AS Rank, *
FROM tblDiabetic_HgbA1C_Total vw2) vw3
WHERE (Rank = 2)
ORDER BY RecordNumber )tblHgA1Cgroup by RecordNumber
')
GO
Can anyone help? It looks right to me, I just can't figure out why the error keeps coming up =\
Thank you!
The error most likely comes from having "cast(Operator as varchar) as Operator1" in the first query and then "0 as Operator1" in the subsequent ones. It should be "'' as Operator1"However,write your query like this instead, and you won't have to use a UNION at all
select
RecordNumber,
Sum(CASE WHEN Rank=0 THEN Rank ELSE NULL END) as HbA1CRank1, -- WILL BE 0
max(CASE WHEN Rank=0 THEN Operator ELSE NULL END) as HbA1COperator1,
sum(CASE WHEN Rank=0 THEN contentValue ELSE NULL END) as HbA1CContentvalue1,
max(CASE WHEN Rank=0 THEN DOS ELSE NULL END) as HbA1CDOS1,
Sum(CASE WHEN Rank=1 THEN Rank ELSE NULL END) as HbA1CRank2,
max(CASE WHEN Rank=1 THEN Operator ELSE NULL END) as HbA1COperator2,
sum(CASE WHEN Rank=1 THEN contentValue ELSE NULL END) as HbA1CContentvalue2,
max(CASE WHEN Rank=1 THEN DOS ELSE NULL END) as HbA1CDOS2,
Sum(CASE WHEN Rank=2 THEN Rank ELSE NULL END) as HbA1CRank3,
max(CASE WHEN Rank=2 THEN Operator ELSE NULL END) as HbA1COperator3,
sum(CASE WHEN Rank=2 THEN contentValue ELSE NULL END) as HbA1CContentvalue3,
max(CASE WHEN Rank=2 THEN DOS ELSE NULL END) as HbA1CDOS3
from ...|||
Gunteman! Oh man, thank you so much! I can't believe how easy that was, your solution worked perfectly. That's awesome, you made my night!
Thanks again =)
|||Great! BTW, why did you use EXEC?
|||about a year ago someone here recommended wrapping my union statements in the EXEC command when running them within stored procedures. I didnt' bother researching further... it seemed to work, so I just ran with it and kept doing it :)
Should I not bother with it?
sqlMonday, March 19, 2012
Help with a loop.
I am writing a script that will go through all the database files on a
server, collect the file sizes and return the values in a single
table. This script works for the most part, but there is an instance
when the script fails to collect the information properly. When there
are two or more data files the script only reports the first one twice.
Can someone take a look at this loop and tell me where the error is?
Thanks
-Matt-
/ ****************************************
**********
Script to calculate information about the Data Files
****************************************
**********/
DECLARE @.dbname varchar(50)
DECLARE @.string varchar(250)
SET @.string = ''
Declare @.rows int
CREATE TABLE #dbcc_showfilestats (
fileid tinyint,
FileGroup1 tinyint,
TotalExtents1 decimal (28, 2),
UsedExtents1 decimal (28, 2),
Name varchar(50),
FileName sysname )
CREATE TABLE #dbstats (
DB_Name varchar(50),
DB_Total_Size_in_MB decimal (28, 2),
DB_Used_Size_in_MB decimal (28, 2),
DB_Free_Size_in_MB decimal (28, 2),
DB_Percent_Used decimal (28, 2))
DECLARE dbnames_cursor CURSOR FOR SELECT name FROM master..sysdatabases
-- Collects all the DB name
OPEN dbnames_cursor
FETCH NEXT FROM dbnames_cursor INTO @.dbname
WHILE (@.@.fetch_status = 0)
BEGIN
SET @.string = 'use ' + @.dbname + ' DBCC SHOWFILESTATS'
INSERT #dbcc_showfilestats
EXEC (@.string)
SELECT * FROM #dbcc_showfilestats -- Debug
SELECT @.rows = count(*) from #dbcc_showfilestats
While @.rows > 0
BEGIN
INSERT #dbstats (DB_Name, DB_Total_Size_in_MB, DB_Used_Size_in_MB,
DB_Free_Size_in_MB, DB_Percent_Used)
SELECT @.dbname,
DB_Total_Size_in_MB = sum(TotalExtents1)*65536.0/1048576.0,
DB_Used_Size_in_MB = sum(UsedExtents1)*65536.0/1048576.0,
DB_Free_Size_in_MB =
sum(TotalExtents1-UsedExtents1)*65536.0/1048576.0,
DB_Percent_Used = sum(UsedExtents1/TotalExtents1)*100
FROM #dbcc_showfilestats
SELECT * FROM #dbstats
SET @.rows = @.rows - 1
END
TRUNCATE TABLE #dbcc_showfilestats
FETCH NEXT FROM dbnames_cursor INTO @.dbname
END
CLOSE dbnames_cursor
DEALLOCATE dbnames_cursor
SELECT * FROM #dbstats --Debug
DROP TABLE #dbstats --Debug
DROP TABLE #dbcc_showfilestats --DebugYou are selecting the same rows from #dbcc_showfilestats
every time through your 'while' loop.
Add
id int identity(1,1)
to your #dbcc_showfilestats table and change
FROM #dbcc_showfilestats
to
FROM #dbcc_showfilestats where id=@.rows|||Hi Matthew,
In addition to correctly adding a unique integer to distinguish rows in
your temp table as Mark has suggested, you may want to look at using
another temp table to loop through rather than using a cursor.
Cursors are very memory heavy in comparison to a looped through temp
table.
So instead your loop (in pseudo) would look more like:
-- SET UP 'CURSOR' TABLE
SELECT name INTO #databases FROM master..sysdatabases
-- DEFINE LOOPING PARAMETER
DECLARE @.unqName nvarchar(4000)
-- SELECT LOOPING PARAMETER
SELECT @.unqName = name FROM #databases
-- ENTER WHILE LOOP
WHILE LEN(@.unqName) > 0
BEGIN
-- PERFORM LOOP CODE
--DELETE ROW FROM LOOPING TABLE #databases
DELETE FROM #databases WHERE name = @.unqName
SELECT @.unqName = '' -- CLEAR VARIABLE
SELECT @.unqName = name FROM #databases
END
This will make a big difference in large looping scenarios - just try
it out.
Andrew La Grange
Business Artists
http://www.businessartists.co.za|||By doing the SUM(...), which is an aggregate function, you are only
saying you want 1 row.
What do you really want, the size and usage of each file? Or the size
of the entire database?
-Jeff|||If you want the entire database, then there is no need for a loop use
the following:
SELECT * FROM #dbcc_showfilestats -- Debug
INSERT #dbstats (DB_Name, DB_Total_Size_in_MB,
DB_Used_Size_in_MB,
DB_Free_Size_in_MB, DB_Percent_Used)
SELECT @.dbname,
DB_Total_Size_in_MB =
sum(TotalExtents1)*65536.0/1048576.0,
DB_Used_Size_in_MB =
sum(UsedExtents1)*65536.0/1048576.0,
DB_Free_Size_in_MB =
sum(TotalExtents1-UsedExtents1)*65536.0/1048576.0,
DB_Percent_Used =
(sum(UsedExtents1)/sum(TotalExtents1))*100
FROM #dbcc_showfilestats
SELECT * FROM #dbstats
TRUNCATE TABLE #dbcc_showfilestats
server, collect the file sizes and return the values in a single
table. This script works for the most part, but there is an instance
when the script fails to collect the information properly. When there
are two or more data files the script only reports the first one twice.
Can someone take a look at this loop and tell me where the error is?
Thanks
-Matt-
/ ****************************************
**********
Script to calculate information about the Data Files
****************************************
**********/
DECLARE @.dbname varchar(50)
DECLARE @.string varchar(250)
SET @.string = ''
Declare @.rows int
CREATE TABLE #dbcc_showfilestats (
fileid tinyint,
FileGroup1 tinyint,
TotalExtents1 decimal (28, 2),
UsedExtents1 decimal (28, 2),
Name varchar(50),
FileName sysname )
CREATE TABLE #dbstats (
DB_Name varchar(50),
DB_Total_Size_in_MB decimal (28, 2),
DB_Used_Size_in_MB decimal (28, 2),
DB_Free_Size_in_MB decimal (28, 2),
DB_Percent_Used decimal (28, 2))
DECLARE dbnames_cursor CURSOR FOR SELECT name FROM master..sysdatabases
-- Collects all the DB name
OPEN dbnames_cursor
FETCH NEXT FROM dbnames_cursor INTO @.dbname
WHILE (@.@.fetch_status = 0)
BEGIN
SET @.string = 'use ' + @.dbname + ' DBCC SHOWFILESTATS'
INSERT #dbcc_showfilestats
EXEC (@.string)
SELECT * FROM #dbcc_showfilestats -- Debug
SELECT @.rows = count(*) from #dbcc_showfilestats
While @.rows > 0
BEGIN
INSERT #dbstats (DB_Name, DB_Total_Size_in_MB, DB_Used_Size_in_MB,
DB_Free_Size_in_MB, DB_Percent_Used)
SELECT @.dbname,
DB_Total_Size_in_MB = sum(TotalExtents1)*65536.0/1048576.0,
DB_Used_Size_in_MB = sum(UsedExtents1)*65536.0/1048576.0,
DB_Free_Size_in_MB =
sum(TotalExtents1-UsedExtents1)*65536.0/1048576.0,
DB_Percent_Used = sum(UsedExtents1/TotalExtents1)*100
FROM #dbcc_showfilestats
SELECT * FROM #dbstats
SET @.rows = @.rows - 1
END
TRUNCATE TABLE #dbcc_showfilestats
FETCH NEXT FROM dbnames_cursor INTO @.dbname
END
CLOSE dbnames_cursor
DEALLOCATE dbnames_cursor
SELECT * FROM #dbstats --Debug
DROP TABLE #dbstats --Debug
DROP TABLE #dbcc_showfilestats --DebugYou are selecting the same rows from #dbcc_showfilestats
every time through your 'while' loop.
Add
id int identity(1,1)
to your #dbcc_showfilestats table and change
FROM #dbcc_showfilestats
to
FROM #dbcc_showfilestats where id=@.rows|||Hi Matthew,
In addition to correctly adding a unique integer to distinguish rows in
your temp table as Mark has suggested, you may want to look at using
another temp table to loop through rather than using a cursor.
Cursors are very memory heavy in comparison to a looped through temp
table.
So instead your loop (in pseudo) would look more like:
-- SET UP 'CURSOR' TABLE
SELECT name INTO #databases FROM master..sysdatabases
-- DEFINE LOOPING PARAMETER
DECLARE @.unqName nvarchar(4000)
-- SELECT LOOPING PARAMETER
SELECT @.unqName = name FROM #databases
-- ENTER WHILE LOOP
WHILE LEN(@.unqName) > 0
BEGIN
-- PERFORM LOOP CODE
--DELETE ROW FROM LOOPING TABLE #databases
DELETE FROM #databases WHERE name = @.unqName
SELECT @.unqName = '' -- CLEAR VARIABLE
SELECT @.unqName = name FROM #databases
END
This will make a big difference in large looping scenarios - just try
it out.
Andrew La Grange
Business Artists
http://www.businessartists.co.za|||By doing the SUM(...), which is an aggregate function, you are only
saying you want 1 row.
What do you really want, the size and usage of each file? Or the size
of the entire database?
-Jeff|||If you want the entire database, then there is no need for a loop use
the following:
SELECT * FROM #dbcc_showfilestats -- Debug
INSERT #dbstats (DB_Name, DB_Total_Size_in_MB,
DB_Used_Size_in_MB,
DB_Free_Size_in_MB, DB_Percent_Used)
SELECT @.dbname,
DB_Total_Size_in_MB =
sum(TotalExtents1)*65536.0/1048576.0,
DB_Used_Size_in_MB =
sum(UsedExtents1)*65536.0/1048576.0,
DB_Free_Size_in_MB =
sum(TotalExtents1-UsedExtents1)*65536.0/1048576.0,
DB_Percent_Used =
(sum(UsedExtents1)/sum(TotalExtents1))*100
FROM #dbcc_showfilestats
SELECT * FROM #dbstats
TRUNCATE TABLE #dbcc_showfilestats
Help with a CASE statement.
I am trying to get this case statement to work where it will
concatenate the values into a single string.
If I place anything with an equals sign such as (SELECT @.status_message
= @.status_message + 'variable') it give me an error. Any ideas. It
probably obvious but I am failing to see it.
DECLARE @.status_message varchar(100)
Set @.status_message = ''
SELECT * from master..sysdatabases
SELECT name, dbid, status, cmptlevel, filename,
CASE WHEN (status & 1073741824) <> 0 THEN 'cleanly shutdown'
WHEN (status & 4194304) <> 0 THEN 'autoshrink'
WHEN (status & 32768) <> 0 THEN 'emergency mode'
WHEN (status & 4096) <> 0 THEN 'single user'
WHEN (status & 2048) <> 0 THEN 'dbo use only'
WHEN (status & 1024) <> 0 THEN 'read only'
WHEN (status & 512) <> 0 THEN 'offline'
WHEN (status & 256) <> 0 THEN 'not recovered'
WHEN (status & 128) <> 0 THEN 'recovering'
WHEN (status & 64) <> 0 THEN 'pre recovery'
WHEN (status & 32) <> 0 THEN 'loading'
WHEN (status & 16) <> 0 THEN 'torn page detection'
WHEN (status & 8) <> 0 THEN 'trunc. log on chkpt'
WHEN (status & 4) <> 0 THEN 'select into/bulkcopy'
WHEN (status & 1) <> 0 THEN 'autoclose'
ELSE 'Unknown'
end
from master..sysdatabasesYou cannot assign values to variables in the same select statement in which
you also return a result-set to the client.
E.g. this is not allowed:
select <column list>
,@.<variable> = <some column>
from <table>
Put the variable assignment ina separate query, then include it in the one
that returns the result to the client.
select @.<variable> = <some column>
from <table>
select <column list>
,@.<variable> as <variable name>
from <table>
Does that answer your question?
ML
http://milambda.blogspot.com/|||Sort of, I know what the problems is, I am looking for a work around,
or a different solution that might achieve the same results. someone
said that i sould thorugh it into a loop, so i might try that.|||Anyone. I must be totaly brainfried.|||>> I am trying to get this case statement to work where it will concatenate
Which values are you talking about? The string values in the THEN clause of
the CASE? Please elaborate on what you are trying to do here.
What is the error message? Which piece of code are you trying to run to
generate the error?
Anith|||I figured it out
/ ****************************************
*******************
Returns a the STATUS of all databases on a server in English
****************************************
*******************/
SELECT @.@.SERVERNAME AS SERVER, VERSION, LEFT(name,30) AS [Databases],
DBID,
SUBSTRING(CASE status & 1 WHEN 0 THEN '' ELSE ',Aautoclose' END +
CASE status & 4 WHEN 0 THEN '' ELSE ',Select Into / Bulk Copy' END +
CASE status & 8 WHEN 0 THEN '' ELSE ',Truncate Log on Checkpoint' END +
CASE status & 16 WHEN 0 THEN '' ELSE ',Torn Page Detection' END +
CASE status & 32 WHEN 0 THEN '' ELSE ',Loading' END +
CASE status & 64 WHEN 0 THEN '' ELSE ',Pre-Recovery' END +
CASE status & 128 WHEN 0 THEN '' ELSE ',Recovering' END +
CASE status & 256 WHEN 0 THEN '' ELSE ',Not Recovered' END +
CASE status & 512 WHEN 0 THEN '' ELSE ',Offline' END +
CASE status & 1024 WHEN 0 THEN '' ELSE ',Read Only' END +
CASE status & 2048 WHEN 0 THEN '' ELSE ',dbo USE Only' END +
CASE status & 4096 WHEN 0 THEN '' ELSE ',Single User' END +
CASE status & 32768 WHEN 0 THEN '' ELSE ',Emergency Mode' END +
CASE status & 4194304 WHEN 0 THEN '' ELSE ',autoshrink' END +
CASE status & 1073741824 WHEN 0 THEN '' ELSE ',Cleanly Shutdown' END,
2,8000) AS OPTIONS_1,
SUBSTRING(CASE status2 & 16384 WHEN 0 THEN '' ELSE ',ANSI NULL default'
END +
CASE status2 & 65536 WHEN 0 THEN '' ELSE ',concat NULL yields NULL' END
+
CASE status2 & 131072 WHEN 0 THEN '' ELSE ',recursive triggers' END +
CASE status2 & 1048576 WHEN 0 THEN '' ELSE ',default TO local cursor'
END +
CASE status2 & 8388608 WHEN 0 THEN '' ELSE ',quoted identifier' END +
CASE status2 & 33554432 WHEN 0 THEN '' ELSE ',cursor CLOSE on commit'
END +
CASE status2 & 67108864 WHEN 0 THEN '' ELSE ',ANSI NULLs' END +
CASE status2 & 268435456 WHEN 0 THEN '' ELSE ',ANSI warnings' END +
CASE status2 & 536870912 WHEN 0 THEN '' ELSE ',full text enabled' END,
2,8000) AS OPTIONS_2, CMPTLEVEL, FILENAME
FROM master..sysdatabases|||On 27 Feb 2006 14:48:32 -0800, Matthew wrote:
>I am trying to get this case statement to work where it will
>concatenate the values into a single string.
>If I place anything with an equals sign such as (SELECT @.status_message
>= @.status_message + 'variable') it give me an error. Any ideas. It
>probably obvious but I am failing to see it.
>DECLARE @.status_message varchar(100)
>Set @.status_message = ''
>SELECT * from master..sysdatabases
>SELECT name, dbid, status, cmptlevel, filename,
>CASE WHEN (status & 1073741824) <> 0 THEN 'cleanly shutdown'
> WHEN (status & 4194304) <> 0 THEN 'autoshrink'
> WHEN (status & 32768) <> 0 THEN 'emergency mode'
> WHEN (status & 4096) <> 0 THEN 'single user'
> WHEN (status & 2048) <> 0 THEN 'dbo use only'
> WHEN (status & 1024) <> 0 THEN 'read only'
> WHEN (status & 512) <> 0 THEN 'offline'
> WHEN (status & 256) <> 0 THEN 'not recovered'
> WHEN (status & 128) <> 0 THEN 'recovering'
> WHEN (status & 64) <> 0 THEN 'pre recovery'
> WHEN (status & 32) <> 0 THEN 'loading'
> WHEN (status & 16) <> 0 THEN 'torn page detection'
> WHEN (status & 8) <> 0 THEN 'trunc. log on chkpt'
> WHEN (status & 4) <> 0 THEN 'select into/bulkcopy'
> WHEN (status & 1) <> 0 THEN 'autoclose'
> ELSE 'Unknown'
>end
>from master..sysdatabases
Hi Matthew,
Though concatenating these in a single string is presentation and
shouyld therefor be handled in the presentation layer, I'll give you a
working SQL solution:
SELECT name, dbid, status, cmptlevel, filename,
CASE WHEN (status & 1073741824) <> 0 THEN 'cleanly shutdown'
ELSE '' END
+ CASE WHEN (status & 4194304) <> 0 THEN 'autoshrink'
ELSE '' END
+ CASE WHEN (status & 32768) <> 0 THEN 'emergency mode'
ELSE '' END
(...)
+ CASE WHEN (status & 1) <> 0 THEN 'autoclose'
ELSE '' END
from master..sysdatabases
Hugo Kornelis, SQL Server MVP|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:81e902hircfvvlacle0bg16ois08j7kmoq@.
4ax.com...
>.
> Though concatenating these in a single string is presentation and
> shouyld therefor be handled in the presentation layer,
>.
Hmm where have I heard this same idea before?
Once upon a time many were saying the same thing about
row numbering or worse line numbering.Surely this was
something to be done on the client whereas it had no
place being done on the server.(Not to mention the situation
was further muddled when the same voices used the very same
constructs as intermediate results in a query).Now along comes
Sql 2005 with some Sql-99 analytic functions like row_number()
and viola row numbering is no longer *presentation* and it's
perfectly fine to do on the server.To keep the analytics
company, 2005 introduces some more xml.Now we learn that
concatenating over rows can be done explicitly with it.
Presentation now has now become transformed into a serve-ice.
Since the underlying nature of 2005 hasn't changed
save for the mechanisms to do these things, it is now
*expedient* to do them on the server.Of course the shift
over even includes the dreaded crosstab with the ingenius
implementation of PIVOT.One must marvel at the ease of how
MS can change presentation to serve-ice:) But this shift may
come with some head scratching.Least some users wonder why
they can't simulate the serve-ice of 2005 to overcome the
presentation inherent in 2000.But one of the great things
about expediency is that it takes so shallow an explanation.
Flip flopping is alive and well in the world of sql.And for those
who actually study the subject academically I would say
't'where ignorance is bliss,t'is folly to be wise':)
$.03 from
www.rac4sql.net|||Well I guess as "proof" in SQL 2005 running the Execution Plan, the
cost associated for running query is exactly the same for both. So I
really boils down, which way is the "more correct" way to code.
e.g. which is better in practice.|||'which is better in practice' is just another way of saying what
is the most expedient way.Whatever works best for you:)
"Matthew" <MKruer@.gmail.com> wrote in message
news:1141230238.399541.6200@.z34g2000cwc.googlegroups.com...
> Well I guess as "proof" in SQL 2005 running the Execution Plan, the
> cost associated for running query is exactly the same for both. So I
> really boils down, which way is the "more correct" way to code.
> e.g. which is better in practice.
>
concatenate the values into a single string.
If I place anything with an equals sign such as (SELECT @.status_message
= @.status_message + 'variable') it give me an error. Any ideas. It
probably obvious but I am failing to see it.
DECLARE @.status_message varchar(100)
Set @.status_message = ''
SELECT * from master..sysdatabases
SELECT name, dbid, status, cmptlevel, filename,
CASE WHEN (status & 1073741824) <> 0 THEN 'cleanly shutdown'
WHEN (status & 4194304) <> 0 THEN 'autoshrink'
WHEN (status & 32768) <> 0 THEN 'emergency mode'
WHEN (status & 4096) <> 0 THEN 'single user'
WHEN (status & 2048) <> 0 THEN 'dbo use only'
WHEN (status & 1024) <> 0 THEN 'read only'
WHEN (status & 512) <> 0 THEN 'offline'
WHEN (status & 256) <> 0 THEN 'not recovered'
WHEN (status & 128) <> 0 THEN 'recovering'
WHEN (status & 64) <> 0 THEN 'pre recovery'
WHEN (status & 32) <> 0 THEN 'loading'
WHEN (status & 16) <> 0 THEN 'torn page detection'
WHEN (status & 8) <> 0 THEN 'trunc. log on chkpt'
WHEN (status & 4) <> 0 THEN 'select into/bulkcopy'
WHEN (status & 1) <> 0 THEN 'autoclose'
ELSE 'Unknown'
end
from master..sysdatabasesYou cannot assign values to variables in the same select statement in which
you also return a result-set to the client.
E.g. this is not allowed:
select <column list>
,@.<variable> = <some column>
from <table>
Put the variable assignment ina separate query, then include it in the one
that returns the result to the client.
select @.<variable> = <some column>
from <table>
select <column list>
,@.<variable> as <variable name>
from <table>
Does that answer your question?
ML
http://milambda.blogspot.com/|||Sort of, I know what the problems is, I am looking for a work around,
or a different solution that might achieve the same results. someone
said that i sould thorugh it into a loop, so i might try that.|||Anyone. I must be totaly brainfried.|||>> I am trying to get this case statement to work where it will concatenate
Which values are you talking about? The string values in the THEN clause of
the CASE? Please elaborate on what you are trying to do here.
What is the error message? Which piece of code are you trying to run to
generate the error?
Anith|||I figured it out
/ ****************************************
*******************
Returns a the STATUS of all databases on a server in English
****************************************
*******************/
SELECT @.@.SERVERNAME AS SERVER, VERSION, LEFT(name,30) AS [Databases],
DBID,
SUBSTRING(CASE status & 1 WHEN 0 THEN '' ELSE ',Aautoclose' END +
CASE status & 4 WHEN 0 THEN '' ELSE ',Select Into / Bulk Copy' END +
CASE status & 8 WHEN 0 THEN '' ELSE ',Truncate Log on Checkpoint' END +
CASE status & 16 WHEN 0 THEN '' ELSE ',Torn Page Detection' END +
CASE status & 32 WHEN 0 THEN '' ELSE ',Loading' END +
CASE status & 64 WHEN 0 THEN '' ELSE ',Pre-Recovery' END +
CASE status & 128 WHEN 0 THEN '' ELSE ',Recovering' END +
CASE status & 256 WHEN 0 THEN '' ELSE ',Not Recovered' END +
CASE status & 512 WHEN 0 THEN '' ELSE ',Offline' END +
CASE status & 1024 WHEN 0 THEN '' ELSE ',Read Only' END +
CASE status & 2048 WHEN 0 THEN '' ELSE ',dbo USE Only' END +
CASE status & 4096 WHEN 0 THEN '' ELSE ',Single User' END +
CASE status & 32768 WHEN 0 THEN '' ELSE ',Emergency Mode' END +
CASE status & 4194304 WHEN 0 THEN '' ELSE ',autoshrink' END +
CASE status & 1073741824 WHEN 0 THEN '' ELSE ',Cleanly Shutdown' END,
2,8000) AS OPTIONS_1,
SUBSTRING(CASE status2 & 16384 WHEN 0 THEN '' ELSE ',ANSI NULL default'
END +
CASE status2 & 65536 WHEN 0 THEN '' ELSE ',concat NULL yields NULL' END
+
CASE status2 & 131072 WHEN 0 THEN '' ELSE ',recursive triggers' END +
CASE status2 & 1048576 WHEN 0 THEN '' ELSE ',default TO local cursor'
END +
CASE status2 & 8388608 WHEN 0 THEN '' ELSE ',quoted identifier' END +
CASE status2 & 33554432 WHEN 0 THEN '' ELSE ',cursor CLOSE on commit'
END +
CASE status2 & 67108864 WHEN 0 THEN '' ELSE ',ANSI NULLs' END +
CASE status2 & 268435456 WHEN 0 THEN '' ELSE ',ANSI warnings' END +
CASE status2 & 536870912 WHEN 0 THEN '' ELSE ',full text enabled' END,
2,8000) AS OPTIONS_2, CMPTLEVEL, FILENAME
FROM master..sysdatabases|||On 27 Feb 2006 14:48:32 -0800, Matthew wrote:
>I am trying to get this case statement to work where it will
>concatenate the values into a single string.
>If I place anything with an equals sign such as (SELECT @.status_message
>= @.status_message + 'variable') it give me an error. Any ideas. It
>probably obvious but I am failing to see it.
>DECLARE @.status_message varchar(100)
>Set @.status_message = ''
>SELECT * from master..sysdatabases
>SELECT name, dbid, status, cmptlevel, filename,
>CASE WHEN (status & 1073741824) <> 0 THEN 'cleanly shutdown'
> WHEN (status & 4194304) <> 0 THEN 'autoshrink'
> WHEN (status & 32768) <> 0 THEN 'emergency mode'
> WHEN (status & 4096) <> 0 THEN 'single user'
> WHEN (status & 2048) <> 0 THEN 'dbo use only'
> WHEN (status & 1024) <> 0 THEN 'read only'
> WHEN (status & 512) <> 0 THEN 'offline'
> WHEN (status & 256) <> 0 THEN 'not recovered'
> WHEN (status & 128) <> 0 THEN 'recovering'
> WHEN (status & 64) <> 0 THEN 'pre recovery'
> WHEN (status & 32) <> 0 THEN 'loading'
> WHEN (status & 16) <> 0 THEN 'torn page detection'
> WHEN (status & 8) <> 0 THEN 'trunc. log on chkpt'
> WHEN (status & 4) <> 0 THEN 'select into/bulkcopy'
> WHEN (status & 1) <> 0 THEN 'autoclose'
> ELSE 'Unknown'
>end
>from master..sysdatabases
Hi Matthew,
Though concatenating these in a single string is presentation and
shouyld therefor be handled in the presentation layer, I'll give you a
working SQL solution:
SELECT name, dbid, status, cmptlevel, filename,
CASE WHEN (status & 1073741824) <> 0 THEN 'cleanly shutdown'
ELSE '' END
+ CASE WHEN (status & 4194304) <> 0 THEN 'autoshrink'
ELSE '' END
+ CASE WHEN (status & 32768) <> 0 THEN 'emergency mode'
ELSE '' END
(...)
+ CASE WHEN (status & 1) <> 0 THEN 'autoclose'
ELSE '' END
from master..sysdatabases
Hugo Kornelis, SQL Server MVP|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:81e902hircfvvlacle0bg16ois08j7kmoq@.
4ax.com...
>.
> Though concatenating these in a single string is presentation and
> shouyld therefor be handled in the presentation layer,
>.
Hmm where have I heard this same idea before?
Once upon a time many were saying the same thing about
row numbering or worse line numbering.Surely this was
something to be done on the client whereas it had no
place being done on the server.(Not to mention the situation
was further muddled when the same voices used the very same
constructs as intermediate results in a query).Now along comes
Sql 2005 with some Sql-99 analytic functions like row_number()
and viola row numbering is no longer *presentation* and it's
perfectly fine to do on the server.To keep the analytics
company, 2005 introduces some more xml.Now we learn that
concatenating over rows can be done explicitly with it.
Presentation now has now become transformed into a serve-ice.
Since the underlying nature of 2005 hasn't changed
save for the mechanisms to do these things, it is now
*expedient* to do them on the server.Of course the shift
over even includes the dreaded crosstab with the ingenius
implementation of PIVOT.One must marvel at the ease of how
MS can change presentation to serve-ice:) But this shift may
come with some head scratching.Least some users wonder why
they can't simulate the serve-ice of 2005 to overcome the
presentation inherent in 2000.But one of the great things
about expediency is that it takes so shallow an explanation.
Flip flopping is alive and well in the world of sql.And for those
who actually study the subject academically I would say
't'where ignorance is bliss,t'is folly to be wise':)
$.03 from
www.rac4sql.net|||Well I guess as "proof" in SQL 2005 running the Execution Plan, the
cost associated for running query is exactly the same for both. So I
really boils down, which way is the "more correct" way to code.
e.g. which is better in practice.|||'which is better in practice' is just another way of saying what
is the most expedient way.Whatever works best for you:)
"Matthew" <MKruer@.gmail.com> wrote in message
news:1141230238.399541.6200@.z34g2000cwc.googlegroups.com...
> Well I guess as "proof" in SQL 2005 running the Execution Plan, the
> cost associated for running query is exactly the same for both. So I
> really boils down, which way is the "more correct" way to code.
> e.g. which is better in practice.
>
Friday, March 9, 2012
HELP w/ Stored Procedure
Can someone explain what I'm doing wrong?
I am passing values to the SPROC and am trying to do the following:
1.) I check to see if the record already exists in the MEMBERS table
NO -> I want to insert a new record and then set @.MEMBER_ID = SCOPE_IDENTITY
YES-> I want to skip the insert and set @.MEMBER_ID to the existing MEMBER_ID
2.) I check to see if the record already exists in the SW_REQUESTS table
NO -> I want to insert a new record (Insert @.MEMBER_ID)
YES -> I want to skip the insert and close out the SPROC
Here is my code that doesn't work. I get an error when setting @.MEMBER_ID = MEMBER_ID:
You need to declare @.Member_ID as an OUTPUT parameter, if you expect to get it back:
CREATE PROCEDURE b4b_sw_request@.FName as varchar(50)= NULL,
@.LName as varchar(50)=NULL,
@.Address1 as varchar(100) = NULL,
@.Address2 as varchar(100) = NULL,
@.City as varchar(50) = NULL,
@.State as char(2) = NULL,
@.Zip as char(5) = NULL,
@.Email as varchar(100) = NULL,
@.Send_Updates as smallint = '0',
@.MEMBER_ID as int = '0'AS
SET NOCOUNT ON
IF EXISTS
(SELECT MEMBER_ID from MEMBERS WHERE FName = @.FName AND LName = @.LName AND Address1 = @.Address1 AND Zip = @.Zip)
BEGIN
SET @.MEMBER_ID = MEMBER_ID
END
ELSE
BEGIN
INSERT INTO MEMBERS
(FName, LName, Address1, Address2, City, State, Zip, Email)
VALUES
(@.FName, @.LName, @.Address1, @.Address2, @.City, @.State, @.Zip, @.Email)
SELECT @.MEMBER_ID=SCOPE_IDENTITY()
ENDIF EXISTS
(SELECT MEMBER_ID FROM SW_REQUESTS WHERE MEMBER_ID = @.MEMBER_ID)
BEGIN
Print ''
END
ELSE
BEGIN
INSERT INTO SW_REQUESTS
(MEMBER_ID, Send_Updates)
Values
(@.MEMBER_ID, @.Send_Updates)
END
GO
CREATE PROCEDURE b4b_sw_request
@.FName as varchar(50)= NULL,
@.LName as varchar(50)=NULL,
@.Address1 as varchar(100) = NULL,
@.Address2 as varchar(100) = NULL,
@.City as varchar(50) = NULL,
@.State as char(2) = NULL,
@.Zip as char(5) = NULL,
@.Email as varchar(100) = NULL,
@.Send_Updates as smallint = '0',
@.MEMBER_ID as int = '0' OUTPUT
MEMBERS does have a IDENTITY column, correct?
This section of code:
IF EXISTS
(SELECT MEMBER_ID from MEMBERS WHERE FName = @.FName AND LName = @.LName AND Address1 = @.Address1 AND Zip = @.Zip)
BEGIN
SET @.MEMBER_ID = MEMBER_ID
END
should be like this:
IF EXISTS
(SELECT * from MEMBERS WHERE FName = @.FName AND LName = @.LName AND Address1 = @.Address1 AND Zip = @.Zip)
BEGIN
SELECT @.Member_ID=MEMBER_ID from MEMBERS WHERE FName = @.FName AND LName = @.LName AND Address1 = @.Address1 AND Zip = @.Zip
SET @.MEMBER_ID = MEMBER_ID
END
You need to actually select the @.Member_ID value somewhere. Alternately, you could do the select where @.Member_ID=Member_ID outside the IF and then check IsNull(@.Member_ID,0)<>0|||I do have a IDENTITY column in the table MEMBERS. It is MEMBER_ID. When I made the changes you suggested, I still get an error message "Invalid Column Name MEMBER_ID"
Here is the code:
|||Sorry. Loose this line:
CREATE PROCEDURE b4b_sw_request@.FName as varchar(50)= NULL,
@.LName as varchar(50)=NULL,
@.Address1 as varchar(100) = NULL,
@.Address2 as varchar(100) = NULL,
@.City as varchar(50) = NULL,
@.State as char(2) = NULL,
@.Zip as char(5) = NULL,
@.Email as varchar(100) = NULL,
@.Send_Updates as smallint = '0',
@.MEMBER_ID as int = '0' OUTPUTAS
SET NOCOUNT ON
IF EXISTS(SELECT * from MEMBERS WHERE FName = @.FName AND LName = @.LName AND Address1 = @.Address1 AND Zip = @.Zip)
BEGIN
SELECT @.Member_ID=MEMBER_ID from MEMBERS WHERE FName = @.FName AND LName = @.LName AND Address1 = @.Address1 AND Zip = @.Zip
SET @.MEMBER_ID = MEMBER_ID
END
ELSE
BEGIN
INSERT INTO MEMBERS
(FName, LName, Address1, Address2, City, State, Zip, Email)
VALUES
(@.FName, @.LName, @.Address1, @.Address2, @.City, @.State, @.Zip, @.Email)
SELECT @.MEMBER_ID=SCOPE_IDENTITY()
ENDIF EXISTS
(SELECT MEMBER_ID FROM SW_REQUESTS WHERE MEMBER_ID = @.MEMBER_ID)
BEGIN
Print ''
END
ELSE
BEGIN
INSERT INTO SW_REQUESTS
(MEMBER_ID, Send_Updates)
Values
(@.MEMBER_ID, @.Send_Updates)
END
GO
SET @.MEMBER_ID = MEMBER_ID
If you still get an error, please specify exactly what line gived the error.|||Thanks Doug. I had just removed it and tested it when I got your message. Works great!
Wednesday, March 7, 2012
Help tranlating values and Nulls
Hi,
I'm appending selections from 2 tables together and putting the results in a
3rd table. Some of the columns in the tables can be NULL or ' '. I would
like to convert anything that is not 0-9,a-z,A-Z in all positions into
something, say "~".
I'm new to SQL Server. I've tried a Case statement, an IF statement and
can't seem to get it to work. I've used LIKE '[0-z]' in both of those and i
n
the few cases that I didn't get errors, it just didn't work.
Can anyone help me with this?
Thanks,
ArtWe can help you if you could at least post some sample data, preferably with
DDL, and specify expected results.
So far you've specified that any value which is not a letter, be it a upper
or lowercase character, or a number should be transformed to the tilde
character "~".
In other words: if the column in question contains any data other than
letters and numbers its value must be replaced by the "~" character.
Or is it like this: any character other than letters and numbers are to be
replaced by the "~" character.
See the confusion? Or is it just me...?
ML|||ML,
Thanks for getting back to me. What I want to do is:
Look at a value from a particular column. If any character in the value is
not [0-z] then replace the entire value by ~.
My purpose in this is that the incoming data is a mixture of valid values,
empty strings and nulls -- so far. I have finally found a way of fixing the
NULLs. I don't know for sure what else I might find in that column. So
rather than look for NULLs or empty strings and adjusting them, I thought it
might be better to look for anything that isn't valid.
Thanks,
Art
"ML" wrote:
> We can help you if you could at least post some sample data, preferably wi
th
> DDL, and specify expected results.
> So far you've specified that any value which is not a letter, be it a uppe
r
> or lowercase character, or a number should be transformed to the tilde
> character "~".
> In other words: if the column in question contains any data other than
> letters and numbers its value must be replaced by the "~" character.
> Or is it like this: any character other than letters and numbers are to be
> replaced by the "~" character.
> See the confusion? Or is it just me...?
>
> ML|||Good data starts with validation. :)
A CASE function will take care of what you need:
case
when <column> like '%[^A-Za-z0-9]%' or <column> = ''
then '~'
else isnull(<column>, '~')
end
Includes handling null values.
ML|||ML,
Thanks for the help.
Art
"ML" wrote:
> Good data starts with validation. :)
> A CASE function will take care of what you need:
> case
> when <column> like '%[^A-Za-z0-9]%' or <column> = ''
> then '~'
> else isnull(<column>, '~')
> end
> Includes handling null values.
>
> ML
I'm appending selections from 2 tables together and putting the results in a
3rd table. Some of the columns in the tables can be NULL or ' '. I would
like to convert anything that is not 0-9,a-z,A-Z in all positions into
something, say "~".
I'm new to SQL Server. I've tried a Case statement, an IF statement and
can't seem to get it to work. I've used LIKE '[0-z]' in both of those and i
n
the few cases that I didn't get errors, it just didn't work.
Can anyone help me with this?
Thanks,
ArtWe can help you if you could at least post some sample data, preferably with
DDL, and specify expected results.
So far you've specified that any value which is not a letter, be it a upper
or lowercase character, or a number should be transformed to the tilde
character "~".
In other words: if the column in question contains any data other than
letters and numbers its value must be replaced by the "~" character.
Or is it like this: any character other than letters and numbers are to be
replaced by the "~" character.
See the confusion? Or is it just me...?
ML|||ML,
Thanks for getting back to me. What I want to do is:
Look at a value from a particular column. If any character in the value is
not [0-z] then replace the entire value by ~.
My purpose in this is that the incoming data is a mixture of valid values,
empty strings and nulls -- so far. I have finally found a way of fixing the
NULLs. I don't know for sure what else I might find in that column. So
rather than look for NULLs or empty strings and adjusting them, I thought it
might be better to look for anything that isn't valid.
Thanks,
Art
"ML" wrote:
> We can help you if you could at least post some sample data, preferably wi
th
> DDL, and specify expected results.
> So far you've specified that any value which is not a letter, be it a uppe
r
> or lowercase character, or a number should be transformed to the tilde
> character "~".
> In other words: if the column in question contains any data other than
> letters and numbers its value must be replaced by the "~" character.
> Or is it like this: any character other than letters and numbers are to be
> replaced by the "~" character.
> See the confusion? Or is it just me...?
>
> ML|||Good data starts with validation. :)
A CASE function will take care of what you need:
case
when <column> like '%[^A-Za-z0-9]%' or <column> = ''
then '~'
else isnull(<column>, '~')
end
Includes handling null values.
ML|||ML,
Thanks for the help.
Art
"ML" wrote:
> Good data starts with validation. :)
> A CASE function will take care of what you need:
> case
> when <column> like '%[^A-Za-z0-9]%' or <column> = ''
> then '~'
> else isnull(<column>, '~')
> end
> Includes handling null values.
>
> ML
HELP to write stored procedure whose values are calculated automatically in database
Hi frdz,
I m creating my web-application in asp.net with C# 2005 and using sql server 2005.
I have created the stored procedure for the insert,update.
I want to know how to write the mathematical calculations in the stored procedure..
Pls tell me from the below stored procedure were i m making the mistake ??
As the discount and the total amount are not calculated by itself...and stored in the database
How to convert the
@.discpercentnumeric(5,2) to
@.discpercent ="NoDiscount" should be displayed when no discount is being given to the customers...
ALTER PROCEDURE CalculationStoredProcedure @.accountidint output, @.accountnamevarchar(20), @.opbalnumeric(10, 2), @.opbalcodechar(2), @.totalnumeric(10, 2), @.clbalnumeric(10, 2), @.clbalcodechar(2), @.discpercentnumeric(5,2), @.discamtnumeric(10, 2)asbeginset nocount on if @.opbalISNULL OR @.opbal = 0beginselect @.opbal=0select @.opbalcode=' 'select @.clbal= 0select @.total= 0select @.clbalcode=' ' @.discpercent ="NoDiscount" @.discamt=0end select @.accountid =isnull(max(accountid),0) + 1from accountmasterselect @.total=@.opbal - @.clbalfrom accountmasterselect @.discamt=@.total* @.discpercent/100from accountmasterbegin insert into accountmaster(accountname,opbal,opbalcode,clbal,clbalcode )values ( @.accountname,@.opbal,@.opbalcode,@.clbal,@.clbalcode )end set nocount offend
Thanxs in adv...
Hello my friend,
It appears you are using 1 field for 2 purposes; numeric calculation and text display. I would avoid doing this.
Declare another parameter (e.g. @.DiscDisplay) to hold a copy of the @.discpercent variable when a discount is being applied, and have it set to 'NoDiscount' if @.discpercent is 0. Then just display @.DiscDisplay on the web page in both situations.
Kind regards
Scotty
|||
thanxs for the reply...
can u pls help to calculate this for my above stored procedure its not working..
select @.total=@.opbal - @.clbalfrom accountmaster
select @.discamt=@.total* @.discpercent/100from accountmaster
|||
Looks like there is some confusion here. You are calculating a percentage from values in parameters. Then why do you have a "..FROM AccountMaster" in your SELECT?
select @.discamt=(@.opbal - @.clbal )* @.discpercent/100
should do.
select @.total=(@.opbal - @.clbal )
not working in database it shows me NULL only
I tried with this also but don't work pls help me...
if (@.total IS NULL or @.total = 0)
begin
select @.total=(@.opbal - @.clbal )
end
thanxs for ur replies....
Could it be because either @.opbal or @.clbal is null?
I would use the following: -
IF (ISNULL(@.Total, 0) = 0)
BEGIN
SET @.total = (ISNULL(@.opbal, 0) - ISNULL(@.clbal, 0))
END
SELECT @.Total
Kind regards
Scotty
|||
thanxs very much again...
Tried with ur suggestion...but don't work...why it does not returns/set/stores the value into the database...??
What can be the problem ?? Not understanding...i think everything is correct now...putting all of ur suggestions..together but not able to get the solution
Hello again Sheenaa,
You say that you have put it all together based on our help. Can you show us all of the procedure that you have put together and we can see if you have made any mistakes.
Kind regards
Scotty
Help to write a query to kamal
hello,
I have a Question Paper table
Create Table QPaper (QPID, QPName,SubjectSLNo,Type, NoOfQues)
Data is like
Insert Into QPaper Values (1, 'UNIT1', 1,'Objective', 10)
Insert Into QPaper Values (2, 'UNIT1', 1,'Descriptive', 5)
Insert Into QPaper Values (3, 'UNIT2', 1,'Objective', 2)
Insert Into QPaper Values (4, 'UNIT2', 1,'Objective', 15)
My Output should be
1, 'UNIT1', 1,'Objective', 1, 1
1, 'UNIT1', 1,'Objective', 1, 2
1, 'UNIT1', 1,'Objective', 1, 3
1, 'UNIT1', 1,'Objective', 1, 4
--
--
--
1, 'UNIT1', 1,'Objective', 1, 10
2, 'UNIT1', 1,'Descriptive', 5,1
2, 'UNIT1', 1,'Descriptive', 5,2
--
--
--
2, 'UNIT1', 1,'Descriptive', 5,5
3, 'UNIT2', 1,'Objective', 2,1
3, 'UNIT2', 1,'Objective', 2,2
4, 'UNIT2', 1,'Objective', 15,1
4, 'UNIT2', 1,'Objective', 15,2
4, 'UNIT2', 1,'Objective', 15,3
--
--
--
--
--
4, 'UNIT2', 1,'Objective', 15,15
thanks on advance
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Try,
use northwind
go
select
identity(int, 1, 1) as number
into
number
from
sysobjects as a cross join sysobjects as b
go
Create Table QPaper (QPID int, QPName varchar(25) ,SubjectSLNo int,Type
varchar(25), NoOfQues int)
go
Insert Into QPaper Values (1, 'UNIT1', 1,'Objective', 10)
Insert Into QPaper Values (2, 'UNIT1', 1,'Descriptive', 5)
Insert Into QPaper Values (3, 'UNIT2', 1,'Objective', 2)
Insert Into QPaper Values (4, 'UNIT2', 1,'Objective', 15)
go
select
a.QPID,
a.QPName,
a.SubjectSLNo,
a.Type,
a.NoOfQues,
n.number
from
QPaper as a
inner join
number as n
on n.number <= a.NoOfQues
order by
a.QPID,
n.number
go
drop Table QPaper, number
go
AMB
"kamal hussain" wrote:
>
> hello,
> I have a Question Paper table
> Create Table QPaper (QPID, QPName,SubjectSLNo,Type, NoOfQues)
> Data is like
> Insert Into QPaper Values (1, 'UNIT1', 1,'Objective', 10)
> Insert Into QPaper Values (2, 'UNIT1', 1,'Descriptive', 5)
> Insert Into QPaper Values (3, 'UNIT2', 1,'Objective', 2)
> Insert Into QPaper Values (4, 'UNIT2', 1,'Objective', 15)
> My Output should be
> 1, 'UNIT1', 1,'Objective', 1, 1
> 1, 'UNIT1', 1,'Objective', 1, 2
> 1, 'UNIT1', 1,'Objective', 1, 3
> 1, 'UNIT1', 1,'Objective', 1, 4
> --
> --
> --
> 1, 'UNIT1', 1,'Objective', 1, 10
> 2, 'UNIT1', 1,'Descriptive', 5,1
> 2, 'UNIT1', 1,'Descriptive', 5,2
> --
> --
> --
> 2, 'UNIT1', 1,'Descriptive', 5,5
> 3, 'UNIT2', 1,'Objective', 2,1
> 3, 'UNIT2', 1,'Objective', 2,2
> 4, 'UNIT2', 1,'Objective', 15,1
> 4, 'UNIT2', 1,'Objective', 15,2
> 4, 'UNIT2', 1,'Objective', 15,3
> --
> --
> --
> --
> --
> 4, 'UNIT2', 1,'Objective', 15,15
>
> thanks on advance
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
>
Sunday, February 26, 2012
Help talking to stored proc
Can someone please lend a hand.
I am a total noob at this .NET/ASP stuff. I simply need help passing and returning values to a stored procedure.
Here’s my sp.
ALTER PROCEDURE [dbo].[returnIdUser]-- Add the parameters for the stored procedure here @.sessionUservarchar(25)OUTPUTASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements.SET NOCOUNT ON;-- Insert statements for procedure hereSELECT idUserFROM tblUsersWHERE domainUser = @.sessionUserEND
So when the user logs into to the default page (windows authentication) I capture their domain\login in a session variable (sessionUser). On the next page, when the page loads I need to pass this to my stored proc and get the idUser in return.
Here is what I have for the page load so far.
try{ SqlConnection cxnReturnID =new SqlConnection(ConfigurationManager.ConnectionStrings["cxnLeaveRecords"].ConnectionString); SqlCommand cmdReturnID =new SqlCommand("returnIdUser", cxnReturnID); cmdReturnID.CommandType = CommandType.StoredProcedure; cmdReturnID.Parameters.Add("@.sessionUser", SqlDbType.VarChar, 25).Direction = ParameterDirection.Output; cmdReturnID.Connection.Open(); cmdReturnID.ExecuteReader(); cmdReturnID.Connection.Close(); cmdReturnID.Connection.Dispose(); Session["sessionUserID"] = cmdReturnID.Parameters["idUser"].Value;}catch (Exception ex){ lblStatus.Text = ex.Message;}Of course this fails cause I don’t know what I’m doing. My error label shows the following: "An SqlParameter with ParameterName 'idUser' is not contained by this SqlParameterCollection."
Can someone point me to what I’m doing wrong?
Hmm.. You mixed up every thing here..
First you need to a pass an input parameter and expect an out put paremeter. So, your stored procedure should have two paramets defined in it.. You have only one
And also, in your c# code, you need to assign the value of session variable to the input parameter, which you have not.
Anyway, try this..
ALTER PROCEDURE [dbo].[returnIdUser]
-- Add the parameters for the stored procedure here
@.sessionUservarchar(25),
@.idOUT varchar(25) outAS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;-- Insert statements for procedure here
set idOUT = (SELECT idUserFROM tblUsersWHERE domainUser = @.sessionUser)
return @.idOUTEND
and your code to
cmdReturnID.Parameters.Add("@.sessionUser", SqlDbType.VarChar, 25).Direction = ParameterDirection.Input;command.Parameters["@.sessionUser"].Value = Session["UserName"]
cmdReturnID.Parameters.Add("@.idOUT", SqlDbType.VarChar, 25).Direction = ParameterDirection.Output;SqlDataReader reader = cmdReturnID.ExecuteReader();
reader.close();
Session["sessionUserID"] = cmdReturnID.Parameters["idOUT"].Value;
Even though, the code looks real, its not... I typed by hand, so beware of small errors...|||
Hi , in your example I guess you want to pass a session user into the stored procedure and return idUser. In this case your stored procedure should be :
ALTER PROCEDURE [dbo].[returnIdUser]-- Add the parameters for the stored procedure here (@.sessionUservarchar(25))ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements.SET NOCOUNT ON;-- Insert statements for procedure hereSELECT idUserFROM tblUsersWHERE domainUser = @.sessionUserEND
And here is the modified code:
try { SqlConnection cxnReturnID =new SqlConnection(ConfigurationManager.ConnectionStrings["cxnLeaveRecords"].ConnectionString); SqlCommand cmdReturnID =new SqlCommand("returnIdUser", cxnReturnID); cmdReturnID.CommandType = CommandType.StoredProcedure; cmdReturnID.Parameters.Add("@.sessionUser", SqlDbType.VarChar, 25); cmdReturnID.Connection.Open(); SqlDataReader sqldr = cmdReturnID.ExecuteReader(); cmdReturnID.Connection.Close(); cmdReturnID.Connection.Dispose(); Session["sessionUserID"] = sqldr.GetString(0); }catch (Exception ex) { lblStatus.Text = ex.Message; } Here I use sessionUser as an input value and read idUser with SqlDataReader.Hope this helps.|||it took some alterations, but finally got it to work. Thanks to both of you!
Friday, February 24, 2012
Help sending email
My code below works fine when run from my pc (changed all the values for
obvious reasons). The code is placed inside a DTS task via VBS scripting.
But when I try to run directly from the server where sqlserver is installed,
the script fails.
obvious reasons). The code is placed inside a DTS task via VBS scripting.
But when I try to run directly from the server where sqlserver is installed,
the script fails.
I have SMTP running, but there is no outlook installed.
Can someone please advise what I am missing.
Thanks
Bob
Set objEmail = CreateObject("CDO.Message")
objEmail.From = "send@.test.com"
objEmail.To = "receive@.test.com"
objEmail.Subject = "TEST SUBJECT"
objEmail.AddAttachment "\\server\test.csv"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
ration/sendusing") = 2
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
ration/smtpserver") = "SERVER_NAME"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
ration/smtpauthenticate") = 1
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
ration/sendusername") = "username"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
ration/sendpassword") = "userpwd"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
ration/smtpserverport") = 25
objEmail.Configuration.Fields.Update
objEmail.Send
set objEmail = nothingHi B
One thing you might try is change
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/sendusing") = 2
to
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/sendusing") = "2"
quotes around the 2.
I spent several hours a week ago trying the very same thing and that made
all the difference.
Also is \\server\test.csv accessible from the server you are running this
on?
Here is the full text of the DTS Package I wrote. Note that I don't think
all the fields you included are necessary.
'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************
Function Main()
email_alert "dchristo@.yahoo.com", "George_Bush@.whitehouse.gov","Test
Subject", "Test Body"
Main = DTSTaskExecResult_Success
End Function
Sub email_alert(strTo, strFrom, strSubject, strBody)
Dim iConf 'As CDO.Configuration
Dim imsg 'As CDO.Message
Dim flds
Set imsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
Set flds = iConf.Fields
'The http://schemas.microsoft.com/cdo/configuration/ namespace defines
the majority of fields used to set configurations for various CDO objects.
We set and update the following three fields (SendUsing, SMTP_SERVER, and
TimeOut) of the Configuration object:
With flds
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") =
"2"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") =
"smtp-server.mn.rr.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout")
= 10
.Update
End With
Set imsg.Configuration = iConf
imsg.To = strTo
imsg.From = strFrom
imsg.Subject = strSubject
imsg.TextBody = strBody
imsg.AddAttachment "c:\log\myfile.txt"
imsg.Send
End Sub
--
-Dick Christoph
"B" <no_spam@.no_spam.com> wrote in message
news:KsydnTnYtfFV2wbZnZ2dnUVZ_uqdnZ2d@.rcn.net...
> My code below works fine when run from my pc (changed all the values for
> obvious reasons). The code is placed inside a DTS task via VBS scripting.
> But when I try to run directly from the server where sqlserver is
> installed,
> the script fails.
> I have SMTP running, but there is no outlook installed.
> Can someone please advise what I am missing.
> Thanks
> Bob
>
> Set objEmail = CreateObject("CDO.Message")
> objEmail.From = "send@.test.com"
> objEmail.To = "receive@.test.com"
> objEmail.Subject = "TEST SUBJECT"
> objEmail.AddAttachment "\\server\test.csv"
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/sendusing") = 2
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/smtpserver") = "SERVER_NAME"
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/smtpauthenticate") = 1
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/sendusername") = "username"
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/sendpassword") = "userpwd"
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/smtpserverport") = 25
> objEmail.Configuration.Fields.Update
> objEmail.Send
> set objEmail = nothing|||Follow-up to my original post below.
Is it possible for the "objEmail.To" to lookup the values from a sqlserver
table?
At the moment, I type the email address separated by a semi-colon.
TIA~
"B" <no_spam@.no_spam.com> wrote in message
news:KsydnTnYtfFV2wbZnZ2dnUVZ_uqdnZ2d@.rcn.net...
> My code below works fine when run from my pc (changed all the values for
> obvious reasons). The code is placed inside a DTS task via VBS scripting.
> But when I try to run directly from the server where sqlserver is
installed,
> the script fails.
> I have SMTP running, but there is no outlook installed.
> Can someone please advise what I am missing.
> Thanks
> Bob
>
> Set objEmail = CreateObject("CDO.Message")
> objEmail.From = "send@.test.com"
> objEmail.To = "receive@.test.com"
> objEmail.Subject = "TEST SUBJECT"
> objEmail.AddAttachment "\\server\test.csv"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/sendusing") = 2
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/smtpserver") = "SERVER_NAME"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/smtpauthenticate") = 1
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/sendusername") = "username"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/sendpassword") = "userpwd"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/smtpserverport") = 25
> objEmail.Configuration.Fields.Update
> objEmail.Send
> set objEmail = nothing|||Hi B,
Well not directly but you could create an ADODB Command, Connection and
Recordset and use the command to return you a recordset from the Database
that would have 1 or many email addresses that you could concatenate
together and stick in the objEmail.To field.
--
-Dick Christoph
"B" <no_spam@.no_spam.com> wrote in message
news:c4mdnfssT45ImT7ZnZ2dnUVZ_r-dnZ2d@.rcn.net...
> Follow-up to my original post below.
> Is it possible for the "objEmail.To" to lookup the values from a sqlserver
> table?
> At the moment, I type the email address separated by a semi-colon.
> TIA~
>
> "B" <no_spam@.no_spam.com> wrote in message
> news:KsydnTnYtfFV2wbZnZ2dnUVZ_uqdnZ2d@.rcn.net...
>> My code below works fine when run from my pc (changed all the values for
>> obvious reasons). The code is placed inside a DTS task via VBS
>> scripting.
>> But when I try to run directly from the server where sqlserver is
> installed,
>> the script fails.
>>
>> I have SMTP running, but there is no outlook installed.
>>
>> Can someone please advise what I am missing.
>> Thanks
>> Bob
>>
>>
>> Set objEmail = CreateObject("CDO.Message")
>>
>> objEmail.From = "send@.test.com"
>> objEmail.To = "receive@.test.com"
>> objEmail.Subject = "TEST SUBJECT"
>> objEmail.AddAttachment "\\server\test.csv"
>>
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
>> ration/sendusing") = 2
>>
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
>> ration/smtpserver") = "SERVER_NAME"
>>
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
>> ration/smtpauthenticate") = 1
>>
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
>> ration/sendusername") = "username"
>>
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
>> ration/sendpassword") = "userpwd"
>>
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
>> ration/smtpserverport") = 25
>> objEmail.Configuration.Fields.Update
>> objEmail.Send
>>
>> set objEmail = nothing
>>
>>
Help retrieving values from 2 similar stored procs
I created one stored proc, then copied it to create another similar stored proc with just some filtering changes in the second. Now I want to obtain the results from both like this:
ProjFee ProjGross DailyRunRate Var1 InHouse1 InHouse2 GrossGoal Group Name PostedAmount
-
Row# 1 from current stored proc
Row # 2 from called stored proc
so something like this I should get in the end for example when both results are combined:
ProjFee ProjGross DailyRunRate Var1 InHouse1 InHouse2 GrossGoal Group Name PostedAmount
-
100000 33455 200 300 345555 4455555 5666666 Arizona 56000
103400 22455 900 700 777555 3333555 5444666 Illinois 660000
The first stored proc (CurrentMonthCollections_AZ) attempts to include the results of the second stored proc at the end (CurrentMonthCollections_IL) by calling it and inserting the fields into a temp table.
I was told by Angel to instead convert the second stored proc into a UDF...but having trouble with this.
What is the best approach to get the desired 2 rows back that I need in that fasion?
Here is the code for the 2 stored procs (very long so I will post as as links):
http:
http:
Look at the end of CurrentMonthCollections_AZ.txt to see where I'm stuck in trying to select the results (ProjFee ProjGross DailyRunRate Var1 InHouse1 InHouse2 GrossGoal Var1 PostedAmount
) from both stored procs.
I don't think UNION is what I want because it will combine stuff...I just want 2 separated rows
"I don't think UNION is what I want because it will combine stuff...I just want 2 separated rows"
Union is what you want:
select 1
union
select 2
returns
1
2
It seems to me that you could parameterize the procedures and get back the two rows that you want, or at least have one procedure and pass in the state you are interested in...that is a lot of proc to deal with though...
|||what about union all ?|||The difference between the 2 is that union removes duplicates and union all does not
union all is also faster because of that
select 1
union
select 2
union
select 2
select 1
union all
select 2
union all
select 2
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||Sorry, I should have been clearer:
UNION ALL doesn't eliminate duplicates, UNION does.
UNION ALL is probably the more correct way to go in this case, but it won't really make a difference with such a small set.
|||in this case, there are no dups so it doesn't matter I guess then.|||so I thought UNION puts it all in one row? I must be wrong...
Subscribe to:
Posts (Atom)