Prepared Statements (SQL Server)

[Anakim]

Has anyone got an example of using PREPARED SQL in conjunction with cursors? I've used them before with Informix but I don't know what the syntax is for SQL Server - I've found loads of references on Microsite site but no actual examples.

I do not know the syntax for PREPAREing SQL, and neither do I know the syntax for FETCHing a row while passing in the parameter.

I want to prepare a cursor where the join is based on values determined at run time. Something like....

PREPARE sSQL as 
   SELECT title, cat_no FROM titles t, order o
     WHERE o.order_no = ?

DECLARE cur1 CURSOR FOR sSQL
...

FETCH cur1 USING @sOrdNo 
  INTO @sTitle, @sCatNo

Thanking you in advance....

[Anakim]

Tis okay yawl.

No need for shouts of "divvy" (proper SQL peeps) or cries of
"What the fick's he on about?" (VvB SQLers.. ..Visual 'very' BASIC SQLers). I've sussed it out and is back on to me merry crusade.

Ta-ra chucks

[paulw]

Well tell us then

I know how to do it, but not everyone might...

Paul.

[Anakim]

I lied.

Syntax I'd got was for execution through ODBC. So how'd you do it then?

I'm now working round it by looping through with an outer cursor which is open/closed 20 or so times. After each row is fetched I'm building another SQL Cursor based on each row's results. This inner sqwelly goes off and does it's thing.

Not nice but it works.

Basically what I want to do is PREPARE two lots of SQL with ? placeholders. One of them will be an outer cursor which fetch results into variable which in turn are used within the 2nd SQL. If I could prep the sqwelly up fromt it'd be a little quicker and muchos easier to read.

[paulw]

I do it from the client side - something like

cmd.CommandText = "SELECT Titles FROM Books WHERE title_id=?"
cmd.CommandType = adCmdText
cmd.Prepared = True
set prm = cmd.CreateParameter("title_id", adChar, adParamInput, 6, "AB1234")
cmd.Parameters.Append prm
cmd.Execute
cmd("title_id") = "BC4567"
cmd.Execute
etc.

cmd is a Command object and prm is a Parameter object

Well?

Cheers,

Paul.

[Anakim]

Uh oh, The head honcho at KISS (Keep it server side) has just done his nut, and the under secretary at Sqwelly-coders National Organisation of Guru's is also quivering wildly.

See, what they knew - and I'd omitted to mention - was that all this gubbins is all being done from within a SPROC.

(We like doing things that way in these murkey backwaters, sorry floodwaters, cuz we don't have to go round installing recompiled apps if the queries/dbs change).

Appleogres for not mentioning that sooner, must have been the way the display refracts through the subsiding waters of the River Office, cuz I thought I'd mentioned SPROC previously. But no, so my mandatory subscription to KISS has just gone up by a tad bit more than what petrol has come down by.

[paulw]

Well, I could have worked it out - it was implicit in your post, but that was much too hard!

Cheers,

Paul.