Hi everybody

I am a little confused regarding the code on page 337

    USE Northwind
    DECALRE @Test money
    SELECT @Test = Max(UnitPrice) FROM [Order Details]
    SELECT @Test

Why are there two SELECT statements? I would expect @Test to have a value after the SELECT statement in line 3.

Than you

Ian Hewitt

bc u use @Test = Max(...
why do u use a variable if u dont wanna use that?! its usefull when u dont wanna show the result & then use it somewhere else.
u can do it like:
Always:),
Hovik Melkomian.

Thanks for the quick response.

I was having a bit of a 'dumb' day yesterday and the question I asked was a result of one of my sprocs not working correctly and getting confused while trying to find the answer. The sproc that caused me the trouble is cut and pasted below :-

CREATE PROCEDURE sp_cClub_New
    @Title as text,
    @Region as integer,
    @Charter as Datetime,
    @NewID Integer OUTPUT
AS
    Declare @Uid_Capitation as integer

    SELECT @Uid_Capitation = ClubDefaultCapitation FROM tblOrgRegion WHERE Uid_Region = @Region


    INSERT INTO tblOrgClub (ClubName, Comment, Uid_Region, DateChartered, Uid_Capitation)
        Values (
            @Title,
            '',
            @Region,
            @Charter,
            @Uid_Capitation
        )
    SELECT @NewId = @@IDENTITY
GO

The problem I was geting when running this sproc via ADO from VB6 was an error message basicly telling me that the Uid_Capitation field of table tblOrgClub could not contain a null value. I then modded the sproc by adding a line after the 'SELECT @Uid_Capitation....." line that so that it read :-

    Declare @Uid_Capitation as integer

    SELECT @Uid_Capitation = ClubDefaultCapitation FROM tblOrgRegion WHERE Uid_Region = @Region
        SELECT @Uid_Capitation

And re-ran the viual basic code. This time it worked correctly. I don't actauly understand why I got the original error message as @Uid_Capitation should have had a valid value by the time the INSERT statement was processed. I am quite sure that the @Region input parameter had a valid value and it should have returned a value of 6. The seconf time I ran the sproc (with the second SELECT statement) and @Uid_Capitation did have the value of 6.

Any thoughts ?

Ian Hewitt

u can use select and insert into in a single go
insert into x values(select * from y)
or specify the col name that are common
for rest cols u can supply direct values