CODEWITHSUNDEEP
sql-servert-sql
cihadakt
cihadakt

Reputation: 3214

SQL Server : getting "invalid column name"

I am working on writing a query but there are problems which I couldn't find. Here is my code

begin
    declare @v_max int
    declare @v_count int
    declare @sessionID int
    declare @sessionStart datetime
    declare @sessionEnd datetime

    declare my_cursor cursor local for
        select * from Test;

    open my_cursor
    fetch next from my_cursor INTO @sessionID, @sessionStart, @sessionEnd

    while @@FETCH_STATUS = 0
    begin
        select *  into **@v_count**
        from [dbo].[Test]
        WHERE **[dbo].[Test].[SessionStartTime]** > @sessionStart
        OR **[dbo].[Test].[SessionCloseTime]** < @sessionEnd

        if @v_count > @v_max 
            set @v_max = @v_count


        fetch next from my_cursor INTO @sessionID, @sessionStart, @sessionEnd
    end

    print @v_max;

    close my_cursor
    deallocate my_cursor
end

Bolded areas have problems:

Msg 207, Level 16, State 1, Line 18
Invalid column name 'SessionStartTime'.
Msg 207, Level 16, State 1, Line 19
Invalid column name 'SessionCloseTime'.
Msg 102, Level 15, State 1, Line 16
Incorrect syntax near '@v_count'.

Here is my Table

CREATE TABLE [dbo].[Test]( 
    [ScenarioID] [bigint] NULL, 
    [SessionStartTime] [datetime] NOT NULL, 
    [SessionCloseTime] [datetime] NULL 
) ON [PRIMARY]

Here is my Table

CREATE TABLE [dbo].[Test](
    [ScenarioID] [bigint] NULL,
    [SessionStartTime] [datetime] NOT NULL,
    [SessionCloseTime] [datetime] NULL
) ON [PRIMARY]

GO

Upvotes: 1

Views: 1539

Answers (2)

podiluska
podiluska

Reputation: 51494

Select into creates a table.

You may want

select @v_count = count(*)
from [dbo].[Test] 
...

You also need to initalise @v_max at the start of your routine.

set @v_max = 0

As for your other problem, do you have columns called SessionStartTime and SessionCloseTime in your Test table?

Upvotes: 3

user1758851
user1758851

Reputation: 111

@v_count is declared as an int in the beginning of your query and not as a table. You can not use SELECT * INTO @v_count there.

Upvotes: 5

Related Questions