Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Malby
Starting Member
10 Posts |
 Posted - 2004-03-16 : 00:51:40
|
| I have this:DECLARE @Ids TABLE (Id INT PRIMARY KEY, Ordering SMALLINT)INSERT INTO @Ids SELECT Id, OrderBy FROM OtherTableCan I Pass the @Ids variable to a UDF and have it get the first record for me? ie:SET @Counter = 10SET @CurrentId = TablePop(@Ids)-- just saves me typing (SELECT TOP 1 Id FROM @Ids ORDER BY O)WHILE (@CurrentId IS NOT NULL)BEGIN UPDATE Content SET OrderBy = @Counter WHERE Id = @CurrentId DELETE FROM @Ids WHERE Id = @CurrentId SET @CurrentId = TablePop(@Ids) SET @Counter = @Counter + 10ENDor is it impossible and I should just stick with the duplication?----------------------"An eye for an eye will leave the whole world blind" - Gandhi |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-03-16 : 01:58:18
|
| No you can't pass a table variable as an input parameter to a function. My initial reaction was to suggest using a global temp table, but as it turns out you can't even access a temp table from a UDF. I guess it's back to a bit of duplication. But what are you trying to do in the proc, perhaps there is a simpler way of doing it? If you can post the whole procedure, we should be able to suggest a few ideas.OS |
 |
|
|
Malby
Starting Member
10 Posts |
Posted - 2004-03-16 : 18:50:53
|
Sure, here you go.It really doesn't need the change, it was more of a "I wonder if..."ALTER PROCEDURE ReorderContent @PageId INTAS SET NOCOUNT ON -- Get List Of Ids We Want to Edit. Sorted By OrderBY -- Add Them To a Temporary Memory Table -- Update Each Record in the Original Table with Incremented Counter (+= 10) -- DELETE FROM Temp Table DECLARE @Counter INT DECLARE @CurrentId INT DECLARE @Ids TABLE (Id INT PRIMARY KEY, O SMALLINT) INSERT INTO @Ids SELECT Id, OrderBy FROM Content WHERE PageId = @PageId ORDER BY OrderBy SET @Counter = 10 SET @CurrentId = (SELECT TOP 1 Id FROM @Ids ORDER BY O) WHILE (@CurrentId IS NOT NULL) BEGIN UPDATE Content SET OrderBy = @Counter WHERE Id = @CurrentId DELETE FROM @Ids WHERE Id = @CurrentId SET @CurrentId = (SELECT TOP 1 Id FROM @Ids ORDER BY O) SET @Counter = @Counter + 10 END RETURN @Counter ----------------------"An eye for an eye will leave the whole world blind" - Ghandi |
|
|
|
Malby
Starting Member
10 Posts |
Posted - 2004-03-16 : 18:53:05
|
| My initial proc involved a cursor, because it was the only way I knew of looping through a set of records.... Then I decided to walk away from the computer and get a coffee. while the kettle was boiling I had an "AHAH" moment.This seems to work well, its cleaner than the damned cursor version...Thoughts?----------------------"An eye for an eye will leave the whole world blind" - Gandhi |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-03-17 : 00:59:22
|
Oh a WHILE loop is certainly better than a CURSOR, anytime! But SQL is built for set-based processing and even though sometimes a set-based solution is not obvious, it is often worth the effort of finding one. Here's a set-based version of your proc:ALTER PROCEDURE ReorderContent @PageId INTASBEGIN SET NOCOUNT ON -- Get List Of Ids We Want to Edit. Sorted By OrderBY -- Add Them To a Temporary Memory Table -- Update Each Record in the Original Table with Incremented Counter (+= 10) -- DELETE FROM Temp Table DECLARE @Ids TABLE (Id INT PRIMARY KEY, O SMALLINT, Counter INT IDENTITY(10,10)) INSERT INTO @Ids(Id, O) SELECT Id, OrderBy FROM Content WHERE PageId = @PageId UPDATE Content SET OrderBy = Counter FROM Content INNER JOIN @Ids ON PageID = Id AND OrderBy = O END btw, "Gandhi" is the right spelling  OS |
|
|
|
Malby
Starting Member
10 Posts |
Posted - 2004-03-17 : 01:06:06
|
| Great stuff. Thats heaps simpler than my version. The things you learn!Thanks again, and thanks for the spelling mistake pickup!----------------------"An eye for an eye will leave the whole world blind" - Gandhi |
|
|
|
|
|
|
|
|
Can you pass a TABLE to a UDF as an argument?