Restore Database: procedure

Showing posts with label procedure. Show all posts

Tuesday, March 27, 2012

Help with combining two queries

I have a stored procedure that needs to retrieve the top 1000 sent items and
the top 1000 received items - each ordered by date. So, effectively, the
most recent 1000 sent items and the most recent 1000 received items. Then, I
need to combine them into one result set and again take the top 1000 items
when ordered by date.
Originally, the stored procedure created a temp table and inserted the
results of each query consecutively, then did a SELECT TOP to get the final
results. Because of high traffic, this is killing the DB server. I suspect
that the queries could be combined into one query. I also tried a UNION of
the two selects, but that doesn't work because I need to do have accurate
results on the subquery first (top 1000 ordered by date). Can anyone help me
determine a more effecient solution, preferrably to combine the two queries
into one? I have slimmed down the two queries significantly to show only the
differences. They are below.
DECLARE @.userName AS VARCHAR(25)
SELECT @.userName = 'MyUserName'
-- Gets the sent items
--
SELECT TOP 1000
@.userName as SenderName,
'Sent' AS SentReceived,
u.[user_name] as ReceiverName
FROM dbo.Email_Type (nolock)
INNER JOIN dbo.Email (nolock) ON dbo.Email_Type.ID = dbo.Email.Type
INNER JOIN dbo.Email_Folders (nolock) ON dbo.Email.Folder =
dbo.Email_Folders.ID
RIGHT OUTER JOIN [Profile].dbo.Contact_history ch (nolock) ON dbo.Email.ID =
ch.source_id_guid
INNER JOIN [Profile].dbo.user_profile u (nolock) ON ch.[contact_user_id] =
u.[user_id]
WHERE
ch.[user_id] = @.UserID
ORDER BY
ch.createstamp DESC
-- Gets the received items
--
SELECT TOP 1000
u.[user_name] as SenderName,
'Received' AS SentReceived,
@.userName as ReceiverName
FROM dbo.Email_Type (nolock)
INNER JOIN dbo.Email (nolock) ON dbo.Email_Type.ID = dbo.Email.Type
INNER JOIN dbo.Email_Folders (nolock) ON dbo.Email.Folder =
dbo.Email_Folders.ID
RIGHT OUTER JOIN [Profile].dbo.Contact_history ch (nolock) ON dbo.Email.ID =
ch.source_id_guid
INNER JOIN [Profile].dbo.user_profile u (nolock) ON ch.[user_id] =
u.[user_id]
WHERE
ch.[contact_user_id] = @.UserID
ORDER BY
ch.createstamp DESCHello, karch
You probably want a query like this:
SELECT TOP 1000 *
FROM (
SELECT TOP 1000 <your columns>
FROM <sent items>
ORDER BY TheDate
UNION ALL
SELECT TOP 1000 <your columns>
FROM <sent items>
ORDER BY TheDate
) x
ORDER BY TheDate
However, I think the above query will give the same results as this
query (as long as TheDate is unique among all rows):
SELECT TOP 1000 *
FROM (
SELECT <your columns>
FROM <sent items>
UNION ALL
SELECT <your columns>
FROM <sent items>
) x
ORDER BY TheDate
Razvan|||Yes, but I dont think you can have ORDER BY in the subqueries if you UNION -
I tried this approach and received an error. But, yes, logically that is
what I want to accomplish.
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1146597584.838889.18770@.v46g2000cwv.googlegroups.com...
> Hello, karch
> You probably want a query like this:
> SELECT TOP 1000 *
> FROM (
> SELECT TOP 1000 <your columns>
> FROM <sent items>
> ORDER BY TheDate
> UNION ALL
> SELECT TOP 1000 <your columns>
> FROM <sent items>
> ORDER BY TheDate
> ) x
> ORDER BY TheDate
> However, I think the above query will give the same results as this
> query (as long as TheDate is unique among all rows):
> SELECT TOP 1000 *
> FROM (
> SELECT <your columns>
> FROM <sent items>
> UNION ALL
> SELECT <your columns>
> FROM <sent items>
> ) x
> ORDER BY TheDate
> Razvan
>|||In this case, try another level of subqueries:
SELECT TOP 1000 *
FROM (
SELECT * FROM (
SELECT TOP 1000 <your columns>
FROM <sent items>
ORDER BY TheDate
) a
UNION ALL
SELECT * FROM (
SELECT TOP 1000 <your columns>
FROM <sent items>
ORDER BY TheDate
) b
) x
ORDER BY TheDate
Razvan

Help with CASE in Stored Procedure

hi guys! Help please..I'm new to sotred procedure. Currently, i have a stored procedure (see Below) but i can't figure out on how to solve the errors(see below). What im trying to do with my stored procedure is to take two(2) parameters, the string to be search(@.Search_Arg) and the search by(@.Search_by) Either by Login name or by firstname. Under my CASE statement, When the @.Search_by is equal to Username i want to select the user with User_login equals to @.Search_Arg and when the @.Search_by is equal to Firstname it will select all user with User_FirstName equals to @.Search_Arg...Any help please..Thanks in advance!

CREATE PROCEDURE sp_User_Search
@.Search_Arg varchar(50),
@.Search_By varchar(20)
AS
BEGIN

CASE @.Search_By
WHEN 'Username' THEN SELECT * FROM RCPS_UserAccount WHERE User_login = @.Search_Arg
WHEN 'Firstname' THEN SELECT * FROM RCPS_UserAccount WHERE User_FirstName = @.Search_Arg
END
END

Msg 156, Level 15, State 1, Procedure sp_User_Search, Line 7
Incorrect syntax near the keyword 'CASE'.
Msg 156, Level 15, State 1, Procedure sp_User_Search, Line 9
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Procedure sp_User_Search, Line 10
Incorrect syntax near the keyword 'END'.what you have there is a dynamic search condition. read this for various techniques:

http://www.sommarskog.se/dyn-search.html

in particular, this might suit you:

select col1, col2, col3 from RCPS_UserAccount
where
(User_login = @.user_login or @.user_login is null) and
(User_FirstName = @.user_firstname or @.user_firstname is null)|||Case can return only one value at a time
use of 'Select *' is not allowed in case statement. What you can do is :

CREATE PROCEDURE sp_User_Search
@.Search_Arg varchar(50),
@.Search_By varchar(20)
AS
BEGIN
SELECT * FROM RCPS_UserAccount
WHERE
CASE
When @.Search_By = 'Username' then
User_login
When @.Search_By = 'Firstname' then
User_FirstName
End
= @.Search_Arg
END
END

...but I think way suggested by jezemine is better than this one.sql

Help with CASE in Stored Procedure

