July 23, 2008 at 7:59 am
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
July 23, 2008 at 8:14 am
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.
July 23, 2008 at 9:42 am
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
July 23, 2008 at 10:02 am
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 Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 23, 2008 at 10:21 am
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