Hi,
I am using tdbc's odbc driver to work with an MS SQL Server (2008) database.  I have run into a problem when writing code to create a stored procedure, specifically w.r.t. the syntax for a procedure's parameters.  SQL Server's "CREATE PROCEDURE" syntax (http://msdn.microsoft.com/en-us/library/ms187926%28v=sql.100%29.aspx) requires that a procedure's parameters be declared using the @ symbol preceding the name, e.g. @myvar
Just doing this results in the connection's prepare method returning a statement whose params method returns a dict that has all of the stored procedure's parameters as bound variables.  If I precede the parameter specifications with an underscore then the params dict does not contain the parameters, they are not seen as bound variables (b.t.w. this is undocumented) but when executing the statement fails due to the sql command still having the underscore.  See examples below.
Thanks Douglas


(tools) 5 %   set con_str {Driver=SQL Server Native Client 10.0;Server=127.0.0.1\SQLEXPRESS;DATABASE=TDBC;UID=builder;PWD=bob;}
Driver=SQL Server Native Client 10.0;Server=127.0.0.1\SQLEXPRESS;DATABASE=TDBC;UID=builder;PWD=bob;
(tools) 6 % tdbc::odbc::connection create my_mssql_con $con_str
::my_mssql_con
(tools) 7 % set sql_stmt {
>   CREATE PROCEDURE [ESS].[sp_commitsinsert]
>     @commitRef nchar(64),
>     @streamId int,
>     @lastCommitId int,
>     @events nchar(64),
>     @metadata nchar(64) = NULL,
>     @snapshot nchar(64) = NULL
>   AS
>     IF EXISTS (SELECT 1 FROM [ESS].[t_streams_myStore] s WHERE s.streamId = @streamId AND s.lastCommitId != @lastCommitId)
>       RAISERROR('Concurrency exception', 0, 0)
>     ELSE
>       INSERT INTO [ESS].[t_commits_myStore] (commitRef, streamId, lastCommitId, events, metadata, snapshot)
>       VALUES (@commitRef, @streamId, @lastCommitId, @events, @metadata, @snapshot)
> }

  CREATE PROCEDURE [ESS].[sp_commitsinsert]
    @commitRef nchar(64),
    @streamId int,
    @lastCommitId int,
    @events nchar(64),
    @metadata nchar(64) = NULL,
    @snapshot nchar(64) = NULL
  AS
    IF EXISTS (SELECT 1 FROM [ESS].[t_streams_myStore] s WHERE s.streamId = @streamId AND s.lastCommitId != @lastCommitId)
      RAISERROR('Concurrency exception', 0, 0)
    ELSE
      INSERT INTO [ESS].[t_commits_myStore] (commitRef, streamId, lastCommitId, events, metadata, snapshot)
      VALUES (@commitRef, @streamId, @lastCommitId, @events, @metadata, @snapshot)

(tools) 8 % set stmt [my_mssql_con prepare $sql_stmt]
::oo::Obj22::Stmt::1
(tools) 9 % set param_dict {}
(tools) 10 % set param_dict [$stmt params]
commitRef {name commitRef direction in type wvarchar precision 255 scale 0} streamId {name streamId direction in type wvarchar precision 255 scale 0} lastCommitId {name lastCommitId direction in type wvarchar precision 255 scale 0} events {name events direction in type wvarchar precision 255 scale 0} metadata {name metadata direction in type wvarchar precision 255 scale 0} snapshot {name snapshot direction in type wvarchar precision 255 scale 0}
(tools) 11 % set args {}
(tools) 12 % foreach v_name [dict keys $param_dict] arg $args {
>   if { ($v_name ne {}) && ([dict get $param_dict $v_name direction] ne "out") } {
>     dict set param_dict $v_name $arg
>   }
> }
(tools) 13 % puts $param_dict
commitRef {} streamId {} lastCommitId {} events {} metadata {} snapshot {}
(tools) 14 % set resultset [$stmt execute $param_dict]
[Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near the keyword 'PROCEDURE'.
[Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.
(executing the statement)
(tools) 15 %
(tools) 15 %
(tools) 15 % set sql_stmt {
>   CREATE PROCEDURE [ESS].[sp_commitsinsert]
>     _@commitRef nchar(64),
>     _@streamId int,
>     _@lastCommitId int,
>     _@events nchar(64),
>     _@metadata nchar(64) = NULL,
>     _@snapshot nchar(64) = NULL
>   AS
>     IF EXISTS (SELECT 1 FROM [ESS].[t_streams_myStore] s WHERE s.streamId = _@streamId AND s.lastCommitId != _@lastCommitId)
>       RAISERROR('Concurrency exception', 0, 0)
>     ELSE
>       INSERT INTO [ESS].[t_commits_myStore] (commitRef, streamId, lastCommitId, events, metadata, snapshot)
>       VALUES (_@commitRef, _@streamId, _@lastCommitId, _@events, _@metadata, _@snapshot)
> }

  CREATE PROCEDURE [ESS].[sp_commitsinsert]
    _@commitRef nchar(64),
    _@streamId int,
    _@lastCommitId int,
    _@events nchar(64),
    _@metadata nchar(64) = NULL,
    _@snapshot nchar(64) = NULL
  AS
    IF EXISTS (SELECT 1 FROM [ESS].[t_streams_myStore] s WHERE s.streamId = _@streamId AND s.lastCommitId != _@lastCommitId)
      RAISERROR('Concurrency exception', 0, 0)
    ELSE
      INSERT INTO [ESS].[t_commits_myStore] (commitRef, streamId, lastCommitId, events, metadata, snapshot)
      VALUES (_@commitRef, _@streamId, _@lastCommitId, _@events, _@metadata, _@snapshot)

(tools) 16 % set stmt [my_mssql_con prepare $sql_stmt]
::oo::Obj22::Stmt::2
(tools) 17 % set param_dict {}
(tools) 18 % set param_dict [$stmt params]
(tools) 19 % set args {}
(tools) 20 % foreach v_name [dict keys $param_dict] arg $args {
>   if { ($v_name ne {}) && ([dict get $param_dict $v_name direction] ne "out") } {
>     dict set param_dict $v_name $arg
>   }
> }
(tools) 21 % puts $param_dict

(tools) 22 % set resultset [$stmt execute $param_dict]
[Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near '_@commitRef'.
[Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.
(executing the statement)
(tools) 23 %