Hi there,
I have a table with the following columns:
wgt_id
wgt_lower_weight
wgt_higher_weight
wgt_country
wgt_Parcel_Price
wgt_RMSD_Price
wgt_RMSD_Pre_9_Price
wgt_RMSD_Pre_1_Price
wgt_RMSD_Sat_Price
wgt_Citylink_Price
wgt_Citylink_Sat_Price
I want to create a stored procedure that is passed:
weight
country
shippingtype
This is what I have so far:
SELECT *
FROM dbo.tblShippingRates
WHERE (wgt_country = @.Country) AND (wgt_weight_lower < @.Weight)
AND (wgt_weight_higher > @.Weight)
I need to add the parameter shippingtype. This will select the
matching column and output the price. I think it needs the use of CASE
but I can't figure it out. Can anyone help me create my store
procedure?This is what I have tried but its not returning the Price, its
returning all the rows but blank.
CREATE PROCEDURE dbo.sp_GetShippingCharge(@.Country varchar(2),
@.Weight decimal(12,2), @.Shipping varchar(10))
AS
SELECT CASE WHEN @.Shipping = 'Parcel' THEN wgt_Parcel_Price
WHEN @.Shipping = 'RMSD9' THEN wgt_RMSD_Pre9_Price
WHEN @.Shipping = 'RMSD1' THEN wgt_RMSD_Pre1_Price
WHEN @.Shipping = 'RMSDSat' THEN wgt_RMSD_Sat_Price
WHEN @.Shipping = 'Citylink' THEN wgt_Citylink_Price
WHEN @.Shipping = 'CitylinkSat' THEN wgt_Citylink_Sat_Price END AS
Price
FROM dbo.tblShippingRates
WHERE (wgt_country = @.Country) AND (wgt_weight_lower <= @.Weight)
AND (wgt_weight_higher >= @.Weight)
GO
Any ideas where I am going wrong?|||(a) never use sp_ prefix on stored procedures.
(b) provide DDL for the tblShippingRates table (another questionable prefix,
btw), some sample data, and desired results. I have no idea what data is in
the table, what parameter values you are passing in, what should be returned
by the query, and what "all the rows but blank" means...
Aaron Bertrand
SQL Server MVP
"Dooza" <doozadooza@.gmail.com> wrote in message
news:1186057919.366437.11420@.d55g2000hsg.googlegroups.com...
> This is what I have tried but its not returning the Price, its
> returning all the rows but blank.
> CREATE PROCEDURE dbo.sp_GetShippingCharge(@.Country varchar(2),
> @.Weight decimal(12,2), @.Shipping varchar(10))
> AS
> SELECT CASE WHEN @.Shipping = 'Parcel' THEN wgt_Parcel_Price
> WHEN @.Shipping = 'RMSD9' THEN wgt_RMSD_Pre9_Price
> WHEN @.Shipping = 'RMSD1' THEN wgt_RMSD_Pre1_Price
> WHEN @.Shipping = 'RMSDSat' THEN wgt_RMSD_Sat_Price
> WHEN @.Shipping = 'Citylink' THEN wgt_Citylink_Price
> WHEN @.Shipping = 'CitylinkSat' THEN wgt_Citylink_Sat_Price END AS
> Price
> FROM dbo.tblShippingRates
> WHERE (wgt_country = @.Country) AND (wgt_weight_lower <= @.Weight)
> AND (wgt_weight_higher >= @.Weight)
> GO
> Any ideas where I am going wrong?
>|||Hi Aaron,
Firstly thank you for helping! I am a self taught asp developer, so
wasn't aware of the naming conventions, I will change them straight
away.
wgt_weight_lower/wgt_weight_higher/wgt_country/wgt_Parcel_Price/
wgt_RMSD_Pre9_Price/wgt_RMSD_Pre1_Price/wgt_RMSD_Sat_Price/
wgt_Citylink_Price wgt_Citylink_Sat_Pric
e
0 0.5 uk =A34.90 =A313.00 =A35.60 =A38.10 =A310.40 =A325.40
0=2E51 1 uk =A36.20 =A315.00 =A37.00 =A39.50 =A310.40 =A325.40
1=2E01 2 uk =A36.70 =A318.70 =A39.20 =A311.70 =A310.40 =A325.40
2=2E01 4 uk =A39.70 =A323.00 =A310.40 =A325.40
4=2E01 6 uk =A323.00 =A310.40 =A325.40
6=2E01 8 uk =A323.00 =A310.40 =A325.40
8=2E01 10 uk =A323.00 =A310.40 =A325.40
10.01 15 uk =A312.90 =A327.90
15.01 20 uk =A315.40 =A330.40
I am passing the stored procedure country =3D uk weight =3D 2 and shipping
=3D RMSD9
I am expecting Price to be returned as 6.70
The results that I am getting back at the moment are all the column
names from the table with no data in them at all. It's not like an
empty recordset, this is a row with nothing in it. I am expecting just
the one column, well, the alias called Price.
Cheers,
Steve|||I worked it out!
CREATE PROCEDURE dbo.sp_GetShippingCharge(@.Country varchar(2),
@.Weight decimal(12,2), @.Shipping varchar(12))
AS
SELECT ShippingPrice = CASE @.Shipping WHEN 'Parcel' THEN
dbo.tblShippingRates.wgt_Parcel_Price
WHEN 'RMSD9' THEN dbo.tblShippingRates.wgt_RMSD_Pre9_Price
WHEN 'RMSD1' THEN dbo.tblShippingRates.wgt_RMSD_Pre1_Price
WHEN 'RMSDSat' THEN dbo.tblShippingRates.wgt_RMSD_Sat_Price
WHEN 'Citylink' THEN dbo.tblShippingRates.wgt_Citylink_Price
WHEN 'CitylinkSat' THEN dbo.tblShippingRates.wgt_Citylink_Sat_Price
END
FROM dbo.tblShippingRates
WHERE (wgt_country = @.Country) AND (wgt_weight_lower <= @.Weight)
AND (wgt_weight_higher >= @.Weight)
GO|||It seems that the selection criteria is based on weight and country. I
don't understand the RMSD9 bit. In your case, the query would be something
like:
select
wgt_Parcel_Price
from
MyTable
where
wgt_country = @.country
and
@.weight between wgt_weight_lower and wgt_weight_higher
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Dooza" <doozadooza@.gmail.com> wrote in message
news:1186060353.152602.170690@.q75g2000hsh.googlegroups.com...
Hi Aaron,
Firstly thank you for helping! I am a self taught asp developer, so
wasn't aware of the naming conventions, I will change them straight
away.
wgt_weight_lower/wgt_weight_higher/wgt_country/wgt_Parcel_Price/
wgt_RMSD_Pre9_Price/wgt_RMSD_Pre1_Price/wgt_RMSD_Sat_Price/
wgt_Citylink_Price wgt_Citylink_Sat_Price
0 0.5 uk 4.90 13.00 5.60 8.10 10.40 25.40
0.51 1 uk 6.20 15.00 7.00 9.50 10.40 25.40
1.01 2 uk 6.70 18.70 9.20 11.70 10.40 25.40
2.01 4 uk 9.70 23.00 10.40 25.40
4.01 6 uk 23.00 10.40 25.40
6.01 8 uk 23.00 10.40 25.40
8.01 10 uk 23.00 10.40 25.40
10.01 15 uk 12.90 27.90
15.01 20 uk 15.40 30.40
I am passing the stored procedure country = uk weight = 2 and shipping
= RMSD9
I am expecting Price to be returned as 6.70
The results that I am getting back at the moment are all the column
names from the table with no data in them at all. It's not like an
empty recordset, this is a row with nothing in it. I am expecting just
the one column, well, the alias called Price.
Cheers,
Steve|||I think they are zones and/or delivery speeds (e.g. 1 day, 2 day, 9 day,
etc.). So the values in the different columns in the actual table are
actually relevant... the shipping price is not always wgt_Parcel_Price.
Aaron Bertrand
SQL Server MVP
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eH8U5iQ1HHA.464@.TK2MSFTNGP02.phx.gbl...
> It seems that the selection criteria is based on weight and country. I
> don't understand the RMSD9 bit. In your case, the query would be
> something
> like:
> select
> wgt_Parcel_Price
> from
> MyTable
> where
> wgt_country = @.country
> and
> @.weight between wgt_weight_lower and wgt_weight_higher
> --
> Tom|||On Aug 2, 2:37 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> I think they are zones and/or delivery speeds (e.g. 1 day, 2 day, 9 day,
> etc.). So the values in the different columns in the actual table are
> actually relevant... the shipping price is not always wgt_Parcel_Price.
Each column that ends in Price is a different courier option.|||Oh, OK. So you'd have a CASE:
CASE @.shipping
WHEN 'RMSD9' then wgt_RMSD_Pre9_Price
WHEN 'RMSD1' then wgt_RMSD_Pre1_Price
..
END
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Dooza" <doozadooza@.gmail.com> wrote in message
news:1186062055.667781.242240@.r34g2000hsd.googlegroups.com...
On Aug 2, 2:37 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> I think they are zones and/or delivery speeds (e.g. 1 day, 2 day, 9 day,
> etc.). So the values in the different columns in the actual table are
> actually relevant... the shipping price is not always wgt_Parcel_Price.
Each column that ends in Price is a different courier option.|||>I worked it out!
Your procedure suddenly started returning data because you changed AS
ShippingPrice to ShippingPrice = and CASE WHEN @.Shipping = to CASE @.Shipping
WHEN ? That doesn't seem right. Anyway, how about readability? Do you
need to repeat dbo.tblShippingRates 18 times?
CREATE PROCEDURE dbo.usp_GetShippingCharge
@.Country VARCHAR(2),
@.Weight DECIMAL(12,2),
@.Shipping VARCHAR(12)
AS
BEGIN
SET NOCOUNT ON;
SELECT
ShippingPrice = CASE @.Shipping
WHEN 'Parcel' THEN wgt_Parcel_Price
WHEN 'RMSD9' THEN wgt_RMSD_Pre9_Price
WHEN 'RMSD1' THEN wgt_RMSD_Pre1_Price
WHEN 'RMSDSat' THEN wgt_RMSD_Sat_Price
WHEN 'Citylink' THEN wgt_Citylink_Price
WHEN 'CitylinkSat' THEN wgt_Citylink_Sat_Price
END
FROM
dbo.tblShippingRates
WHERE
wgt_country = @.Country
AND (@.Weight BETWEEN wgt_weight_lower AND wgt_weight_higher);
END
GO
Finally, since this will only ever return one column and *should* only be
returning one row, why not make it a scalar function, or at least capture
the data via an output parameter?
Aaron Bertrand
SQL Server MVP

Help with CASE in Stored Procedure

