Right, Jagged, GOT IT
Changed line 18, 'MAX' to 'MIN', and of course, line 23 as previously stated in my previous post and it now outputs the data the way needed.
Many thanks Jagged,
Davinski...
davinski
Last Activity: Feb 23 '08, 02:25 PM
Joined: Mar 26 '07
Location: valencia
-
The only part where I think you mean is line 23 in the original post
So I changed it toCode:23 SET @CID = (SELECT MAX(ID) FROM CP_FORUM_Comments WHERE ParentID = @Root AND ID < @CID and ArticleId = @ArticleId)
...Code:23 SET @CID = (SELECT MIN(ID) FROM CP_FORUM_Comments WHERE ParentID = @Root AND ID > @CID and ArticleId = @ArticleId)
Leave a comment:
-
Thanks your response Jagged,
I can't see where you mean to change the code, which line do you mean exactly please, I'm a real noob with nested stored procs and temp tables.
Thanks
Davinski...Leave a comment:
-
stumped with stored proc and temp tables
Hello, it's been a while since my last post, hope everyone is fine :P
I'm stuck with what seems to be a simple task, but I'm getting confused on how to complete this. Basically, I have been given a stored procedure which nests itself within itself and uses a temporary table to store the data while writing.
The nested stored procedure is used so that it can output the data rows in a db table into an organized tree like... -
Yay, finally, this is the solution, the one below is what I've been looking for and fulfills all the conditions required.
Many thanks for all your 'Thinking' whether it was hard for you or not; it would have definately been hard for me!
Regards
Davinski...Leave a comment:
-
Yes I am sorry that I did not try your other solution before requesting another one however I am simply trying to cover all bases and as we all are able to use our experience from similar problems to solves new ones. And I believe I made a good judgement call with the Codesmith and Nettiers constraint on the problem I have so given forth.
I'm sure you didn't make anything up or guess your solution, but I am now confident that your...Leave a comment:
-
Thanks for the work around, I'm yet to test it with CodeSmith and nettiers but I think it might cause a problem :S not sure.
Is there another work around or is the problem caused by my SQL server not being up to date?
I'm using MSSQL 2005 but it might not be patched and up to date.
Many thanks for any insight or other work arounds.
Davinski...Leave a comment:
-
Wow, do you ever sleep? you're like a machine!!!
Just one last anomaly I think, and that is when I use your template on a column in the table that is declared as a bool, bActive and in the stored procedure I take a variable defined as a bit, @bActive (the reasoning for it being a bit definition is to do with nettiers), when I run my stored proc, for null values and value of 1 I get the disired results, however passing the @bActive...Leave a comment:
-
iburyak,
Yes I've tried your previous suggestion with absolute success :D
I just don't understand the cryptic message when I don't use the CAST
I think that this message does not relate to this line does it?Code:Conversion failed when converting the varchar value '%' to data type int.
Thanks for all of your helpCode:then '%'
...Leave a comment:
-
Hey, that last example works great, amazing!
Can you help me find out what the problem is with this snippet of your template?
When I run the stored proc and @nAge is NULL I get this errorCode:nAge like case when isnull(@nAge,'') = '' then '%' else @nAge end
...Code:Conversion failed when converting the varchar value '%'
Leave a comment:
-
iburyak, many thanks, I can see how your cast will work however I can't test it right now because I've created another error :S btw the error before was something along the lines of
I'll let you know how I get on with your CAST, however one other little modification to your template if you could as that is if I wanted to test...Code:Conversion failed when converting the varchar value '%' to data type int.
Leave a comment:
-
Right you are iburyak!!!
using like '%' on int and encrypted bits works just fine and it's great, thank you.
I have however stumbled upon a small problem with your template that maybe you can solve, that is in using your template I tried to do the following, nAge is of int definition.
...Code:nAge like case when isnull(@nAge,'') = '' then '%' else @nAge end
Leave a comment:
-
Thank you so much for the explanation, it was enlightening and you do truely know what you are doing!
In light to understand your methods more completely, I also created a Test Database with 1 table.
What I found was that the 'like' comparison doesn't hold true for int, or should I say that when the column definition is an 'int' and the variable is passed through as null, the int column definition gets compared with...Leave a comment:
-
Hello,
Thanks for the prompt reply and answer. Good news is that when I use your syntax template in my stored proc I don't get any error messages, only thing is that I can't test it because I don't have any data in my table yet :S
I've been taking another look at Books Online to make sense of your syntax however I get a little confused.
I think I understand
... where column1 like (uses...Leave a comment:
-
stuck again, if else after where clause
So thanks to another member here I got my view sorted out :D but now that I've begun to write my stored proc I've ran into another problem!
Basically, my stored proc takes about 30 variables and I want to build a WHERE CLAUSE constraint upon these variables. Only thing is, I would like to check the variable values before adding them to the WHERE clause and if the value of the variable is null or "" then I would like to... -
Dude,
It works!!!
I just put an 'AS' in too like
...,NumberOfDou bleBedrooms + NumberOfSingleB edrooms AS NumberOfBedroom sTotal
Thanks for your help!
Davinski...Leave a comment:
-
aggregation or what?
hello techies,
Got myself a little stuck here.
I would like to be able to have in a VIEW a column which is not defined in any table to show the total of the row from another 2 columns which are defined in the table.
For example, if I have a table called Property with 3 columns, PropertyID, NumberOfDoubleB edrooms, NumberOfSingleB edrooms, int, int, int respectively.
I would like in my View...
No activity results to display
Leave a comment: