Showing posts with label customer. Show all posts
Showing posts with label customer. Show all posts

Monday, March 12, 2012

Help with a "select"

All of my customers are in a single data file, with one record per customer for each year that the customer bought something. (If customer A bought something in 1993, he would have a customer record in the file for that year. If he did not buy something in 2004 he would have no customer record for 2004 in the file.)

I want to "select" customer records from the file for which there is a 1993 record, but no 2004 record.

What would the select syntax be like?

Thanks in advance.Roughly:

Select * from Customers where Year(Fieldname) = '1993' and Customers.Cust_ID not in(select Cust_ID from Customers where Year(Fieldname) = '2004')

Sunday, February 26, 2012

Help stored procedures

Hello ,

I am in a state of conflict with my c# asp.net web app. Basically i have a stored procedure that updates my customer table with the param supplied, and generates the primary key incrementally. My problem is returning the pk to my asp.net.

Procedure looks like so:

ALTER Procedure newUser (
/* Param List */

@.fname varchar(20),
@.lname varchar(20),
@.address1 varchar(20),
@.address2 varchar(20),
@.city varchar(20),
@.province varchar(20),
@.postalCode varchar(7),
@.country varchar(20),
@.phone varchar(10),
@.email varchar(30),
@.receiveNews bit,
@.archive bit,
@.business varchar(10),
@.fax varchar(10)
)
AS

BEGIN TRANSACTION
INSERT INTO customer (
fname,
lname,
address1,
address2,
city,
province,
postalCode,
country,
phone,
email,
receiveNews,
archive,
business,
fax
)
VALUES (
@.fname,
@.lname,
@.address1,
@.address2,
@.city,
@.province,
@.postalCode,
@.country,
@.phone,
@.email,
@.receiveNews,
@.archive,
@.business,
@.fax
)
COMMIT
RETURN

The syntax has been verified correct and does populate my customers table with valid data.

the column that i wish to return is called "pk_customerId".
I tried using "RETURN pk_customerId" but it gives me an error that the column does not exist even thogh it does.

Any ideas.

Thanks in advancein short, you can do this:

return (scope_identity())

but i'd declare a @.variable and an @.error before your begin tran, then right before your commit do this:

select @.error=@.@.error, @.variable=scope_identity()
if @.error != 0 begin
raiserror ('jkshdk fskjhdf kjshdf jkshdfh', 10, 1)
rollback tran
end
commit
return @.variable