Stored Procedures and Arrays

Question

Stored Procedures and Arrays

Viewing 6 posts - 16 through 20 (of 20 total)

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

Mark Rossiter Old Hand Points: 395 More actions · Answer 1

Many thanks Peter your code has given me the results I need with a very fast return to the webpage.

I should have noticed the variable @cnt never increments, thanks for noticing that, it was a stupid error. As i've mentioned before i'm new to Stored Procedures and converting my VBScript code has been troublesome at times as i'm unsure of the syntax & Stored Procedures not supporting Arrays etc, but what I have noticed are Stored Procedures do give a much more elegant solution requiring less code than VBScript and return faster results to a web page.

Many thanks again for your help, patience & advice Peter it is much appreciated and encouraged me to develop my understanding of Stored Procedures further.

I also liked the article on how many more Mondays till I retire, it helped me understand your Stored Procedure, although now being able to calculate the number of Mondays till I retire is cool, the thought can be a bit depressing but at least its friday tommorrow. Thanks again & keep up the good work.

Cheers

Mark

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 2

You're welcome! Now there is only holidays to take care of as well

Actually, there are so many ways to utilize functions, like mine in the article, to create date sequences. Especially for finding dates NOT present in tables. This is very useful when creating reports as one example.

Beacause the function creates all dates in the desired range, you can join that to other tables such as Holiday table or Vacation table to find all dates between two dates that denotes a time interval.

Or just for fun, as this to get bad luck days from today to the end of this decade

select SeqDate 'Stay indoors! It''s Friday 13''th...'

from dbo.fnSeqDates('6/29/2006', '12/31/2009')

where day(seqdate) = 13

and datepart(dw, seqdate) = 4

N 56°04'39.16"
E 12°55'05.25"

Mark Rossiter Old Hand Points: 395 More actions · Answer 3

Hi Peter,

I'm now trying to pass a parameter to the Stored Procedure from an ASP page, I've changed the stored procedure to accept parameters but its the actual ASP page that seems to cause the problem. Have you any thoughts on this?

Cheers Mark

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 4

The Quick'n'Dirty solution, which is not recommended due to the possibility of SQL injection, is to build your string with VbScript such as

sSQL = "MyStoredProcedure '" & FirstParameter & "', '" & SecondParameter & "'"

Set MyRs = MyCn.Execute(sSQL)

You can achieve the same result using command object, which is recommended.

Also, the Stored Procedure must accept two parameters, such as

CREATE PROCEDURE MyStoredProcedure

(

P1 DATETIME,

P2 VARCHAR

)

N 56°04'39.16"
E 12°55'05.25"

stax68 SSChampion Points: 11711 More actions · Answer 5

To expand on the above, you need to create parameter objects and add them to the parameters collection of the command object, which should be of 'stored procedure' type.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Mark Rossiter Old Hand Points: 395 More actions · Answer 6

Thanks all for advice and comments, i've managed to get the project completed using Stored Procedures. The forums been a great help to me.

Thanks again

Mark