SQL text together with the parameters it refers to. Build one from a string literal, with mkQuery, or from pieces joined with <>; Hmssql.Query shows each way.

The positional parameters are sent as @p1, @p2, ... in the order queryParams lists them, and the named parameters by name. <> appends the text and the parameters of two queries and renumbers the right operand's placeholders past the left operand's positional parameters, so any two queries can be joined. A placeholder that refers to no parameter makes sending the query throw EncodeError.

A query with no parameters is sent as a plain SQL batch, and one with parameters through sp_executesql. fromString (OverloadedStrings) is text as it is, with no parameters. Show is the text as it would be sent, without the parameter values.

SQL text with its parameters. The parameters are those of the ToSqlRow value: a tuple, a record with a derived instance, an Only for a single parameter, a list of SqlParam, or NamedParams. In the text, @p1, @p2, ... refer to the positional parameters and @name to the named ones.

mkQuery "UPDATE dbo.customer SET email = @p1 WHERE id = @p2" (email, customerId)

@pN, in either case, is a placeholder wherever it appears outside a string literal, a quoted identifier or a comment, even where the SQL means something else by it. So a variable of that name, as in DECLARE @p1 int, can't be used in a mkQuery text; use another name. A placeholder that refers to no parameter makes sending the query throw EncodeError.

A string literal (OverloadedStrings) is the same as mkQuery text () for text without placeholders.

The text is lexed once per application of mkQuery to it, not once per set of parameter values. With a literal, the lexing floats out to the top level when mkQuery is inlined, so the text is lexed once per program run however many times the query is built with new parameters.

Text spliced into the query as it is, with no parameters; a string literal gives the same. Nothing in it is parsed, so a @p1 in it is left to the server. For text that comes from data, use param, namedParam or escapeIdentifier instead.

A positional parameter: a placeholder in the text, and the value sent with the query.

"WHERE id = " <> param (42 :: Int)

A named parameter: @name in the text, and the value sent with the query. The leading @ is optional. The name must be a T-SQL variable name (see paramNameProblem), and not of the form @pN, which is reserved for the positional parameters. A query that uses one name for two parameters can't be sent, and throws EncodeError.

"OFFSET " <> namedParam "skip" (100 :: Int) <> " ROWS"

An identifier (a table or column name from data, say) quoted with brackets: [name], with ] doubled.

"SELECT " <> escapeIdentifier col <> " FROM " <> escapeIdentifier table

The SQL text as it is sent, with @pN for the placeholders.

A placeholder next to a character that could continue a variable name gets a space between them: "x = " <> param 1 <> "1" is sent as x = @p1 1, not x = @p11 (another parameter), and "N" <> param s as N @p1, not the identifier N@p1.

Run a statement, or a batch of them, and return the number of rows affected, which is the sum of the counts the server reports. Any result sets are read and discarded.

Run a statement as execute does, and discard the row count. The whole response is still read, and a server error is still thrown.

The rows of the first result set, decoded into the row type by its FromSqlRow instance, with the column types checked before the first row. A query with no result set, such as an UPDATE, gives []. The rest of the response is read to its end, so a later result set is discarded, and an error from a later statement is still thrown.

query for exactly one row. It throws UnexpectedResult if the first result set has no rows or several.

query1 with the given row decoder instead of the row type's FromSqlRow instance. Throws UnexpectedResult unless the first result set has exactly one row.

query for at most one row. It returns Nothing if the first result set has no rows, and throws UnexpectedResult if it has several.

queryMay with the given row decoder instead of the row type's FromSqlRow instance. Throws UnexpectedResult if the first result set has more than one row.

query with the given row decoder instead of the row type's FromSqlRow instance.

query with a RowDecoderMonadic instead of a RowDecoder: the decoder runs per row, so it can look at the columns (remainingColumns) and decode a shape only known at runtime.

Fold over the rows of the first result set as they arrive, without building a list or a stream: the fastest way to count, sum or write rows out. The step runs with the connection lock held, in the middle of reading the response, so it must not use the connection. On a MARS connection it may, because the request takes a session of its own. An exception from the step cancels the response at once and propagates. If it is the step's own exception, the response is drained before it propagates; if it is an interruption, the drain is left to the next request. After the fold, the rest of the response is read to its end, as with query.

queryFold with the given row decoder instead of the row type's FromSqlRow instance.

The rows of the first result set as a Stream, read from the socket as they are consumed, so memory doesn't grow with the result. Hmssql.Query shows how to consume one.

Read the stream to its end before making another request on the connection. The next request cancels the statement and discards what was left, and the old stream then throws ResultAbandoned. So does a stream whose read was interrupted, by timeout say, which cancels the statement at once, and a stream run a second time after it was drained.

A stream not read to its end leaves its statement running on the server, holding its locks, until the next request, cancelActiveStatement or release cancels it. On a MARS connection (connectMars) other requests leave the stream open instead, so it stays open until it is read to its end, canceled with cancelActiveStatement, or the connection is released. withQueryS is the form that can't leak a stream.

Canceling a statement that is still running inside a transaction under SET XACT_ABORT ON rolls the transaction back (TransactionEndedEarly). Where the transaction must survive, read the stream to its end.

queryS with the given row decoder instead of the row type's FromSqlRow instance.

queryS with a RowDecoderMonadic instead of a RowDecoder: the decoder runs per row, so it can look at the columns (remainingColumns) and decode a shape only known at runtime.

queryS in a scope. The action gets the stream, and when the action ends, a stream not read to its end is canceled (cancelResponse). When the action returns, or throws an exception of its own, withQueryS returns once the statement is over on the server and its locks are released. On a MARS connection (connectMars) this is the way to read a stream beside other requests. Without MARS it works too, under the rule of queryS: a request the action makes abandons the stream.

withQueryS conn "SELECT id FROM dbo.parent" $ \parents ->
  S.mapM_ (\(Only pid) -> query conn ("SELECT ... WHERE parent_id = " <> param (pid :: Int))) parents

An interruption (timeout, killThread) doesn't wait. The statement is canceled at once, and the connection's next request reads the rest of the response first; a transaction function, release and cancelActiveStatement do the same. Until then the statement's locks may still be held, and cancelActiveStatement waits for them to go. The stream must not be used after the action.

withQueryS with the given row decoder instead of the row type's FromSqlRow instance.

withQueryS with a RowDecoderMonadic instead of a RowDecoder, as querySMWith.

One column's value in a row being read, followed by r, the rest of the row. skipCell, cellText and cellDecoded are the usual ways to read one.

A cell's value and what follows it. A NULL gives the rest of the row at once. A non-NULL value has its total length, if known, and its bytes, and gives the rest of the row when they end, so a row's cells can only be read in order.

One row's cells, in column order. Its result is the stream of the rows after it.

The columns of the first result set, and its rows as nested streams: rows of cells, and cells of bytes. A value is read as it arrives, so a large one needn't be in memory. For ordinary rows, queryS is faster.

The rules of queryS apply: a new request abandons the stream on a plain connection, and on MARS it doesn't. Reading any part of the stream a second time, or using it after its scope ends, throws ResultAbandoned.

queryCells in a scope: when the action ends, a stream not read to its end is canceled, with the same interruption rules as withQueryS.

Decode a cell with any field decoder, and get the rest of the row. A failed conversion is a Left once the value has been read past, so the row can go on.

A character or XML cell's value as Text chunks, read as it arrives, returning the rest of the row. A NULL gives no chunks. A column whose encoding can't be decoded throws DecodeError.

Discard a cell's value and get the rest of the row.

How to decode a response that holds several result sets, one at a time in order, for queryResultSets. Build one from resultSet and skipResultSet with the Applicative or Monad combinators; as a Monad, a later decoder may depend on earlier rows. Statements that produce no result set, such as DECLARE, SET and UPDATE, are skipped over.

(parents, children) <- queryResultSets ((,) <$> resultSet <*> resultSet) conn
  "SELECT id, name FROM dbo.parent; SELECT id, parent_id FROM dbo.child"

The rows of the next result set, as query would return them. It throws UnexpectedResult if the response has no further result set.

resultSet with the given row decoder instead of the row type's FromSqlRow instance.

resultSet with a RowDecoderMonadic, which runs per row and can look at the columns (remainingColumns), and the result set's columns returned with its rows.

Read the next result set and discard its rows. It throws UnexpectedResult if the response has no further result set.

Run a batch that returns several result sets, and decode them one at a time, each into its own row type.

(parents, children) <- queryResultSets ((,) <$> resultSet <*> resultSet) conn
  "SELECT id, name FROM dbo.parent; SELECT id, parent_id FROM dbo.child"

The whole response is read, so an error in a later statement still throws SqlServerError, and result sets the ResultSets didn't ask for are discarded.

One result set of a response: its columns and its rows.

Every result set in the response, in order, each row as a SqlRow (sqlRowDecoderFor the set's columns): for a batch or procedure whose columns are only known at runtime. For result sets of known types, use queryResultSets.

Every result set decoded with the given row decoder. Each set's column count and types are checked before reading its rows, including empty sets. Use queryResultSets when the sets have different known shapes.

queryMulti with a RowDecoderMonadic instead of SqlRow: every result set in the response, each decoded with the same decoder, which runs per row and can look at the columns (remainingColumns) to adapt to each set.

Call a stored procedure (or scalar function, or system procedure such as sp_executesql) by RPC, with the parameters in the given order. See ProcResult for how output values are ordered. Like the other query functions, it throws SqlServerError if the server reports an error, even one the procedure continues past; output values are then not available. Parameters are encoded with the connection's context and validated before sending; an encoding failure throws EncodeError.

res <- callProcedure conn "dbo.place_order"
  [ inParam "@customer_id" (42 :: Int32)
  , outParam "@order_id" (Nothing :: Maybe Int32)
  ]
let [orderId] = procOutputValues res
print (decodeReturnValue orderId :: Either DecodeError (Maybe Int32), procReturnStatus res)

A procedure with typed result sets and no output parameters can also be run as EXEC dbo.report @p1 through queryResultSets.

A parameter of a callProcedure call. inParam and outParam build one from a ToSqlField value; rawInParam and rawOutParam declare an exact wire type. Encoding runs in callProcedure, with the connection's context.

An input parameter, encoded with the connection's context when callProcedure runs. Encoding failures throw EncodeError before the procedure request is sent.

inParam "@customer_id" (42 :: Int32)

An input/output parameter; the value is its initial value (a NULL of the right type, Nothing :: Maybe Int32, when it has none). Its value after the call is in procOutputValues, decoded with decodeReturnValue.

The server returns an output value converted to the type declared for the parameter, which is derived from the initial value, so that type is widened so the output isn't truncated:

• strings and binary (char, varchar, nchar, nvarchar, binary, varbinary) are declared as (max);
• decimal and numeric are declared with precision 38 and scale 10, or the initial value's scale if that is larger (but less than 10 if the initial value's integer digits need it). So 0 :: Decimal and Nothing :: Maybe Decimal both declare decimal(38,10); to get more decimal places give the initial value a larger scale (e.g. roundTo 20 0).

