You are soon to be my hero. I am going to be getting into my Sql Server chapter in an ADO.NET book soon.

Wally

Least favorite parts:
* Can't specify a parameter to be used as an IN clause
* Can't easily specify parameters to be used in the ORDER BY clause; especially painful to do multiple order by clauses

Of course they're both trivial to do with dynamic SQL but that's unacceptable to do from a security perspective.

Please tell me this all gets fixed in Yukon?

I have few questions for a good start... ;)
1. Please tell me why did you create this awful syntax for cursor fetch operation?
Why do I have to list all the variables in a cursor fetch twice?
I know it's probably because of a while syntax, but if Oracle could create a special "for" cursor loop, why couldn't you? :(
2. Why there are no row type variables?
3. Why error handling is so craizy?
4. Why there are no packages? Why do I have to use naming conventions to group procedures of similar functionality or purpose?

Well, for my defence I have to say I've started my development history on Oracle and that programmability of Oracle is mutch better that Sql's 2k.
I'm an MS addict, but t-sql always amazes me... Who designed this language?! :(

It is actually possible to use a paramater as an in clause. What you have to do is turn the paramater into a temporary table or some other relational format. Either send in xml or some other delimited format as the paramater for the stored proc. Turn into a temporary table and then use the temporary table as a subquery. Alternatively use a subquery based upon the paramater that returns a relational format.

IE

Create Procedure FOO(@UserList as varchar(2000))
AS
SELECT * from tableLinkedToUsers where userid in (SELECT userid from users where @x like '<' + users.username + '>')
GO

EXEC FOO '<John>,<David>,<Kelly>'
GO

This will return all the rows from the tableLinkedToUsers where userid is for John,David and Kelly.

To bysza: the short answer is: Sybase did. We are trying to improve the design, but backward-compatibility is a big blocking factor here, considering a huge installed base.

This posting is provided "AS IS" with no warranties, and confers no rights.

It was a rhethorical question ;) I'm fully aware of the history of sql server, but what I don't understand is a rate of enhancements to the language. You have rewritten almost everything in v7. After that we got v2k.
During AFAIR TechEd 2002, as a key client, I was able to talk to Peter Conlan and two other guys from sql team, and tryed to convince them to change locking and exception management. They were planning to introduce "On error resume next" in Yukon and leave locking intact... :( Thankfully we managed to force them to reconsider their approach :)
Concerning loops, cursors and error handling, is this that hard to change a syntax oriented things to at least ease development?
I just don't understand why we have to wait for v2k5 to get a decent error handling in tsql and different locking mechanism?
Well, still no packages... :(
Don't get me wrong. I'd like sql server to be the best relational engine available :)

I've using the XML document in TSQL to insert multiple selected values into a table with out parse them. Because values can be inserted into a tmp table I can use this table to filter my results in a natural join like an "IN". Maybe is a bazooka to kill a little bird but it works and performance is not bad. Anyway here is the code. It uses Northwind DB

This posting is provided "AS IS" with no warranties, and confers no rights
-- carlos90210@msn.com


-- Call the SP
EXEC sp_select_ReturnProducts
'<ROOT> <XMLAuto intID=''18'' /> <XMLAuto intID=''15'' /> <XMLAuto intID=''20'' />
</ROOT>'


-- The SP

ALTER PROCEDURE sp_select_ReturnProducts
(@strXML as varchar(2000)) AS
SET NOCOUNT ON

DECLARE @hDoc int

--Creates a tmp table
CREATE TABLE #tblID (intID int)

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @hDoc OUTPUT, @strXML

-- Populate table
INSERT #tblID
SELECT *
FROM OPENXML(@hDoc, '/ROOT/XMLAuto') --Table inside XML
WITH #tblID

--Main SELECT
SELECT
ProductID, ProductName
FROM
Products
JOIN
#tblID
ON
ProductID = intID

-- Remove the internal representation.
exec sp_xml_removedocument @hdoc
drop table #tblID


SET NOCOUNT OFF

PingBack from http://feeds.maxblog.eu/item_713475.html

PingBack from http://onlineslotmachinegame.globecontent.com/2008/09/20/vegan-kayaker-delaware-state-park-ohio/