Hi there,
I have a table with the following columns:
wgt_id
wgt_lower_weight
wgt_higher_weight
wgt_country
wgt_Parcel_Price
wgt_RMSD_Price
wgt_RMSD_Pre_9_Price
wgt_RMSD_Pre_1_Price
wgt_RMSD_Sat_Price
wgt_Citylink_Price
wgt_Citylink_Sat_Price
I want to create a stored procedure that is passed:
weight
country
shippingtype
This is what I have so far:
SELECT *
FROM dbo.tblShippingRates
WHERE (wgt_country = @.Country) AND (wgt_weight_lower < @.Weight)
AND (wgt_weight_higher > @.Weight)
I need to add the parameter shippingtype. This will select the
matching column and output the price. I think it needs the use of CASE
but I can't figure it out. Can anyone help me create my store
procedure?This is what I have tried but its not returning the Price, its
returning all the rows but blank.
CREATE PROCEDURE dbo.sp_GetShippingCharge(@.Country varchar(2),
@.Weight decimal(12,2), @.Shipping varchar(10))
AS
SELECT CASE WHEN @.Shipping = 'Parcel' THEN wgt_Parcel_Price
WHEN @.Shipping = 'RMSD9' THEN wgt_RMSD_Pre9_Price
WHEN @.Shipping = 'RMSD1' THEN wgt_RMSD_Pre1_Price
WHEN @.Shipping = 'RMSDSat' THEN wgt_RMSD_Sat_Price
WHEN @.Shipping = 'Citylink' THEN wgt_Citylink_Price
WHEN @.Shipping = 'CitylinkSat' THEN wgt_Citylink_Sat_Price END AS
Price
FROM dbo.tblShippingRates
WHERE (wgt_country = @.Country) AND (wgt_weight_lower <= @.Weight)
AND (wgt_weight_higher >= @.Weight)
GO
Any ideas where I am going wrong?|||(a) never use sp_ prefix on stored procedures.
(b) provide DDL for the tblShippingRates table (another questionable prefix,
btw), some sample data, and desired results. I have no idea what data is in
the table, what parameter values you are passing in, what should be returned
by the query, and what "all the rows but blank" means...
--
Aaron Bertrand
SQL Server MVP
"Dooza" <doozadooza@.gmail.com> wrote in message
news:1186057919.366437.11420@.d55g2000hsg.googlegroups.com...
> This is what I have tried but its not returning the Price, its
> returning all the rows but blank.
> CREATE PROCEDURE dbo.sp_GetShippingCharge(@.Country varchar(2),
> @.Weight decimal(12,2), @.Shipping varchar(10))
> AS
> SELECT CASE WHEN @.Shipping = 'Parcel' THEN wgt_Parcel_Price
> WHEN @.Shipping = 'RMSD9' THEN wgt_RMSD_Pre9_Price
> WHEN @.Shipping = 'RMSD1' THEN wgt_RMSD_Pre1_Price
> WHEN @.Shipping = 'RMSDSat' THEN wgt_RMSD_Sat_Price
> WHEN @.Shipping = 'Citylink' THEN wgt_Citylink_Price
> WHEN @.Shipping = 'CitylinkSat' THEN wgt_Citylink_Sat_Price END AS
> Price
> FROM dbo.tblShippingRates
> WHERE (wgt_country = @.Country) AND (wgt_weight_lower <= @.Weight)
> AND (wgt_weight_higher >= @.Weight)
> GO
> Any ideas where I am going wrong?
>|||Hi Aaron,
Firstly thank you for helping! I am a self taught asp developer, so
wasn't aware of the naming conventions, I will change them straight
away.
wgt_weight_lower/wgt_weight_higher/wgt_country/wgt_Parcel_Price/
wgt_RMSD_Pre9_Price/wgt_RMSD_Pre1_Price/wgt_RMSD_Sat_Price/
wgt_Citylink_Price wgt_Citylink_Sat_Price
0 0.5 uk =A34.90 =A313.00 =A35.60 =A38.10 =A310.40 =A325.40
0=2E51 1 uk =A36.20 =A315.00 =A37.00 =A39.50 =A310.40 =A325.40
1=2E01 2 uk =A36.70 =A318.70 =A39.20 =A311.70 =A310.40 =A325.40
2=2E01 4 uk =A39.70 =A323.00 =A310.40 =A325.40
4=2E01 6 uk =A323.00 =A310.40 =A325.40
6=2E01 8 uk =A323.00 =A310.40 =A325.40
8=2E01 10 uk =A323.00 =A310.40 =A325.40
10.01 15 uk =A312.90 =A327.90
15.01 20 uk =A315.40 =A330.40
I am passing the stored procedure country =3D uk weight =3D 2 and shipping
=3D RMSD9
I am expecting Price to be returned as 6.70
The results that I am getting back at the moment are all the column
names from the table with no data in them at all. It's not like an
empty recordset, this is a row with nothing in it. I am expecting just
the one column, well, the alias called Price.
Cheers,
Steve|||I worked it out!
CREATE PROCEDURE dbo.sp_GetShippingCharge(@.Country varchar(2),
@.Weight decimal(12,2), @.Shipping varchar(12))
AS
SELECT ShippingPrice = CASE @.Shipping WHEN 'Parcel' THEN
dbo.tblShippingRates.wgt_Parcel_Price
WHEN 'RMSD9' THEN dbo.tblShippingRates.wgt_RMSD_Pre9_Price
WHEN 'RMSD1' THEN dbo.tblShippingRates.wgt_RMSD_Pre1_Price
WHEN 'RMSDSat' THEN dbo.tblShippingRates.wgt_RMSD_Sat_Price
WHEN 'Citylink' THEN dbo.tblShippingRates.wgt_Citylink_Price
WHEN 'CitylinkSat' THEN dbo.tblShippingRates.wgt_Citylink_Sat_Price
END
FROM dbo.tblShippingRates
WHERE (wgt_country = @.Country) AND (wgt_weight_lower <= @.Weight)
AND (wgt_weight_higher >= @.Weight)
GO|||It seems that the selection criteria is based on weight and country. I
don't understand the RMSD9 bit. In your case, the query would be something
like:
select
wgt_Parcel_Price
from
MyTable
where
wgt_country = @.country
and
@.weight between wgt_weight_lower and wgt_weight_higher
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Dooza" <doozadooza@.gmail.com> wrote in message
news:1186060353.152602.170690@.q75g2000hsh.googlegroups.com...
Hi Aaron,
Firstly thank you for helping! I am a self taught asp developer, so
wasn't aware of the naming conventions, I will change them straight
away.
wgt_weight_lower/wgt_weight_higher/wgt_country/wgt_Parcel_Price/
wgt_RMSD_Pre9_Price/wgt_RMSD_Pre1_Price/wgt_RMSD_Sat_Price/
wgt_Citylink_Price wgt_Citylink_Sat_Price
0 0.5 uk £4.90 £13.00 £5.60 £8.10 £10.40 £25.40
0.51 1 uk £6.20 £15.00 £7.00 £9.50 £10.40 £25.40
1.01 2 uk £6.70 £18.70 £9.20 £11.70 £10.40 £25.40
2.01 4 uk £9.70 £23.00 £10.40 £25.40
4.01 6 uk £23.00 £10.40 £25.40
6.01 8 uk £23.00 £10.40 £25.40
8.01 10 uk £23.00 £10.40 £25.40
10.01 15 uk £12.90 £27.90
15.01 20 uk £15.40 £30.40
I am passing the stored procedure country = uk weight = 2 and shipping
= RMSD9
I am expecting Price to be returned as 6.70
The results that I am getting back at the moment are all the column
names from the table with no data in them at all. It's not like an
empty recordset, this is a row with nothing in it. I am expecting just
the one column, well, the alias called Price.
Cheers,
Steve|||I think they are zones and/or delivery speeds (e.g. 1 day, 2 day, 9 day,
etc.). So the values in the different columns in the actual table are
actually relevant... the shipping price is not always wgt_Parcel_Price.
--
Aaron Bertrand
SQL Server MVP
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eH8U5iQ1HHA.464@.TK2MSFTNGP02.phx.gbl...
> It seems that the selection criteria is based on weight and country. I
> don't understand the RMSD9 bit. In your case, the query would be
> something
> like:
> select
> wgt_Parcel_Price
> from
> MyTable
> where
> wgt_country = @.country
> and
> @.weight between wgt_weight_lower and wgt_weight_higher
> --
> Tom|||On Aug 2, 2:37 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> I think they are zones and/or delivery speeds (e.g. 1 day, 2 day, 9 day,
> etc.). So the values in the different columns in the actual table are
> actually relevant... the shipping price is not always wgt_Parcel_Price.
Each column that ends in Price is a different courier option.|||Oh, OK. So you'd have a CASE:
CASE @.shipping
WHEN 'RMSD9' then wgt_RMSD_Pre9_Price
WHEN 'RMSD1' then wgt_RMSD_Pre1_Price
...
END
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Dooza" <doozadooza@.gmail.com> wrote in message
news:1186062055.667781.242240@.r34g2000hsd.googlegroups.com...
On Aug 2, 2:37 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> I think they are zones and/or delivery speeds (e.g. 1 day, 2 day, 9 day,
> etc.). So the values in the different columns in the actual table are
> actually relevant... the shipping price is not always wgt_Parcel_Price.
Each column that ends in Price is a different courier option.|||>I worked it out!
Your procedure suddenly started returning data because you changed AS
ShippingPrice to ShippingPrice = and CASE WHEN @.Shipping = to CASE @.Shipping
WHEN ? That doesn't seem right. Anyway, how about readability? Do you
need to repeat dbo.tblShippingRates 18 times?
CREATE PROCEDURE dbo.usp_GetShippingCharge
@.Country VARCHAR(2),
@.Weight DECIMAL(12,2),
@.Shipping VARCHAR(12)
AS
BEGIN
SET NOCOUNT ON;
SELECT
ShippingPrice = CASE @.Shipping
WHEN 'Parcel' THEN wgt_Parcel_Price
WHEN 'RMSD9' THEN wgt_RMSD_Pre9_Price
WHEN 'RMSD1' THEN wgt_RMSD_Pre1_Price
WHEN 'RMSDSat' THEN wgt_RMSD_Sat_Price
WHEN 'Citylink' THEN wgt_Citylink_Price
WHEN 'CitylinkSat' THEN wgt_Citylink_Sat_Price
END
FROM
dbo.tblShippingRates
WHERE
wgt_country = @.Country
AND (@.Weight BETWEEN wgt_weight_lower AND wgt_weight_higher);
END
GO
Finally, since this will only ever return one column and *should* only be
returning one row, why not make it a scalar function, or at least capture
the data via an output parameter?
--
Aaron Bertrand
SQL Server MVP|||On Thu, 02 Aug 2007 06:22:29 -0700, Dooza wrote:
>I worked it out!
Hi Dooza,
Good for you.
However, I think you'd be better off with a different design of your
table. As it is, you'll have to keep adding and removing columns and
changing your code every time a new courier option comes around, when an
option is removed, or even when an option is renamed.
Instead of different price columns for each courier option, you should
have one column Price and one column CourierOption. The latter should of
course be included in the table's key. That would make this query lots
easier!
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Aug 2, 9:06 pm, Hugo Kornelis
<h...@.perFact.REMOVETHIS.info.INVALID> wrote:
> On Thu, 02 Aug 2007 06:22:29 -0700, Dooza wrote:
> >I worked it out!
> Hi Dooza,
> Good for you.
> However, I think you'd be better off with a different design of your
> table. As it is, you'll have to keep adding and removing columns and
> changing your code every time a new courier option comes around, when an
> option is removed, or even when an option is renamed.
> Instead of different price columns for each courier option, you should
> have one column Price and one column CourierOption. The latter should of
> course be included in the table's key. That would make this query lots
> easier!
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis
Hi Hugo,
You are correct, and this is what I have ended up doing, as my
previous attempt didn't allow me to also save the type of shipping in
the database. The way I am doing it now is much better. I can now
create a drop down list with the available options for that particular
weight, before I couldn't do that, and I now have an ID for the
shipping type that I can store in the database with the order.
The user is now presented with the drop down list to select the type
of shipping, once selected the id of the shipping type is inserted
into the database, I will then lookup the ID and pass the price to the
cart to be included with the total. A much neater solution.
Thank you all for steering me in the right direction!
Dooza

Help with CASE and LIKE

Hello guys! i'm having problem with my stored procedure..can anybody please help me.
I have a stored procedure below that is successfully executed/saved/"Compiled"(whatever you called it) but when I try to use it by supplying value to its paramaters it throws an error (Please see the error message below). I suspected that the error occurs from line with the Bold Letters becuase "@.SeacrhArg" variable is of type varchar while columns "Transac.Item_ID" and "Transac.Item_TransTicketNo" is of type Int. What you think guys?

ERROR:

Msg 245, Level 16, State 1, Procedure sp_Transaction_Search, Line 9
Syntax error converting the varchar value 'Manlagnit' to a column of data type int.

STORED PROCEDURE:

USE [RuslinCellPawnShoppeDB]
GO
/****** Object: StoredProcedure [dbo].[sp_Transaction_Search] Script Date: 09/04/2007 08:48:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Transaction_Search]
@.SeacrhArg varchar(20),
@.SearchBy varchar(20),
@.TransType varchar(20),
@.FromDate datetime,
@.Todate datetime
AS
BEGIN
SELECT Customer.Customer_LastName,Customer.Customer_Middl eInitial, Customer.Customer_FirstName, Customer.Customer_Address,
Items.Item_Description,Items.Item_Principal, Transac.ItemTrans_Date_Granted, Transac.ItemTrans_DateCreated,
Transac.ItemTrans_Status, Transac.Item_ID,Transac.Item_TransID,Transac.Item_ TransTicketNo

FROM RCPS_TF_ItemTransaction Transac
INNER JOIN RCPS_Customer Customer
ON Transac.CustomerID = Customer.CustomerID
INNER JOIN RCPS_Items Items
ON Items.ItemID = Transac.Item_ID

WHERE
CASE
WHEN @.SearchBy = 'FirstName' THEN Customer.Customer_FirstName
WHEN @.SearchBy = 'LastName' THEN Customer.Customer_LastName
WHEN @.SearchBy = 'Item ID' THEN Transac.Item_ID
WHEN @.SearchBy = 'Ticket No' THEN Transac.Item_TransTicketNo
END
LIKE @.SeacrhArg AND

Transac.ItemTrans_DateCreated BETWEEN @.FromDate AND dateadd(day,1,@.Todate) AND
(
(@.TransType = 'Pawned' AND Transac.ItemTrans_Status = 1) OR
(@.TransType = 'Renewed' AND Transac.ItemTrans_Status = 2) OR
(@.TransType = 'Redeemed' AND Transac.ItemTrans_Status = 3) OR
(@.TransType = 'Sold' AND Transac.ItemTrans_Status = 5)
)
END

CALL STORED PROCEDURE

USE [RuslinCellPawnShoppeDB]
GO

DECLARE @.return_value int

EXEC @.return_value = [dbo].[sp_Transaction_Search]
@.SeacrhArg = '%man%',
@.SearchBy = 'LastName',
@.TransType = 'Pawned',
@.FromDate = N'9/01/2007 12:00:00 AM',
@.Todate = N'9/6/2007 12:00:00 AM'

SELECT 'Return Value' = @.return_value

GOI suspect Transac.ItemTrans_Status is a varchar and that is where the problem is|||WHERE CASE
WHEN @.SearchBy = 'FirstName' AND Customer.Customer_FirstName LIKE @.SeacrhArg THEN 1
WHEN @.SearchBy = 'LastName' THEN Customer.Customer_LastName LIKE @.SeacrhArg THEN 1
WHEN @.SearchBy = 'Item ID' THEN CONVERT(VARCHAR, Transac.Item_ID) LIKE @.SeacrhArg THEN 1
WHEN @.SearchBy = 'Ticket No' THEN CONVERT(VARCHAR, Transac.Item_TransTicketNo) LIKE @.SeacrhArg THEN 1
ELSE 0
END = 1
AND Transac.ItemTrans_DateCreated >= @.FromDate
AND Transac.ItemTrans_DateCreated < DATEADD(DAY, 1, @.Todate)
AND 1 = CASE
WHEN @.TransType = 'Pawned' AND Transac.ItemTrans_Status = 1 THEN 1
WHEN @.TransType = 'Renewed' AND Transac.ItemTrans_Status = 2 THEN 1
WHEN @.TransType = 'Redeemed' AND Transac.ItemTrans_Status = 3 THEN 1
WHEN @.TransType = 'Sold' AND Transac.ItemTrans_Status = 5 THEN 1
ELSE 0
END|||Thanks a loT Peso..But I preferred to use BETWEEN..AND.. for date unless you have there agood reason to use the <>= sign..But anyway thanks alot!|||if you use BETWEEN, you inadvertently get too may records.

BETWEEN '20070101' AND '20071231' will NOT get the records dated "20071231 10:14:23"

BETWEEN '20070101' AND '20080101' will get the records dated "20071231 10:14:23" but ALSO the records dated "20080101 00:00:00".

>= '20070101' <'20080101' will get the records dated "20071231 10:14:23" but NOT the records dated "20080101 00:00:00".

Monday, March 26, 2012

Help with an Update/Insert procedure

Hello,
I'm new to SQL in general and I'm looking for the most efficient stored
procedure that can accomplish the following:
If aRecord with field "RecID" = # is found in aTable then
{
If aRecord's field "Updateable"=True then
UPDATE aRecord...
}
Else
{
INSERT aNewRecord into aTable ...
}
Basically, I'm submitting the field parameters for a record to a table in
the stored procedure. If a record already exists with the same primary key
in that table , then, if a field's value in that existing record isn't
"False", update it. Otherwise, if there is no record with that primary key
value, then insert this new record into the table.
Thanks and let me know if this doesn't make any sense!
WykAssuming "RecID" (what a horrible column name!) is a primary key, probably
most efficient is to try to update it first, and if it didn't work, insert.
UPDATE ... WHERE RecID = @.RecID
IF @.@.ROWCOUNT = 0
INSERT ...
You could do something slightly more elaborate, to make the code more
readable, with negligible performance differences:
IF EXISTS (SELECT 1 FROM table WHERE RecID = @.RecID)
UPDATE ...
ELSE
INSERT ...
You're going to have to test in your own environment to be sure, of course.
Most efficient on my schema is not necessarily most efficient on your
schema.
"Wyk" <wykananda@.hotmail.com> wrote in message
news:%23aYKY$$OGHA.2604@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I'm new to SQL in general and I'm looking for the most efficient stored
> procedure that can accomplish the following:
>
> If aRecord with field "RecID" = # is found in aTable then
> {
> If aRecord's field "Updateable"=True then
> UPDATE aRecord...
> }
> Else
> {
> INSERT aNewRecord into aTable ...
> }
> Basically, I'm submitting the field parameters for a record to a table in
> the stored procedure. If a record already exists with the same primary key
> in that table , then, if a field's value in that existing record isn't
> "False", update it. Otherwise, if there is no record with that primary
> key value, then insert this new record into the table.
> Thanks and let me know if this doesn't make any sense!
> Wyk
>
>|||Here's what I've tried which doesn't seem to work though it compiles...
PROCEDURE [dbo].[p_MergeRecordIntoTable]
(
@.Id bigint,
@.Field1 nvarchar(50)
@.Updateable bit
)
AS
BEGIN
SET NOCOUNT ON
SELECT Count(*)
FROM [aTable]
WHERE ( Id= @.Id)
IF @.@.ROWCOUNT != 0
BEGIN
UPDATE [MergeTable]
SET
Field1= @.Field1
WHERE
( Id= @.Id )
AND
( Updateable = 1 )
END
ELSE
BEGIN
INSERT INTO [SI_MLB_Games] (
ID,
Field1,
Updateable,
VALUES (
@.Id,
@.Field1,
@.Updateable)
END
END
"Wyk" <wykananda@.hotmail.com> wrote in message
news:%23aYKY$$OGHA.2604@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I'm new to SQL in general and I'm looking for the most efficient stored
> procedure that can accomplish the following:
>
> If aRecord with field "RecID" = # is found in aTable then
> {
> If aRecord's field "Updateable"=True then
> UPDATE aRecord...
> }
> Else
> {
> INSERT aNewRecord into aTable ...
> }
> Basically, I'm submitting the field parameters for a record to a table in
> the stored procedure. If a record already exists with the same primary key
> in that table , then, if a field's value in that existing record isn't
> "False", update it. Otherwise, if there is no record with that primary
> key value, then insert this new record into the table.
> Thanks and let me know if this doesn't make any sense!
> Wyk
>
>|||Aaron, I was able to make it work perfectly with the your second example.
Not sure how I could make it work with the first though.
Big thanks!
Wyk
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OErAwLAPGHA.1288@.TK2MSFTNGP09.phx.gbl...
> Assuming "RecID" (what a horrible column name!) is a primary key, probably
> most efficient is to try to update it first, and if it didn't work,
> insert.
> UPDATE ... WHERE RecID = @.RecID
> IF @.@.ROWCOUNT = 0
> INSERT ...
> You could do something slightly more elaborate, to make the code more
> readable, with negligible performance differences:
> IF EXISTS (SELECT 1 FROM table WHERE RecID = @.RecID)
> UPDATE ...
> ELSE
> INSERT ...
> You're going to have to test in your own environment to be sure, of
> course. Most efficient on my schema is not necessarily most efficient on
> your schema.
>
>
> "Wyk" <wykananda@.hotmail.com> wrote in message
> news:%23aYKY$$OGHA.2604@.TK2MSFTNGP09.phx.gbl...
>

Friday, March 23, 2012

Help with a stored procedure/query?

I did a join on two tables to get the following results. I saved the
results in a #temptable.

idtable2idtable2descripdateinserted
================================================== ==
13descrip111/3/2002
24descrip211/2/2002
33descrip111/4/2001
43descrip110/5/2003
54descrip212/8/2001
65descrip39/10/2002

I want to query that #temptable to get the max date for each table2id
and only return those record. So I need a query to get the follow
results...

idtable2idtable2descripdateinserted
================================================== ==
24descrip211/2/2002
43descrip110/5/2003
65descrip39/10/2002

Question...
What query can I make with #temptable to give me the results?try...

select *
from #tmp t1
where t1.id=(select top 1 id
from #tmp t2
where t2.table2id=t1.table2id
order by dateinserted desc)

--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net

<newsgroper@.yahoo.com> wrote in message
news:70dd91df.0311191736.109cde98@.posting.google.c om...
> I did a join on two tables to get the following results. I saved the
> results in a #temptable.
>
> id table2id table2descrip dateinserted
> ================================================== ==
> 1 3 descrip1 11/3/2002
> 2 4 descrip2 11/2/2002
> 3 3 descrip1 11/4/2001
> 4 3 descrip1 10/5/2003
> 5 4 descrip2 12/8/2001
> 6 5 descrip3 9/10/2002
> I want to query that #temptable to get the max date for each table2id
> and only return those record. So I need a query to get the follow
> results...
> id table2id table2descrip dateinserted
> ================================================== ==
> 2 4 descrip2 11/2/2002
> 4 3 descrip1 10/5/2003
> 6 5 descrip3 9/10/2002
> Question...
> What query can I make with #temptable to give me the results?

Help with a Stored Procedure..

Can anybody help me with this stored procedure? Basically I need to do
something like do:
Total = Premium + (WHEN AddOn1 != -1) + (WHEN AddOn2 != -1) + (WHEN
AddOn3 != -1)
To clarify, the value 'Total' needs to consist of a field called
'Premium' plus the AddOn1, AddOn2, AddOn3 IF they are not equal to -1.
Hope this is clear.
Can anyone help?
Regards,
ChrisHi Chris,
Total = Premium + (CASE WHEN AddOn1 != -1 THEN AddOn1 ELSE 0 END)
(CASE WHEN AddOn2 != -1 THEN AddOn2 ELSE 0
END)
(CASE WHEN AddOn3 != -1 THEN AddOn3 ELSE 0
END)
HTH, jens Suessmeyer.|||Maybe some more plus signs might be in order, Jens:
Total = Premium + (CASE WHEN AddOn1 != -1 THEN AddOn1 ELSE 0 END)
+ (CASE WHEN AddOn2 != -1 THEN AddOn2 ELSE 0 END)
+ (CASE WHEN AddOn3 != -1 THEN AddOn3 ELSE 0 END)
:)
ML

Help with a stored procedure or DTS package

Can anyone offer me a solution to this , i have a table that hold
QI AN Quantity Price Order Refer
Area
28 96229392 15 83.98 1 A1
Level 1
28 960004877 55 192.68 2 B
Level 1
28 96011194 56 102.66 3 B1
Level 1
28 96011194 112 10.66 3 C
Level 2
and i want to transform it to
QI AN Quantity Price Refer
Area
28 grupanfa 0
Level 1
28 96229392 15 83.98 A1
28 960004877 55 192.68 B
28 96011194 56 102.66 B1
28 grupenda 0
28 grupanfa 0
Level 2
28 96011194 112 10.66 C
28 grupenda 0
can anyone please advise
Regards
JohnAre both these tables in sql server? what are the data types?
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"John" <topguy75@.hotmail.com> wrote in message
news:446afe1a$0$692$fa0fcedb@.news.zen.co.uk...
> Can anyone offer me a solution to this , i have a table that hold
> QI AN Quantity Price Order Refer
> Area
> 28 96229392 15 83.98 1 A1
> Level 1
> 28 960004877 55 192.68 2 B
> Level 1
> 28 96011194 56 102.66 3 B1
> Level 1
> 28 96011194 112 10.66 3 C
> Level 2
> and i want to transform it to
> QI AN Quantity Price Refer
> Area
> 28 grupanfa 0
> Level 1
> 28 96229392 15 83.98 A1
> 28 960004877 55 192.68 B
> 28 96011194 56 102.66 B1
> 28 grupenda 0
> 28 grupanfa 0
> Level 2
> 28 96011194 112 10.66 C
> 28 grupenda 0
> can anyone please advise
> Regards
> John
>|||The first talbe is in SQL, the second need creating as a temporary table,
can assume all columns are varchar(50)
Regards
john
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:8tOdnTrKJqZY1fbZRVnyuw@.bt.com...
> Are both these tables in sql server? what are the data types?
> --
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "John" <topguy75@.hotmail.com> wrote in message
> news:446afe1a$0$692$fa0fcedb@.news.zen.co.uk...
>|||Where are groupenda and groupanfa coming from? Are they indicating the
start and end of groups, based on level? Why do these have a value of 0,
and is the 0 supposed to represent anything?
Are you trying to create a flat file for an export to another system?
This looks like something that you should be doing in an application or
report, rather than in SQL.
"John" <topguy75@.hotmail.com> wrote in message
news:446afe1a$0$692$fa0fcedb@.news.zen.co.uk...
> Can anyone offer me a solution to this , i have a table that hold
> QI AN Quantity Price Order Refer
> Area
> 28 96229392 15 83.98 1 A1
> Level 1
> 28 960004877 55 192.68 2 B
> Level 1
> 28 96011194 56 102.66 3 B1
> Level 1
> 28 96011194 112 10.66 3 C
> Level 2
> and i want to transform it to
> QI AN Quantity Price Refer
> Area
> 28 grupanfa 0
> Level 1
> 28 96229392 15 83.98 A1
> 28 960004877 55 192.68 B
> 28 96011194 56 102.66 B1
> 28 grupenda 0
> 28 grupanfa 0
> Level 2
> 28 96011194 112 10.66 C
> 28 grupenda 0
> can anyone please advise
> Regards
> John
>sql

help with a stored procedure

here is my sp

create proc [login]

(

@.email as varchar(200)

@.password as varchar(200)

)

begin

select customerid from customerlogin

where email = @.email

and password = @.password

End

print 'Welcome'

print (@.email)

End

This works fine, but if the email is wrong, what do I need to add to this to print that the email is wrong and not print 'welcome email'

Help, please.....

Hello,

I think what you want is:

create proc [login]

(

@.email as varchar(200)

@.password as varchar(200)

)

IF EXISTS (select customerid from customerlogin where email = @.email and password = @.password)

BEGIN

print 'Welcome'

print (@.email)

END

ELSE

BEGIN

print 'Bad login!'

print (@.email)

END

As a side note, best practice is to NOT pass the users' password, but rather store a hash of the password and then when the user attempts to login, you simply create a hash of the entered password and compare it with the hashed value stored in the db.

Cheers

Rob

|||

create procedure sp_login

@.email as varchar(200)

@.password as varchar(200)

Select CASE WHEN ((Select COUNT(*) from customerlogin where email = @.email and password = @.password) = 0)

THEN Print 'Invalid Login'

ELSE Print 'Welcome ' + @.email

END AS [EmailTest]

Adamus

Help with a stored procedure

Is it possible to do something like this? I have all the records in #rsltTable (it has an Index and OrderID) I want to be efficient, so I am trying to use the OrderID's from #rsltTable to SELECT all of the records that I need to return. Right now I am getting "Msg 102, Level 15, State 1, Procedure pe_getAppraisals3, Line 119 Incorrect syntax near '>'."

SELECT * FROM Orders WHERE OrderIDIN(SELECT OrderIDFROM #rsltTableWHERE ID=> @.l_FirstRecordAND ID<= @.l_LastRecord)

IF you need more of the SQL I can post it.

Thank You,
Jason

Greater than or equal to is >=, not =>.

SELECT * FROM Orders WHERE OrderIDIN(SELECT OrderIDFROM #rsltTableWHERE ID>= @.l_FirstRecordAND ID<= @.l_LastRecord)

HTH,
Ryan

|||Last time I program after having teeth pulled...

Help with a stored procedure

Hi,
Im very new to this posting business so if Im posting in the wrong place or make a faux pa, I apologise

Ive been stuck for a couple of days with the following error, any help would be great!
I created a simple stored procedure which worked fine, then added parameters and it all fell to pieces.

Incorrect syntax near 'StoredProcedure1'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Incorrect syntax near 'StoredProcedure1'.

ALTER PROCEDUREdbo.StoredProcedure1

@.MYValuevarchar(50)

BEGIN

SELECT[User].*FROM[User]WHERE[User].UserName = @.MyValueEND

>>>>>>>>>>>>>>>>>>>>>

And I call the procedure here:

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"SelectCommand=StoredProcedure1><SelectParameters><asp:SessionParameterName="MYValue"SessionField="name"/></SelectParameters>

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

I've tried fiddling with pretty much everything, but nothing has worked yet.

Cheers

Dan

Can you test this:

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT[User].*FROM[User]WHERE[User].UserName = @.MyValue" >

<asp:SessionParameterName="MYValue"SessionField="name" type="string"/>

</SelectParameters>

</asp:SqlDataSource>

Or you can test this to see whether your SP works:

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="StoredProcedure1" SelectCommandType="StoredProcedure" >

<asp:SessionParameterName="MYValue"SessionField="name" type="string"/>

</SelectParameters>

|||

Whoah, that second suggestion fixed all! Im so stoked,

Cheers mate

Dan

Help with a store procedure

I am trying to create a store procedure that look at a table and only maintain 30 worth of data only forever. The data that fall outside the 30 days need to be deleted. Can anyone help me with this?
Thanks
Lystracreate a time stamp field on your table. Then you can create a sp like...

Create Proc Delete_Old_Junk
As
DELETE FROM TABLE WHERE DATE(dd,timestampfield,Getdate()) > 30|||dateDIFF:

DELETE FROM TABLE WHERE DATEDIFF(dd,timestampfield,Getdate()) > 30

You will need to schedule this procedure as a job that runs once per day.|||Thanks you guy, it did the trick.

Lystra|||just going to fast, spinning too many plates, too much coffee and soda, too many things going on.

but in 99 minutes I splash down on my couch with all of my favorite indulgences.|||...and you totally deserve it. Have a great weekend!

Lystra

Wednesday, March 21, 2012

Help with a special stored procedure

I have a table there have a primary key, this value have i also in another
table as a forreignkey, so good so far.

Here it is
If the number in may primarykey in table 1 , dosn't exits in table 2 then
delete records in table 1

I have made this in a ASP page with a view there list all records where the forreignkey in table 2 are NULL and then delte all records in table one

Can i made this as a stored procedure ?

regards

alvin

You really want a trigger on table 1 to check table 2 before insert.

Research INSTEAD OF Insert Triggers.

What this will do is instead of inserting a new record into table 1, it will check table 2 for a match. If there is no match, no record is inserted into table 1.

It's better to catch the record before it goes in, than to insert the record and then have to remove it later.

Adamus

|||

I don't believe you understand

When i made a post in table one i also make a post in table 2

All works fine

But tabel 2 i also connected with table 3 and here i have in my diagram a cascade delete

So when i delete a record in table 3 it's delete a record or more in table 2
then i have my record in table 1, this i can't delete when i delete the record in table 3
so what i want is to delete all record in table 1 if the record in table 2 are deleted

hop you understand ?

Alvin

|||

Ok table 3 is new...but the answer is still the same...triggers not sp's

When you delete from any table, also delete from other tables...correct?

So you want INSTEAD OF DELETE

Adamus

|||

I try to explain and maybe you can help

In table 1 i have a ID theis ID can bee many times in table 2
Table 2 have also a int there connect to table3

all works fine

When a date field in table3 is over current date then i delete the record in table
And when it does this it also delete the record in table2.

after sometime all the records in table2 there have the ID from table 1 is been deleted
and when there are no more record in table2 there in my feild have the same numbers
as the ID in table 1 then i wnt to delete the record in table 1

Like:

Delete all records in table 1 if table1.ID <> from table2.field

In vbscript i can make a loop to check if the Id from table1 are in table2
if the catch this number = do nothing
if the find the number = delte record

i Can't explain it better. Sorry

Alvin

|||

It looks to me like you're wanting something like this:

create procedure delete_records_from_table1

delete from table1

from table1

left join table2

on table1.ID = table2.ID

where table2.ID is null

The left join will include everything in table1, regardless of whether there's a matching record in table2, and then the where clause excludes all of the records where there is a match in table2. This just leaves those records where there's no match in table2, and these can be deleted.

Is this the sort of thing you were looking for?

Iain

|||

Yes yes yes

Thanks

Alvin

sql

Help with a SP

Code:
ALTER PROCEDURE GetBookedResource
(
@.StartDate datetime,
@.EndDate datetime,
@.Resource char(30)
)
AS

SELECT *
FROM tblBookings
WHERE StartDate >= @.StartDate and EndDate <= @.EndDate and
Resource=@.Resource

__________________________________________________ ______________________
_____________________________

The SP I'm using above is used to find out if a resource is free for a
particular period of time.

The time at my workplace is split into 6 periods...

P1 starts at 9am and ends at 10am....
P2 starts as 10.01am and ends at 11am...e.t.c.

When Bookings are added...I append the time to the Start/End Date
depending on what period the end user has selected..
e.g..

Quote:
If end user has booked a resource for today at P1 then I would have
appended 9am to StartDate and 10am to the EndDate before I pass both the
StartDate and EndDate to the SP.

If end user has booked a resource from today at P1 to tomorrow at P2
then I would have appended 9am to StartDate and 11am to the EndDate
before I pass both the StartDate and EndDate to the SP.

The Problem
The SP only returns the correct data when the @.StartDate and @.EndDate
are on the same day and period
e.g..

Quote:
@.StartDate=Today 9AM and @.EndDate=Today 10AM

It doesn't return the current data when @.StartDate and @.EndDate are on
the same day but on different periods
e.g..

Quote:
@.StartDate=Today 9AM and @.EndDate=Today 11AM

Nor does it return the correct data when @.StartDate and @.EndDAte on on
different Days
e.g.

Quote:
@.StartDate=Today 9AM and @.EndDate=Tomorrow 11AM

Can anyone explain why that is??

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Kieran Dutfield (kolo83@.talk21.com) writes:
> The Problem
> The SP only returns the correct data when the @.StartDate and @.EndDate
> are on the same day and period
> e.g..
>
> Quote:
> @.StartDate=Today 9AM and @.EndDate=Today 10AM
> It doesn't return the current data when @.StartDate and @.EndDate are on
> the same day but on different periods
> e.g..
> Quote:
> @.StartDate=Today 9AM and @.EndDate=Today 11AM
> Nor does it return the correct data when @.StartDate and @.EndDAte on on
> different Days
> e.g.
>
> Quote:
> @.StartDate=Today 9AM and @.EndDate=Tomorrow 11AM

I think you need to provide more input. What does your actual calls
look like? To me it sounds it is when you add the time portions that
things go wrong.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> SELECT *
> FROM tblBookings
> WHERE StartDate >= @.StartDate and EndDate <= @.EndDate and
> Resource=@.Resource

Given that tblBookings show when a resource is busy and has 3 fields:
RESOURCE, BSTART, & BEND. And you want to know if the resource is
free for a given period: @.START & @.END. Consider the cases below. A
resource is free if:

exists(select * from tblbookings where resource=@.resource and
(datediff(minute,@.end,bstart)>=0 OR datediff(minute,bend,@.start) >=0))

a) Resource is busy
BStart Bend
---|---|
| |
@.Start @.End

b) Resource is busy
BStart Bend
---|---|
| |
@.Start @.End

c) Resource is busy
BStart Bend
---|---|
| |
@.Start @.End

d) Resource is free
BStart Bend
---|---|
| |
@.Start@.End

e) Resource is free
BStart Bend
---|---|
| |
@.Start @.End|||Here is a link to another forum I sumitted the problem to...It gives a
bit more detail

http://www.vbcity.com/forums/topic.asp?tid=56539

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||
I want to convert the period given into the appropiate datetime when it
is passed to the SP...
e.g. the below parameter value would be the value I would pass for 1st
Jan 2004 starting Period 1

Quote:
@.StartDate=#1/1/2004 9:00:00 AM#

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||> SELECT *
> FROM tblBookings
> WHERE StartDate >= @.StartDate and EndDate <= @.EndDate and
> Resource=@.Resource

Hi,

Ok second attempt. I think I understand now that there are multiple
rows for each resource. Then change the where clause to identify if
resource is busy and then wrap it with a not exists.

not exists(
SELECT * FROM tblBookings
WHERE Resource=@.Resource and (
(@.startdate between startdate and enddate) or
(@.enddate between startdate and enddate)
)
)|||Kieran Dutfield (kolo83@.talk21.com) writes:
> Here is a link to another forum I sumitted the problem to...It gives a
> bit more detail
> http://www.vbcity.com/forums/topic.asp?tid=56539

Tried the link, but it appears that I have to register to view it. So
you may prefer to repost the information here.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Help with a return value stored procedure that will be used in a calculation

Hi, here is my dilema. I am trying to create an application for overtime recruitment. Basically, I will assign the total hours needed in a variable for each individual time block. What I want to do is show the agents how much time is left on each block based on a selection from a drop down. What I've done so far is create a procedure that sums up the total hours signed up for based on a parameter which will be provided by the drop down menu. I'm very green to asp.net still so I'm having problems using the value from the procedure in a calculation that will subtract the hours taken from the alloted hours. Here is what I've done so far. If anyone has any idea on how I can do this or can point me in the right direction. Maybe I'm taking the wrong approach? Any help would be appreciated. Thanks!
---------------------- VB.Net Code

PrivateSub Page_Load(ByVal senderAs System.Object,ByVal eAs System.EventArgs)HandlesMyBase.Load

lblNameV.Text = (User.Identity.Name)

':::::Declare Department Variables

Dim HoursAsInteger

Dim Sunday2AsInteger

Sunday2 = 10

':::::Fill Supervisor Drop Down

IfNot Page.IsPostBackThen

'Supervisor

Dim cmdSupervisorAs SqlCommand =New SqlCommand("SUPERVISOR", SqlConnection1)

cmdSupervisor.CommandType = CommandType.StoredProcedure

SqlConnection1.Open()

Dim drSupervisorAs SqlDataReader

drSupervisor = cmdSupervisor.ExecuteReader()

lstSupervisor.DataSource = drSupervisor

lstSupervisor.DataTextField = "Supervisor"

lstSupervisor.DataBind()

drSupervisor.Close()

SqlConnection1.Close()

EndIf

':::::Find Agent Name

Dim dsAsNew DataSet

Dim workparamAsNew SqlParameter("@.KMSID", System.Data.SqlDbType.Char)

workparam.Direction = ParameterDirection.Input

workparam.Value = (User.Identity.Name)

Dim danameAsNew SqlDataAdapter

daname.SelectCommand =New SqlCommand

daname.SelectCommand.Connection = SqlConnection1

daname.SelectCommand.CommandText = "NTAGENTNAME"

daname.SelectCommand.CommandType = CommandType.StoredProcedure

daname.SelectCommand.Parameters.Add(workparam)

ds =New DataSet

daname.Fill(ds)

If ds.Tables(0).Rows.Count = "0"Then

lblFName.Text = ""

Else

lblFName.Text = ds.Tables(0).Rows(0)("name")

EndIf

':::::Fill Drop Down With Available Shifts

If dlDept.SelectedValue = "Select a Department"Then

dlShift.Items.Clear()

ElseIf dlDept.SelectedValue = "Video"Then

Dim cmdVideoAs SqlCommand =New SqlCommand("AVAILABLEOTVIDEO", SqlConnection2)

cmdVideo.CommandType = CommandType.StoredProcedure

SqlConnection2.Open()

Dim drVideoAs SqlDataReader

drVideo = cmdVideo.ExecuteReader()

dlShift.DataSource = drVideo

dlShift.DataTextField = "Shift"

dlShift.DataBind()

drVideo.Close()

SqlConnection2.Close()

ElseIf dlDept.SelectedValue = "Sales"Then

Dim cmdSalesAs SqlCommand =New SqlCommand("AVAILABLEOTSALES", SqlConnection2)

cmdSales.CommandType = CommandType.StoredProcedure

SqlConnection2.Open()

Dim drSalesAs SqlDataReader

drSales = cmdSales.ExecuteReader()

dlShift.DataSource = drSales

dlShift.DataTextField = "Shift"

dlShift.DataBind()

drSales.Close()

SqlConnection2.Close()

ElseIf dlDept.SelectedValue = "Retention"Then

dlShift.Items.Clear()

ElseIf dlDept.SelectedValue = "Tier 1"Then

dlShift.Items.Clear()

ElseIf dlDept.SelectedValue = "Tier 2"Then

dlShift.Items.Clear()

EndIf

EndSub

PrivateSub dlShift_SelectedIndexChanged(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles dlShift.SelectedIndexChanged

Dim AvailAsNew SqlCommand("AVAILABLEOT", SqlConnection2)

Avail.CommandType = CommandType.StoredProcedure

Dim workparam1AsNew SqlParameter("@.Shift", System.Data.SqlDbType.Char)

workparam1.Direction = ParameterDirection.Input

workparam1.Value = (dlShift.SelectedValue)

Dim TimeAsNew SqlParameter("@.Return_Value", SqlDbType.Int)

Time.Direction = ParameterDirection.ReturnValue

Avail.Parameters.Add(workparam1)

Avail.Parameters.Add(Time)

SqlConnection2.Open()

Dim readerAs SqlDataReader = Avail.ExecuteReader()

SqlConnection2.Close()

Dim retValParamAsInteger = Convert.ToInt32(Time.Value)

Label1.Text = retValParam

EndSub
---------------------- Stored Proc
CREATE PROCEDURE AVAILABLEOT

(
@.Shift [varchar](250)
)

SELECT SUM(HoursRequested) AS Hours
FROM [TBL: OT]
WHERE (ShiftRequested = @.Shift)
GO

In VB most of employee time related code is in the link below including stored procedures. Hope this helps.
http://asp.net/TimeTrackerStarterKit/Docs/Docs.htm

Help with a Query

In a stored procedure that performs a search on a Client table, one
condition in my WHERE clause is:
WHERE
(ISNULL(ClientPhone, '%%') LIKE '%' + COALESCE(@.ClientPhone,
ClientPhone, '') + '%')
This allows me to search on the ClientPhone column depending on whether
or not the @.ClientPhone parameter was NULL or not.
This works great, except that I recently noticed that if I leave the
@.ClientPhone parameter as NULL, the search results are exluding rows
which have the ClientPhone formatted as [1] 310-555-1212
So, any row in which the phone number has the [ and ] characters is not
being returned.
How should my condition in the WHERE clause change to handle this?
Thank you for your helpOn 4 Oct 2005 11:09:23 -0700, george.durzi@.gmail.com wrote:

>In a stored procedure that performs a search on a Client table, one
>condition in my WHERE clause is:
>WHERE
> (ISNULL(ClientPhone, '%%') LIKE '%' + COALESCE(@.ClientPhone,
>ClientPhone, '') + '%')
>This allows me to search on the ClientPhone column depending on whether
>or not the @.ClientPhone parameter was NULL or not.
>This works great, except that I recently noticed that if I leave the
>@.ClientPhone parameter as NULL, the search results are exluding rows
>which have the ClientPhone formatted as [1] 310-555-1212
>So, any row in which the phone number has the [ and ] characters is not
>being returned.
>How should my condition in the WHERE clause change to handle this?
>Thank you for your help
Hi George,
The reason is that the characters [ and ] in a LIKE expression have a
special meaning (see description in Books Online for details).
Here's a way to get around this:
WHERE
REPLACE(REPLACE(COALESCE(ClientPhone, '%%'), '[', '('), ']', ')')
LIKE
REPLACE(REPLACE('%' + COALESCE(@.ClientPhone, ClientPhone, '') + '%'),
'[', '('), ']', ')')
Ugly? Yes.
If I were you, I'd change the existing square brackets in your data to
round brackets (as is the custom for phone numbers), and add a CHECK
constraint on the column to ensure no new square brackets are entered.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks for your reply. As you recommended, I'll clean up the data to
get rid of the [ and ] in the ClientPhone column. I'll also add the
CHECK constraint to prevent those from getting into that column..
Thanks again|||Please provide DDL and sample data, since there must be a simpler way to do
what you need. In the mean time try replacing "[" and "]" with "(" and ")"
inside the where clause.
Read more about wildcard characters here:
http://msdn.microsoft.com/library/d...br />
115x.asp
A far better solution involves a few extra columns inside the table (or a
view) where individual parts of the phone number are stored, since it is
quite obvious that on of your business requirements might benefit greatly
from this.
MLsql

Monday, March 19, 2012

Help with a hierarchy query or procedure

I have a table with a parent, child, and grandchild relationship. Can anyone help me with a query that will return the child and grandchild of a parent?

Heres my table:

id pid name
----------
1 0 UntID
2 0 Vin Number
3 0 Make
4 3 Model
5 4 Model Number
6 0 Model Year
7 0 Vehicle Type
8 0 Odometer Miles

When I select 3 as the id I need these results:

id pid name
----------
3 0 Make
4 3 Model
5 4 Model Number

Thanks for any help!

Ryan

ive successfully used a technique very much like this:http://www.developerfusion.co.uk/show/4633/2/

By tracking your "hierarchy path" (lineage), you can easily pull out an entire branch (Parent/child/grandchild...) of your tree.
By tracking your "hierarchy depth", you can also limit how much of a branch your pull out.

I prefer this approch over something recursive.

If you implement this, your data would end up like this:

id pid name lineage----------1 0 UntID /1/2 0 Vin Number /2/3 0 Make /3/4 3 Model /3/4/5 4 Model Number /3/4/5/6 0 Model Year /6/7 0 Vehicle Type /7/8 0 Odometer Miles /8/

And you query would look like this:

SELECT *FROM someTableWHERE lineageLIKE'/3/%';

|||

This SQL command will get all information required for all tables.

Child, Parent, GranShild are ur tables

select Parent.Id, Child.Id,GrandShild.Id from Parent inner join Child on Parent.Id = Child.Pid inner join GrandChild on Child.id = GrandChild.Pid

where ...

Inform me if this helps

|||I cant change the structure I have. So I need something different. Thanks anyway!|||Try using the command i showed u, doesnt force u to change any structure|||But theres only one table|||provide me with ur table fields, in order to write for u the sql query|||

Hi there,

this query works

SELECT
PARENT.*
FROM
PARENT
INNER JOIN PARENT CHILDS ON PARENT.ID = CHILDS.PID
INNER JOIN PARENT GRANDCHILDS ON CHILDS.ID = GRANDCHILDS.PID
WHERE
PARENT.ID = 3
UNION
--CHILDS RECORDS
SELECT
CHILDS.*
FROM
PARENT
INNER JOIN PARENT CHILDS ON PARENT.ID = CHILDS.PID
INNER JOIN PARENT GRANDCHILDS ON CHILDS.ID = GRANDCHILDS.PID
WHERE
PARENT.ID = 3
UNION
--GRANDCHILDS RECORDS
SELECT
GRANDCHILDS.*
FROM
PARENT
INNER JOIN PARENT CHILDS ON PARENT.ID = CHILDS.PID
INNER JOIN PARENT GRANDCHILDS ON CHILDS.ID = GRANDCHILDS.PID
WHERE
PARENT.ID = 3

I hope it helps

Regards,

Fernando

|||

Thanks FerVitale, that works the best so far. The only problem is that I also have parent and child relationships without grandchildren and if I set that parent as the parentID I get no records. Any thoughts?

Ryan

|||Remove the inner join and replace them with left outer join it should work fine|||

Cool, now the only problem is that if there is a parent/child but no grandchild, the top row is all nulls. any ideas?

Ryan

|||

dear Ryan Try this a bit

Remove all unions just keep the folowing

SELECT
PARENT.*
FROM
PARENT
left outer JOIN PARENT CHILDS ON PARENT.ID = CHILDS.PID
Left outer JOIN PARENT GRANDCHILDS ON CHILDS.ID = GRANDCHILDS.PID
WHERE
PARENT.ID = 3

|||

That only gives me the parent and child in a single row. I need a row for each relationship. I found something else that works perfectly.

declare @.idintset @.id = 3select config_id, config_pid, config_namefrom crm_map_configwhereconfig_id = @.idor config_pid = @.idor config_pidin ( select config_id from crm_map_config whereconfig_pid=@.id)

Thanks for everyones help,
Ryan

|||

Hi,

this script will work, no matter how deep is your parent/child/grandchild relation:

DECLARE

@.Hierarchy_ListTABLE(

INT,

PID

INT,NAMEVARCHAR(50),LevelINT)

DECLARE

@.LevelINT

SET

@.Level= 1

INSERT

INTO @.Hierarchy_ListSELECT*,

@.Level

ASLevelFROM dbo.HierarchyWHERE ID= 3 --This get the parent you are looking for

WHILE(@.@.ROWCOUNT> 0) --now get all child/grandchild/grand-grandchild/etc.BEGINSET @.Level= @.Level+ 1INSERTINTO @.Hierarchy_ListSELECT H.*,

@.Level

ASLevelFROM dbo.Hierarchy H(NOLOCK)INNERJOIN @.Hierarchy_List LON H.PID= L.IDAND L.Level= @.LEVEL-1ENDSELECT id, pid, [Name]FROM @.Hierarchy_List

Hope this helps

|||

kpeguero,

Thanks, thats even better!

Ryan

Monday, March 12, 2012

Help with @@Rowcount

I am writing a vb.net application that calls a stored procedure and need some
help.
I am writting the procedure to check if multiple records exists and the only
way I can figure it out is to use @.@.RowCount, but can't get the right result,
please help.
What I have now is
if exists(select c_driver from cartons where orderid = @.orderid and
@.@.Rowcount = 1)
update...
I tried using the following but got an error in my vb application becuase
the Procedure was returning rows
select c_driver from cartons where orderid = @.orderid
if @.@.Rowcount = 1
update...
Is there any way to use the above query without returning rows to VB?
Is there a way to write an exists to query @.@.Rowcount?
What are you querying @.@.ROWCOUNT for? @.@.ROWCOUNT returns the rowcount of
the last operation... I have a feeling you really want:
if (select COUNT(*) from cartons where orderid = @.orderid) = 1
update ...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
news:7A9F67DA-3C32-445F-B9AD-8A69F4855292@.microsoft.com...
> I am writing a vb.net application that calls a stored procedure and need
some
> help.
> I am writting the procedure to check if multiple records exists and the
only
> way I can figure it out is to use @.@.RowCount, but can't get the right
result,
> please help.
> What I have now is
> if exists(select c_driver from cartons where orderid = @.orderid and
> @.@.Rowcount = 1)
> update...
>
> I tried using the following but got an error in my vb application becuase
> the Procedure was returning rows
> select c_driver from cartons where orderid = @.orderid
> if @.@.Rowcount = 1
> update...
> Is there any way to use the above query without returning rows to VB?
> Is there a way to write an exists to query @.@.Rowcount?
>
>
|||I assume you want to update the row if it already exists and insert the row
if it doesn't exist. If so this should work...
UPDATE cartons SET
...
WHERE orderid = @.orderid
IF (@.@.ROWCOUNT = 0)
BEGIN
INSERT INTO cartons (...) VALUES (...)
END
Hope this helps.
Paul
"John Shepherd" wrote:

> I am writing a vb.net application that calls a stored procedure and need some
> help.
> I am writting the procedure to check if multiple records exists and the only
> way I can figure it out is to use @.@.RowCount, but can't get the right result,
> please help.
> What I have now is
> if exists(select c_driver from cartons where orderid = @.orderid and
> @.@.Rowcount = 1)
> update...
>
> I tried using the following but got an error in my vb application becuase
> the Procedure was returning rows
> select c_driver from cartons where orderid = @.orderid
> if @.@.Rowcount = 1
> update...
> Is there any way to use the above query without returning rows to VB?
> Is there a way to write an exists to query @.@.Rowcount?
>
>
|||I tried the count() but I don't get what I need
what I need to do is determin is if a single driver is assigned to multiple
cartons within an order. for Instance say driver #1 was assigned to Carton
100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of these
cartons are in Order #1000
Order# Carton# Driver#
1000 100 1
1000 101 24
1000 102 24
What I need to get to is this without the select (becuase doing it this way
returns an error in my vb.net application because the select wants to return
rows)
select c_driver from Cartons where orderid = @.orderid
if @.@.RowCount = 1
--Only one driver exists for this order
Update orders set oDriver = (select distinct c_driver from cartons
where orderid = @.orderid)
if @.@.RowCount > 1
-- Multiple Driver exists for this Order
"Adam Machanic" wrote:

> What are you querying @.@.ROWCOUNT for? @.@.ROWCOUNT returns the rowcount of
> the last operation... I have a feeling you really want:
> if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> update ...
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
> news:7A9F67DA-3C32-445F-B9AD-8A69F4855292@.microsoft.com...
> some
> only
> result,
>
>
|||I tried the count() but I don't get what I need
what I need to do is determin is if a single driver is assigned to multiple
cartons within an order. for Instance say driver #1 was assigned to Carton
100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of these
cartons are in Order #1000
Order# Carton# Driver#
1000 100 1
1000 101 24
1000 102 24
What I need to get to is this without the select (becuase doing it this way
returns an error in my vb.net application because the select wants to return
rows)
select c_driver from Cartons where orderid = @.orderid
if @.@.RowCount = 1
--Only one driver exists for this order
Update orders set oDriver = (select distinct c_driver from cartons
where orderid = @.orderid)
if @.@.RowCount > 1
-- Multiple Driver exists for this Order
"Paul" wrote:
[vbcol=seagreen]
> I assume you want to update the row if it already exists and insert the row
> if it doesn't exist. If so this should work...
> UPDATE cartons SET
> ...
> WHERE orderid = @.orderid
> IF (@.@.ROWCOUNT = 0)
> BEGIN
> INSERT INTO cartons (...) VALUES (...)
> END
> Hope this helps.
> Paul
> "John Shepherd" wrote:
|||Again, the COUNT(*) should do exactly what you need here... can you tell me
why this won't work for you:
if (select COUNT(*) from cartons where orderid = @.orderid) = 1
BEGIN
Update orders set oDriver = (select distinct c_driver from cartons
where orderid = @.orderid)
END
ELSE
BEGIN
-- do something else...
END
... Given that, however, I should ask why you're updating your orders table
with the driver from the cartons table? Why denormalize your data like
that?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
news:41549C5D-63AA-46DA-92F4-A148A87D1143@.microsoft.com...
> I tried the count() but I don't get what I need
> what I need to do is determin is if a single driver is assigned to
multiple
> cartons within an order. for Instance say driver #1 was assigned to Carton
> 100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of
these
> cartons are in Order #1000
> Order# Carton# Driver#
> 1000 100 1
> 1000 101 24
> 1000 102 24
> What I need to get to is this without the select (becuase doing it this
way
> returns an error in my vb.net application because the select wants to
return[vbcol=seagreen]
> rows)
> select c_driver from Cartons where orderid = @.orderid
> if @.@.RowCount = 1
> --Only one driver exists for this order
> Update orders set oDriver = (select distinct c_driver from cartons
> where orderid = @.orderid)
> if @.@.RowCount > 1
> -- Multiple Driver exists for this Order
>
> "Adam Machanic" wrote:
of[vbcol=seagreen]
message[vbcol=seagreen]
need[vbcol=seagreen]
the[vbcol=seagreen]
becuase[vbcol=seagreen]
|||The problem with count() is that for my example would return 3 rows, I can
only update when all cartons in the orders have the same driver#.
I left out the DISTINCT in my example it should be:
select DISTINCT c_driver from Cartons where orderid = @.orderid
If all drivers are the same for the cartons in the Order I should have 1
row, if there a seperate drivers I should have more than 1 row
The way the db is set up is to have one row for an order in the Orders
Table, ? Rows in Cartons table joined together on Orders.orderid =
Cartons.Orderid based on the #of cartons shipped with the order. All of the
pricing and driver commissions are based on the total $'s charged from the
Orders table. So I need to update the orders table with the correct driver
information, but only if the driver is the same. Little round about I know.
"Adam Machanic" wrote:

> Again, the COUNT(*) should do exactly what you need here... can you tell me
> why this won't work for you:
>
> if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> BEGIN
> Update orders set oDriver = (select distinct c_driver from cartons
> where orderid = @.orderid)
> END
> ELSE
> BEGIN
> -- do something else...
> END
>
> ... Given that, however, I should ask why you're updating your orders table
> with the driver from the cartons table? Why denormalize your data like
> that?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
> news:41549C5D-63AA-46DA-92F4-A148A87D1143@.microsoft.com...
> multiple
> these
> way
> return
> of
> message
> need
> the
> becuase
>
>
|||SELECT COUNT(DISTINCT(DriverNum)) FROM Cartons ?
Paul
"John Shepherd" wrote:
[vbcol=seagreen]
> The problem with count() is that for my example would return 3 rows, I can
> only update when all cartons in the orders have the same driver#.
> I left out the DISTINCT in my example it should be:
> select DISTINCT c_driver from Cartons where orderid = @.orderid
> If all drivers are the same for the cartons in the Order I should have 1
> row, if there a seperate drivers I should have more than 1 row
> The way the db is set up is to have one row for an order in the Orders
> Table, ? Rows in Cartons table joined together on Orders.orderid =
> Cartons.Orderid based on the #of cartons shipped with the order. All of the
> pricing and driver commissions are based on the total $'s charged from the
> Orders table. So I need to update the orders table with the correct driver
> information, but only if the driver is the same. Little round about I know.
>
>
> "Adam Machanic" wrote:
|||Why don't you try COUNT(DISTINCT c_driver) ?
Rather than storing the data in two places (which has data integrity
implications), have you considered creating a view that will return the data
in the way you need it for reporting?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
news:871ECA2C-4846-4442-83FE-53C1657A3159@.microsoft.com...
> The problem with count() is that for my example would return 3 rows, I can
> only update when all cartons in the orders have the same driver#.
> I left out the DISTINCT in my example it should be:
> select DISTINCT c_driver from Cartons where orderid = @.orderid
> If all drivers are the same for the cartons in the Order I should have 1
> row, if there a seperate drivers I should have more than 1 row
> The way the db is set up is to have one row for an order in the Orders
> Table, ? Rows in Cartons table joined together on Orders.orderid =
> Cartons.Orderid based on the #of cartons shipped with the order. All of
the
> pricing and driver commissions are based on the total $'s charged from the
> Orders table. So I need to update the orders table with the correct driver
> information, but only if the driver is the same. Little round about I
know.
>

Tuesday, March 27, 2012

Help with combining two queries

Help with CASE in Stored Procedure

Help with CASE in Stored Procedure

Help with CASE in Stored Procedure

Help with CASE in Stored Procedure

Help with CASE and LIKE

Monday, March 26, 2012

Help with an Update/Insert procedure

Friday, March 23, 2012

Help with a stored procedure/query?

Help with a Stored Procedure..

Help with a stored procedure or DTS package

help with a stored procedure

Help with a stored procedure

Help with a stored procedure

Hi,
Im very new to this posting business so if Im posting in the wrong place or make a faux pa, I apologise

Ive been stuck for a couple of days with the following error, any help would be great!
I created a simple stored procedure which worked fine, then added parameters and it all fell to pieces.

Incorrect syntax near 'StoredProcedure1'.

Help with a store procedure

Wednesday, March 21, 2012

Help with a special stored procedure

Help with a SP

Help with a return value stored procedure that will be used in a calculation

Help with a Query

Monday, March 19, 2012

Help with a hierarchy query or procedure

Monday, March 12, 2012

Help with @@Rowcount

Restore Database

Blog Archive

About Me

Tuesday, March 27, 2012

Monday, March 26, 2012

Friday, March 23, 2012

Hi,Im very new to this posting business so if Im posting in the wrong place or make a faux pa, I apologise Ive been stuck for a couple of days with the following error, any help would be great!I created a simple stored procedure which worked fine, then added parameters and it all fell to pieces.

Incorrect syntax near 'StoredProcedure1'.

Wednesday, March 21, 2012

Monday, March 19, 2012

Monday, March 12, 2012

Blog Archive

About Me

Hi,
Im very new to this posting business so if Im posting in the wrong place or make a faux pa, I apologise

Ive been stuck for a couple of days with the following error, any help would be great!
I created a simple stored procedure which worked fine, then added parameters and it all fell to pieces.