Help on Stored Procedures

I am learning to make a ASP web site and feel that if i can do it the harder way using some stored procedures instead of using multiple datasources on each page requiring that it might be better.

So i am wondering what are these used for:

DECLARE vs just entering "@.param1 varchar(30)"When i use "DECLARE @.rc int" i get the error "Incorrect syntax near DECLARE"
How to return values to ASP page in Visual Studio 2005
How to use @.@.rowcount - doesn't seem to work for me?i tried using
DECLARE @.rc int
SET @.rc = @.@.rowcountWhen to use GO, BEGIN etcIf i want to use the variable only in the procedure, and not needed to be inputed, do i need to put it in the CREATE PROCEDURE (section)?Should i use my own stored procedures or VS2005 created ones using datasources? not really procedures but SQL, in SQL can i do like IF ELSE? if i use my own i cant use the Optimistic Concurrency right? and whats that?

You need to read some basic tutorials on stored procedures.

http://www.awprofessional.com/articles/article.asp?p=25288&rl=1

http://www.functionx.com/sqlserver/Lesson16.htm

http://www.quackit.com/sql_server/tutorial/sql_server_stored_procedures.cfm

Also, Tatworth gave you some good links when you asked about this a few days ago.http://forums.asp.net/p/1118089/1757710.aspx#1757710

Help on multiple date range on sql statement

Using SQLServer ver 7.0, two tables:
TableA = contains all inventory data
TableB = contains four fields: ID, source, date_from, date_to
This is where multiple range of dates are populated.
Sample 1:
1,'A','9/1/2004','9/30/2004'

Sample 2:
2,'A','1/1/2003','3/31/2003'
3,'A','10/1/2004','10/31/2004'

Data populated on TableB varies.

Sample SQL for Sample 1:
SELECT *
FROM TableA
WHERE inventory_date BETWEEN (select DATE_FROM from TableB) AND (select
DATE_TO from TableB)

Problem: How to approach sql statement based on Sample 2 above?B (no_spam@.no_spam.com) writes:
> Using SQLServer ver 7.0, two tables:
> TableA = contains all inventory data
> TableB = contains four fields: ID, source, date_from, date_to
> This is where multiple range of dates are populated.
> Sample 1:
> 1,'A','9/1/2004','9/30/2004'
> Sample 2:
> 2,'A','1/1/2003','3/31/2003'
> 3,'A','10/1/2004','10/31/2004'
> Data populated on TableB varies.
>
> Sample SQL for Sample 1:
> SELECT *
> FROM TableA
> WHERE inventory_date BETWEEN (select DATE_FROM from TableB) AND (select
> DATE_TO from TableB)

SELECT *
FROM TableA A
JOIN TableB B ON B.ID = A.ID
WHERE A.inventory_date BETWEEN B.date_from ABD B.date_to

But this is really a guess. If this does not answer your question, please
post:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The expected result given the sample data.

That makes it possible to post a tested solution.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Mon, 8 Nov 2004 22:40:44 -0500, B wrote:

>Using SQLServer ver 7.0, two tables:
>TableA = contains all inventory data
>TableB = contains four fields: ID, source, date_from, date_to
>This is where multiple range of dates are populated.
>Sample 1:
>1,'A','9/1/2004','9/30/2004'
>Sample 2:
>2,'A','1/1/2003','3/31/2003'
>3,'A','10/1/2004','10/31/2004'
>Data populated on TableB varies.
>
>Sample SQL for Sample 1:
>SELECT *
>FROM TableA
>WHERE inventory_date BETWEEN (select DATE_FROM from TableB) AND (select
>DATE_TO from TableB)
>Problem: How to approach sql statement based on Sample 2 above?

Hi B,

If you want it to return all inventory details with an inventory_date
between 1/1/2003 and 3/31/2003 or with an inventory date between 10/1/2004
and 10/31/2004, try this query:

SELECT A.Column1, A.Column2, ..., A.ColumnN
FROM TableA AS A
INNER JOIN TableB AS B
ON A.inventory_date BETWEEN B.DATE_FROM and B.DATE_TO

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||This is exactly solution I needed.

Many thanks for your time!
Bob

> If you want it to return all inventory details with an inventory_date
> between 1/1/2003 and 3/31/2003 or with an inventory date between 10/1/2004
> and 10/31/2004, try this query:
> SELECT A.Column1, A.Column2, ..., A.ColumnN
> FROM TableA AS A
> INNER JOIN TableB AS B
> ON A.inventory_date BETWEEN B.DATE_FROM and B.DATE_TO
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

help on multiple column updates - backtracking data for 30 days

i need to back track data should there be changes for the last 30 days.
help!
thank you!misheL wrote:

