Really basic question about the Select statement in T-SQL

  • I'm a newbie at writing T-SQL (2005), having been a C# programmer for 5 years. I keep getting confused on the same point. I work alone and have no one to turn to for help.

    My confusion is this: I am accustomed to (as a programmer) to getting a set of records returned from a select statement in my C# data tier code. However I have a suspicion that in a while loop executing in a stored procedure in T-SQL I do not get a set or records, but rather a single row. So that confuses me.

    Secondly, what about the same situation as I describe above, but the select statement also contains an order by clause. How would that affect, if at all, what gets returned to me.

    Anyone help a newcomer? Thanks.

    Victor Victor

  • You'd have to post the code to get help on the WHILE loop. In general, any execution that includes a SELECT or PRINT will return something to the client. Other code just runs on the server. The SELECT or PRINT must be its own statement.

    So

    while exists (select * from mytable)

    begin

    set rowcount 1

    delete MyTable

    set rowcount 0

    end

    doesn't return anything. But this does.

    while @i < 10

    begin

    select @i

    set @i = @i + 1

    end

    Assuming @i starts at less than 10.

    Rows have no ordering in SQL Server. You cannot assume if I insert in order (1, 2, 4) in a column that they will get returned in that order. Indexing can affect things, but the point is there is no guaranteed order unless you use an ORDER BY clause.

    However that causes sorting to occur and uses resources, so you shouldn't use it unless you really need it.

  • Steve: Thank you so much for responding. I had hoped to avoid posting a lot of code but I can see I did not fully present the complete confusion.

    I do understand both while loops that you presented.

    I am working my way thru a book: Sams Teach Yourself Microsoft SQL Server 2005 Express in 24 hours (yeah, right!). I’m using the Northwind database and using with XP/SP3 and the Developer version of SQL Server 2005, not the Express version. I'm tackling the Workshop Activity on page 222, which presents a practice problem:

    "Build a stored procedure that creates a temporary table containing OrderID, CustomerID, OrderDate, and Freight. Execute a second stored procedure that selects those fields from the Orders Table, ordering by CustomerID and OrderDate. The first stored procedure should pass the second stored procedure a parameter for the freight amount. The second stored procedure receives the parameter for the freight amount. It selects all fields from the Orders table where freight is greater than the parameter value, ordering by CustomerID and Freight. It returns the result into the temporary table in the first stored procedure. Finally, have the first stored procedure display the contents of the temporary table.

    The book does not offer a suggested solution to this. Below appears my code, which BTW gives me the following error: “Conversion failed when converting the nvarchar value ‘RICSU’ to data type int. [stored proc2 - line 13]. This puzzles me because I am not using a variable RICSU nor am I using anything declared as nvarchar. There must be some hidden code that I am not seeing.

    But, more importantly, my original question still haunts me and its this:

    In stored procedure1 below line 14 asks for the table to be fully populated (with more than just one record) from the execution of stored proc2. On the other hand, line 20 of stored procedure 2 asks for variables to be filled from just one record. And even more confusing is the fact that the insert statement of line 23-24 processes by adding one record at a time. So, I’m confused: How can processing be in terms of a set of rows (more than one), on one hand and on the other hand, later processing be one row at a time.

    Thanks for your help in this. I’m really confused and until I can explain to myself how this works, further study won’t be too effective.

    1 create procedure [dbo].[storedprocedure1]

    2 @freightcutoffvalue money

    3 as

    4 set nocount on

    5 begin

    6 create table #temptable1

    7(

    8orderid int not null primary key,

    9customerid int,

    10orderdate datetime not null,

    11freight money not null

    12)

    13insert into #temptable1 (orderid, customerid, orderdate, freight)

    14exec storedprocedure2 '@freightcutoffvalue'

    15select * from #temptable1

    16 end

    -------------------------------------------------------------------------------------

    1 create procedure [dbo].[storedprocedure2]

    2@freightcutoffvalue money

    3 as

    4 set nocount on

    5 begin

    6declare @orderid int

    7declare @customerid int

    8declare @orderdate datetime

    9declare @freight money

    10declare @rowcount int

    11declare @currentrow int; set @currentrow = 1

    12select @orderid = orderid,

    13@customerid = customerid,

    14@orderdate =orderdate,

    15@freight =freight

    16from orders

    17where freight >= @freightcutoffvalue

    18order by customerid, orderdate

    19set @rowcount = @@RowCount

    20select @orderid, @customerid, @orderdate, @freight from #temptable1

    21while @currentrow <= @rowcount

    22begin

    23insert into #temptable1 (orderid, customerid, orderdate, freight)

    24Values (@orderid, @customerid, @orderdate, @freight)

    25select @currentrow = @currentrow + 1

    26end

    27 end

  • kayuca (7/23/2008)


    Below appears my code, which BTW gives me the following error: “Conversion failed when converting the nvarchar value ‘RICSU’ to data type int. [stored proc2 - line 13]. This puzzles me because I am not using a variable RICSU nor am I using anything declared as nvarchar. There must be some hidden code that I am not seeing.

    This error is caused because you define customerid as int in your temporary table and customerid in Northwind is an NVARCHAR() column. Thus trying to convert the customerid, 'RICSU', to an integer fails.

    In your second procedure all you need to do is a single select. Like this:

    [font="Courier New"]SELECT

        orderid,

        customerid,

        orderdate,

        freight

    FROM

        orders

    WHERE

        freight >= @freightcutoffvalue[/font]

    You are making a common mistake to those new to SQL, you are not thinking in sets. The SQL language works on sets of data not single rows. SQL programmers rarely use loops. The select I list is all you need in the second stored procedure as this code:

    insert into #temptable1 (orderid, customerid, orderdate, freight)

    exec storedprocedure2 @freightcutoffvalue

    Does the insert into the temp table. You also should note that I removed the single quotes that were around the parameter as those change it from a parameter to a literal string which would make you select this:

    [font="Courier New"]SELECT

        orderid,

        customerid,

        orderdate,

        freight

    FROM

        orders

    WHERE

        freight >= '@freightcutoffvalue'

    [/font]

    This is also a contrived situation as you would just do this in one procedure in real life.

  • Jack:

    Thanks so much for the help. I won't make that nchar to int mistake again. (The learning process is sooo drawn out in the beginning.) I've printed off your remarks and will study them re: thinking in terms of sets vs rows.

    Thanks again.

    Victor Victor

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply