0

I have a PowerShell function that executes stored procedures in a SQL Server 2016 database. It works fine with any parameters of any char variant type, but it fails when encountering parameters of types such as int, numeric, or datetime.

I get errors like

Error converting data type varchar to int, numeric, datetime

I can force it to work by inserting the values directly into the exec string but obviously I want to avoid this.

Another problem is that while the function handles size of the parameters, I do not know how to handle the precision of numerical values. Other solutions and examples I have found don't address this specific problem.

Here is the function; it takes in the query statement as a string, along with an array of arrays that can hold data for parameters in the form @((name,value,type,size),etc)

function ExecQuery( $sql,$parameters=@()) {
    <#debug#>write-host "query: "$sql
    #create command obj
    $cmd=new-object system.Data.SqlClient.SqlCommand($sql,$Connection)
    $cmd.CommandTimeout=$timeout
    #create and add parameters

    foreach($item in $parameters) {
        #if several params, $item is array
        if($item -is [System.Array]) {
            <#debug#>write-host "key = "$item[0] " ,`tvalue = "$item[1]"`t"$item[1].gettype()
            if($item[3] -eq $null) { #if no size limit
                $cmd.Parameters.Add($item[0], $item[2]).value = $item[1]
            } else { #if size limit
                $cmd.Parameters.Add($item[0], $item[2], $item[3]).Value = $item[1]
            }
        }
        else { #if single param, $item is string
            <#debug#>write-host "key = "$parameters[0] " , value = "$parameters[1]" "$parameters[1].gettype()
            if($parameters[3] -eq $null) { #if no size limit
                $cmd.Parameters.Add($parameters[0], $parameters[2]).Value = $parameters[1]
            } else {
                $cmd.Parameters.Add($parameters[0], $parameters[2], $parameters[3]).Value = $parameters[1]
            }
            break
        }
    }

    $ds=New-Object system.Data.DataSet
    $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
    $da.fill($ds) | Out-Null
    <#debug#>foreach($Row in $ds.Tables[0].Rows){if($($Row[0])){write-host "return: $($Row[0])"}}write-host "" #line space after function output
    return $ds
}

A calling statement would look like:

$result = ExecQuery -sql (  
    "exec procedure_name " +  
    "@param1='%@param1%'," +  
    "@param2='%@param2%'," +  
    "@param3='%@param3%',"  
    ) -parameters (  
    ('param1','foo',[System.Data.SqlDbType]::varchar,3),  
    ('param2',3,[System.Data.SqlDbType]::int,$null), #fail  
    ('param3','2020-02-10 11:30:00',[System.Data.SqlDbType]::datetime,$null) #fail  
    )

Is this a syntax issue? Is there a better way I could be doing this? Any obvious optimizations that would help? Any good in-depth tutorials that address these kinds of issues?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
MG115
  • 23
  • 4
  • Have you looked at the new SqlServer module? I've found it to work quite well with a dataset object. – Matthew Feb 10 '20 at 18:31
  • Since you're already using the SQLCommand object, you should look at using its Parameters collection. – Mike Shepard Feb 10 '20 at 19:58
  • That error sounds logical. When you enquote parameter in single quotes it should cast it to varchar. Use strings or don't enquote. I would also use different name for stored procedure parameter and query parameter. – Mike Twc Feb 11 '20 at 04:26

0 Answers0