> i need to back track data should there be changes for the last 30
> days.
Are you seriously expecting many posters to respond and take the time
to answer your question considering the amount of time you have put
into formulating your question?
Kind regards,
Stijn Verrept.|||On Tue, 13 Dec 2005 17:30:02 -0800, misheL wrote:

>i need to back track data should there be changes for the last 30 days.
>help!
>thank you!
Hi misheL,
Please provide better specifications. See www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Help on complex query

I have a table of items, with multiple records for each item, representing different prices for different periods, and a table which defines supplier rebates for different periods (may be different than item periods)
For example:
ITEMS: itemno, supplier_id, price, valid_from, valid_to:
1, 1, 1000, '01-01-2001'. '12-31-2001'
1, 1, 1100, '01-01-2002'. '12-31-2002'
1, 1, 1200, '01-01-2003'. '12-31-2003'
SUPPLIER_REBATES:
supplier_id, rebate, rebate_valid_to:
1, 10, '10-31-2002' (10% until 10-31-2002)
1, 12, '12-31-2010' (12% until 12-31-2010)
I need a query to list all items, with the corresponding rebate, according to valid_from date, e.g.:
RESULT: itemno, price, valid_from, valid_to, rebate
1, 1000, '01-01-2001'. '12-31-2001', 10
1, 1100, '01-01-2002'. '12-31-2002', 10
1, 1200, '01-01-2003'. '12-31-2003', 12
How do I do that?
Thanks in advance for your help
It helps if you include DDL and sample data INSERTs with your posts so that
we don't have to guess your keys, constraints and datatypes:
CREATE TABLE Items (itemno INTEGER NOT NULL, supplier_id INTEGER NOT NULL,
price INTEGER NOT NULL, valid_from DATETIME NOT NULL, valid_to DATETIME NOT
NULL, CHECK (valid_from <= valid_to) /* PRIMARY KEY ? */)
CREATE TABLE Rebates (supplier_id INTEGER NOT NULL, rebate INTEGER NOT NULL,
rebate_valid_to DATETIME, PRIMARY KEY (supplier_id, rebate_valid_to) /* ?
*/)
INSERT INTO Items VALUES (1, 1, 1000, '20001011', '20011231')
INSERT INTO Items VALUES (1, 1, 1100, '20020101', '20021231')
INSERT INTO Items VALUES (1, 1, 1200, '20030101', '20031231')
INSERT INTO Rebates VALUES (1, 10, '20021031')
INSERT INTO Rebates VALUES (1, 12, '20101231')
Why don't you have a Valid_From date in your Rebates table? You can
calculate it like this:
SELECT R1.supplier_id,
COALESCE(MAX(R2.rebate_valid_to),'17530101') AS rebate_valid_after,
R1.rebate_valid_to, R1.rebate
FROM Rebates AS R1
LEFT JOIN Rebates AS R2
ON R1.supplier_id = R2.supplier_id
AND R1.rebate_valid_to > R2.rebate_valid_to
GROUP BY R1.supplier_id, R1.rebate_valid_to, R1.rebate
You haven't specified how you determine the rebate when more than one rebate
can apply to a particular valid period for an item. Your required result has
only one row for the period
2002-01-01 to 2002-12-31 yet it appears that *both* rebates were valid
during that period. How do you want to arrive at the result you specified?
The following returns *all* the valid rebates for each Item row.
SELECT I.itemno, I.price, I.valid_from, I.valid_to, R.rebate
FROM Items AS I
JOIN
(SELECT R1.supplier_id,
COALESCE(MAX(R2.rebate_valid_to),'17530101') AS rebate_valid_after,
R1.rebate_valid_to, R1.rebate
FROM Rebates AS R1
LEFT JOIN Rebates AS R2
ON R1.supplier_id = R2.supplier_id
AND R1.rebate_valid_to > R2.rebate_valid_to
GROUP BY R1.supplier_id, R1.rebate_valid_to, R1.rebate) AS R
ON I.valid_from < R.rebate_valid_to AND I.valid_to > R.rebate_valid_after
ORDER BY valid_from
David Portas
SQL Server MVP

Help on complex query

