question

DavidThielen-0658 avatar image
0 Votes"
DavidThielen-0658 asked APoblacion answered

Non standard parameter types for stored procedures

Hi all;

We have a commercial library that supports pulling data via ADO.NET. This includes a template designer where our U.I. assists the user in writing the selects. A select that calls a stored procedure obviously needs to support passing in the parameters, in the type expected.

We have code to get all the parameter types from the database metadata. We then type the parameters based on that metadata type info. And it all works fine for all the standard types that are strings, numbers, & dates.

But for custom types (the PostgreSQL pagilua DB has a type of mpaa_rating) and the DB specific standard types (Sql Server has the guid type), we're at a loss of what to do.

Is there a way to get an allowable standard type that can be passed in for that parameter? For example, would mpaa_rating and guid types accept a string as the parameter value? Yes it has to be an acceptable string, but that part is not our problem. Our problem is to give the user a way to pass the parameter. It's on them for the parameter value to be correct.

Or is there some other way to handle this?

thanks - dave

dotnet-adonet
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

APoblacion avatar image
0 Votes"
APoblacion answered

I can't speak about PostgreSQL, but on SQL server you can pass a parameter of any type for any other type, and as long as the server has an implicit conversion from one to the other, it will work. For example, you can pass a Varchar where the server is expecting a Datetime, and it will work as long as the string happens to represent a date that can be recognized by the server's date parser.

This page in the SQL Server documentation contains a table that tells all the compatible conversions. You will notice that it contains some unsusual data types such as hierarchyid or clr_udt which you would normally have difficulty to pass from the client code, but they can be converted from varchar. Hopefully, PostgreSql has something equivalent.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.