Other types are declared as their ToSqlField instance sends them. An xml output parameter needs an xml initial value such as SqlXml "" (the server won't convert xml to the nvarchar a Text initial value declares). For full control of the declared type, use rawOutParam, which widens nothing. Encoding and widening run in callProcedure, with the connection's context; failures throw EncodeError before the procedure request is sent.

An input parameter with an exact wire type and encoded value, for a type no ToSqlField instance declares:

rawInParam "@x" (TIDecimalN 20 4, decimalValue)

callProcedure still validates the wire type and the value before sending.

rawInParam as an input/output parameter: the declared type is the one given, with none of the widening of outParam, so the server converts the output value to exactly it:

rawOutParam "@x" (TIDecimalN 20 4, Nothing)   -- a NULL decimal(20,4)

What callProcedure returns: the return status, the output parameters' values and the result sets.

Decode an output parameter's value (procOutputValues) as a column of its declared type would be decoded.

Run one statement text once for each row of parameters, all in one request, and return the number of rows each affected, in order. The text refers to a row's parameters as a mkQuery text does, with @p1, @p2, ..., and a row is any ToSqlRow value.

executeMany conn "INSERT INTO item (id, name) VALUES (@p1, @p2)" [(1 :: Int, "one" :: Text), (2, "two")]

Each row is its own sp_executesql call, with the text and the parameter declaration shared (read and encoded once, not per row), so the server compiles the statement once and reuses the plan. It is executeStatements conn (map (mkQuery sql) rows) with that sharing, and everything executeStatements says about round trips, errors, transactions and interruption applies: statements after a failed row may have run, so use withTransaction for all or nothing, or executeManyOutcomes to see which rows failed. For a large load into one table, bulkInsert is faster.

Run several statements in one request, which is one round trip, and return the number of rows each affected, in order, as execute would return it. Each statement is sent as its own sp_executesql call, all in one RPC message. That includes a statement with no parameters, so a SET option it sets or a #temp table it creates lasts only until it returns; send such statements with execute. For many rows of one statement, executeMany is the same with the text read once. To know which statements failed, use executeStatementsOutcomes.

The server runs the statements in order. If some fail, the whole response is still read, and then SqlServerError is thrown for the first that failed. Statements after a failed one may have run: an error that only ends its statement (a constraint violation, say) lets the server go on; one that aborts the batch (THROW, a conversion error, a deadlock, any error under SET XACT_ABORT ON) ends the request there. For all-or-nothing, run it inside withTransaction.

A statement that can't be sent (see queryRequest) throws EncodeError naming its position, before its message is sent. A long list goes out in messages of about 1 MiB, each read before the next is sent, so memory stays bounded; the split changes nothing else. Interrupted, it cancels the running statement; the statements before it have run. An empty list returns [] without a round trip.

What happened to one statement of executeStatementsOutcomes or executeManyOutcomes.

executeMany with each row's outcome, as executeStatementsOutcomes: the way to tell which rows failed.

outcomes <- executeManyOutcomes conn "INSERT INTO item (id, name) VALUES (@p1, @p2)" rows
let failed = [(row, msgText err) | (row, StatementFailed err) <- zip rows outcomes]

executeStatements, returning what happened to each statement instead of throwing the first failure: the statements' outcomes, one for each, in order. A failed statement's error is its StatementFailed; the statements after one that aborted the batch are StatementNotRun. Nothing else changes: an EncodeError, a SevereError, an interruption and a connection that fails are thrown as by executeStatements, and so is an error that closes the connection (severity 20 or more), as SqlServerError.

A message to send: its packet type and its body, written for the connection's context when it is sent. queryRequest builds one from a Query; startResponse and responseS send one.

A request body already in memory, as the request writer takes it. The body's length is forced before anything is written, so a lazy encoding failure fails the request before any of it is sent.

The request a query is sent as: a SQL batch when it has no parameters, otherwise sp_executesql with the parameters encoded for the connection. Throws EncodeError for a value that can't be encoded, a placeholder with no parameter (queryProblem), a name that isn't a T-SQL variable name (paramNameProblem) or is of the reserved form @pN, or a name used twice (SQL Server identifiers are case-insensitive, so @a and @A are one name). Nothing is sent.

queryRequest for a connection with this context, without the connection. It throws EncodeError as queryRequest does. A request with a lazy or streamed parameter can be sent only once.

The messages executeStatements sends for these statements, for a connection with this context (as queryRequestFor for one query): each statement an sp_executesql RPC, in messages of about 1 MiB; or why a statement can't be sent.

A SQL batch: the text as it is, no parameters.

sp_executesql @stmt, @params, @p1, @p2, ...

Parameters with an empty name are called @pN after their 1-based position in the list. The names are spliced into the @params declaration as given, so check them with paramNameProblem first (the query functions do). With no parameters, @params is left out.

An RPC call of the named procedure with these parameters, as callProcedure sends it. Invalid parameters throw EncodeError before the request is constructed.

Why a parameter name can't be used, if it can't: it must be a T-SQL variable name, @ followed by one or more letters, combining marks, decimal digits, @, #, $ or _ (Unicode ones included, but only from the Basic Multilingual Plane: SQL Server rejects surrogates in identifiers). The query functions' parameter names are spliced into the sp_executesql declaration, where anything else (a space, a comma, a parenthesis, a comment) would change what is declared. callProcedure doesn't use this check: its parameter names are sent as data, and the server reports the ones it can't match. SQL Server itself accepts a few more names (a bare @, and ignores format characters such as U+200D and trailing spaces in RPC parameter names), and rejects some letters added in recent Unicode versions; it enforces its 128-character limit itself.

Send a request and stream the response tokens, a low-level view of a response: every row as a TRow (see Hmssql.Tds.Value), and the other tokens, in order. ERROR tokens are withheld and rethrown as SqlServerError once the response is complete, so the connection is never left mid-message. A DONE (DONE, DONEPROC or DONEINPROC) flagged DONE_ERROR or DONE_SRVERROR in a response with no ERROR token is thrown as SevereError at the end, likewise: the server failed the request without a message (as when the session was killed).

A response being read: the reader the query functions share, pulling one step at a time (readStep) until StepEnd. ERROR tokens are withheld (the first kept) and thrown as SqlServerError once the response is complete, so the connection is never left mid-message; a DONE flagged as failed in a response with no ERROR token is thrown as SevereError then.

Not a SQL cursor: it reads one response forwards, once.

Send a request and start reading its response.

Cancel one response: if it hasn't been read to its end, the statement is canceled on the server and the rest of the response discarded, as cancelActiveStatement does for every response of the connection. It returns once the response is over on the server, so the statement's locks are released by then (a round trip, unless the response had fully arrived). Nothing happens if the response was already read to its end or canceled, or the connection is closed or broken. Reading on from the reader, or from a stream of it, throws ResultAbandoned.

The cursor of a response started another way (startStreamingRequest), with the statement's text for its error messages.

Sum of the row counts reported by DONE tokens, reading the response to its end.

The next thing in the response (nextStep), with the rows decoded by the given function: a batch of rows, a token other than a row or a nonfatal ERROR (withheld), or StepEnd, which throws the withheld error (or SevereError) instead if there was one. A response that ends with the connection closed after an error (by the server, as after a severity 20 error, or by a failed transport) throws that error; after close, the ConnectionClosed that every use of the connection gets. (The withheld error is the reader's, not a stream's: a stream run again from a step before the error would otherwise report an error it hadn't seen.)

A fatal ERROR is thrown by the common token reader immediately and leaves the connection broken.

Reading one statement of a batch query (respBatch): the statement's DONEPROC is returned and then StepEnd, for as long as the statement stays the current one; an ERROR token isn't withheld but ends the batch (scopeFail), and the response's end throws SevereError for a failed DONE but never a withheld error, which there is none of.

Read the rest of the response and discard it. The withheld error, if any, is thrown at the end. Nothing to do once the end has been read.

The rows at the head of the response, decoded: what one nextStep returns when it finds rows.

What nextStep found next in the response.