I have a table of items, with multiple records for each item, representing d
ifferent prices for different periods, and a table which defines supplier re
bates for different periods (may be different than item periods)
For example:
ITEMS: itemno, supplier_id, price, valid_from, valid_to:
1, 1, 1000, '01-01-2001'. '12-31-2001'
1, 1, 1100, '01-01-2002'. '12-31-2002'
1, 1, 1200, '01-01-2003'. '12-31-2003'
SUPPLIER_REBATES:
supplier_id, rebate, rebate_valid_to:
1, 10, '10-31-2002' (10% until 10-31-2002)
1, 12, '12-31-2010' (12% until 12-31-2010)
I need a query to list all items, with the corresponding rebate, according t
o valid_from date, e.g.:
RESULT: itemno, price, valid_from, valid_to, rebate
1, 1000, '01-01-2001'. '12-31-2001', 10
1, 1100, '01-01-2002'. '12-31-2002', 10
1, 1200, '01-01-2003'. '12-31-2003', 12
How do I do that?
Thanks in advance for your helpIt helps if you include DDL and sample data INSERTs with your posts so that
we don't have to guess your keys, constraints and datatypes:
CREATE TABLE Items (itemno INTEGER NOT NULL, supplier_id INTEGER NOT NULL,
price INTEGER NOT NULL, valid_from DATETIME NOT NULL, valid_to DATETIME NOT
NULL, CHECK (valid_from <= valid_to) /* PRIMARY KEY ? */)
CREATE TABLE Rebates (supplier_id INTEGER NOT NULL, rebate INTEGER NOT NULL,
rebate_valid_to DATETIME, PRIMARY KEY (supplier_id, rebate_valid_to) /* ?
*/)
INSERT INTO Items VALUES (1, 1, 1000, '20001011', '20011231')
INSERT INTO Items VALUES (1, 1, 1100, '20020101', '20021231')
INSERT INTO Items VALUES (1, 1, 1200, '20030101', '20031231')
INSERT INTO Rebates VALUES (1, 10, '20021031')
INSERT INTO Rebates VALUES (1, 12, '20101231')
Why don't you have a Valid_From date in your Rebates table? You can
calculate it like this:
SELECT R1.supplier_id,
COALESCE(MAX(R2.rebate_valid_to),'17530101') AS rebate_valid_after,
R1.rebate_valid_to, R1.rebate
FROM Rebates AS R1
LEFT JOIN Rebates AS R2
ON R1.supplier_id = R2.supplier_id
AND R1.rebate_valid_to > R2.rebate_valid_to
GROUP BY R1.supplier_id, R1.rebate_valid_to, R1.rebate
You haven't specified how you determine the rebate when more than one rebate
can apply to a particular valid period for an item. Your required result has
only one row for the period
2002-01-01 to 2002-12-31 yet it appears that *both* rebates were valid
during that period. How do you want to arrive at the result you specified?
The following returns *all* the valid rebates for each Item row.
SELECT I.itemno, I.price, I.valid_from, I.valid_to, R.rebate
FROM Items AS I
JOIN
(SELECT R1.supplier_id,
COALESCE(MAX(R2.rebate_valid_to),'17530101') AS rebate_valid_after,
R1.rebate_valid_to, R1.rebate
FROM Rebates AS R1
LEFT JOIN Rebates AS R2
ON R1.supplier_id = R2.supplier_id
AND R1.rebate_valid_to > R2.rebate_valid_to
GROUP BY R1.supplier_id, R1.rebate_valid_to, R1.rebate) AS R
ON I.valid_from < R.rebate_valid_to AND I.valid_to > R.rebate_valid_after
ORDER BY valid_from
David Portas
SQL Server MVP
--

Help on complex query

I have a table of items, with multiple records for each item, representing different prices for different periods, and a table which defines supplier rebates for different periods (may be different than item periods
For example
ITEMS: itemno, supplier_id, price, valid_from, valid_to
1, 1, 1000, '01-01-2001'. '12-31-2001
1, 1, 1100, '01-01-2002'. '12-31-2002
1, 1, 1200, '01-01-2003'. '12-31-2003
SUPPLIER_REBATES
supplier_id, rebate, rebate_valid_to
1, 10, '10-31-2002' (10% until 10-31-2002
1, 12, '12-31-2010' (12% until 12-31-2010
I need a query to list all items, with the corresponding rebate, according to valid_from date, e.g.
RESULT: itemno, price, valid_from, valid_to, rebat
1, 1000, '01-01-2001'. '12-31-2001', 1
1, 1100, '01-01-2002'. '12-31-2002', 1
1, 1200, '01-01-2003'. '12-31-2003', 1
How do I do that
Thanks in advance for your helIt helps if you include DDL and sample data INSERTs with your posts so that
we don't have to guess your keys, constraints and datatypes:
CREATE TABLE Items (itemno INTEGER NOT NULL, supplier_id INTEGER NOT NULL,
price INTEGER NOT NULL, valid_from DATETIME NOT NULL, valid_to DATETIME NOT
NULL, CHECK (valid_from <= valid_to) /* PRIMARY KEY ? */)
CREATE TABLE Rebates (supplier_id INTEGER NOT NULL, rebate INTEGER NOT NULL,
rebate_valid_to DATETIME, PRIMARY KEY (supplier_id, rebate_valid_to) /* ?
*/)
INSERT INTO Items VALUES (1, 1, 1000, '20001011', '20011231')
INSERT INTO Items VALUES (1, 1, 1100, '20020101', '20021231')
INSERT INTO Items VALUES (1, 1, 1200, '20030101', '20031231')
INSERT INTO Rebates VALUES (1, 10, '20021031')
INSERT INTO Rebates VALUES (1, 12, '20101231')
Why don't you have a Valid_From date in your Rebates table? You can
calculate it like this:
SELECT R1.supplier_id,
COALESCE(MAX(R2.rebate_valid_to),'17530101') AS rebate_valid_after,
R1.rebate_valid_to, R1.rebate
FROM Rebates AS R1
LEFT JOIN Rebates AS R2
ON R1.supplier_id = R2.supplier_id
AND R1.rebate_valid_to > R2.rebate_valid_to
GROUP BY R1.supplier_id, R1.rebate_valid_to, R1.rebate
You haven't specified how you determine the rebate when more than one rebate
can apply to a particular valid period for an item. Your required result has
only one row for the period
2002-01-01 to 2002-12-31 yet it appears that *both* rebates were valid
during that period. How do you want to arrive at the result you specified?
The following returns *all* the valid rebates for each Item row.
SELECT I.itemno, I.price, I.valid_from, I.valid_to, R.rebate
FROM Items AS I
JOIN
(SELECT R1.supplier_id,
COALESCE(MAX(R2.rebate_valid_to),'17530101') AS rebate_valid_after,
R1.rebate_valid_to, R1.rebate
FROM Rebates AS R1
LEFT JOIN Rebates AS R2
ON R1.supplier_id = R2.supplier_id
AND R1.rebate_valid_to > R2.rebate_valid_to
GROUP BY R1.supplier_id, R1.rebate_valid_to, R1.rebate) AS R
ON I.valid_from < R.rebate_valid_to AND I.valid_to > R.rebate_valid_after
ORDER BY valid_from
--
David Portas
SQL Server MVP
--sql

Help needed with Xquery

Hello,

I'm trying to retreive the values from multiple nodes based on the value of another , without any success. The XML source is stored in an SQL(2005) xml column .

'Sample XML

<!--Combat Flight Sim mission-->

<Mission>

<Params Version="3.0" Directive="nothing" Country="Britain" Aircraft="p_51b" Airbase="brod23" Date="8/10/1940" Time="12:00" Weather="scatteredclouds3.xml" Multiplayer="y" MultiplayerOnly="n" />

.......

<AirFormation ID="6003" Directive="nothing" Country="Britain" Skill="1" FormType="diamond">

<Unit ID="9459" Type="p_51b" IsPlayer="y" Skill="1" />

<Unit ID="9460" Type="p_51b" Skill="2" />

.........

<AirFormation ID="6000" Directive="nothing" Country="Britain" Points="2" DamagePercent="40" Skill="2" Payload="2" FormType="box">

<Unit ID="9467" Type="b_25c" Skill="2" Payload="3" />

<Unit ID="9468" Type="b_25c" Skill="2" Payload="3" />

.........

AirFormation ID="6007" Directive="nothing" Country="Germany" Skill="2" FormType="fingertip">

<Unit ID="9475" Type="bf_109g_6" Skill="2" Payload="6" />

<Unit ID="9476" Type="bf_109g_6" Skill="2"

'This is the SQL code:

SELECT DISTINCT nref.value('@.Type', 'varchar(100)') Aircraft

FROM dbo.MOG_Missions CROSS APPLY xmlData.nodes('//AirFormation/Unit') as T(nref)

WHERE id = @.id 'some additional condition here is needed but I cannot figure it out

Which returns the following values from the ?Type attribute :

b_25c
bf_109g_6
p_51b

What I would like to accomplish is to return only the values from ?Type where the AirFormation-Country attribute matches the ?Country attribute of the ?Params node.

Thank you in advance.

Your XML sample is not clear to me. What is the relationship between the Params element and the AirFormation elements? If that is known then you should simply be able to express the condition in an XPath predicate in your nodes call. For example if the Params element is a sibling of the AirFormation elements then you can check e.g.

Code Snippet

SELECT DISTINCT t.u.value('@.Type', 'nvarchar(10)') AS Type

FROM example1

CROSS APPLY xml.nodes('//AirFormation[@.Country = ../Params/@.Country]/Unit') AS t(u)

WHERE id = 3;

|||I should have asked for help sooner! Thank you so much!