Checking Parameter Type in SQL Query

ANSWER ATTEMPT 1

If my understanding is correct, that you have a text input in your application, and are passing this text verbatim to SQL, then the type of @Search would likely be NVARCHAR(200) to catch all search terms. The length may vary, but I can't imagine people searching for much more than 200 characters at a time.

If my understanding is correct, you can infer a type by trying to convert it, e.g.

DECLARE @i INT = TRY_CONVERT(INT, @Search)

If @i is not null then @Search is an int, and you can use the parameter @i to search on any int columns you have. I'd also be inclined to UNION your results together rather than than do IF. Suppose you have a material name that is "Steel", and you also have a customer whose last name is "Steel", then at present you'll only return the row from Part, and mis any other matches, and you would have no idea what the user actually intended. Something like:

CREATE PROCEDURE dbo.UniversalSearch @Search NVARCHAR(200)
AS
BEGIN

    DECLARE @i INT = TRY_CONVERT(INT, @Search),
            @dt DATETIME = TRY_CONVERT(DATETIME, @Search);

    SELECT SourceTable = 'Part', SourceColumn = 'Part Number', Matches = COUNT(*)
    FROM    Part 
    WHERE   [Part Number] = @i
    UNION ALL
    SELECT SourceTable = 'Part', SourceColumn = 'Material Name', Matches = COUNT(*)
    FROM    Part 
    WHERE   [Material Name]=@Search
    UNION ALL
    SELECT  SourceTable = 'Part', SourceColumn = 'Sales Order', Matches = COUNT(*)
    FROM    Part
    WHERE   SalesOrder = @i
    UNION ALL
    SELECT  SourceTable = 'CustomerInfo', SourceColumn = 'CID', Matches = COUNT(*)
    FROM    CustomerInfo
    WHERE   CID = @i
    UNION ALL
    SELECT  SourceTable = 'CustomerInfo', SourceColumn = 'CID', Matches = COUNT(*)
    FROM    CustomerInfo
    WHERE   CreatedDate = @dt;

END

As long as everything you are searching for is indexed (and you use the appropriate variable in each search) you should get reasonable performance out of this, since you are now searching on the right types.

ANSWER ATTEMPT 2

SQL does not have dynamic typing, the dynamic typing is being done by c#, and in c# you can retrieve what the type is before it is sent to the server , e.g.

using System;
using System.Data;
using System.Data.SqlClient;
                    
public class Program
{
    public static void Main()
    {
        Console.WriteLine(GetSqlType(1)); // Int
        Console.WriteLine(GetSqlType("string")); //NvarChar
        Console.WriteLine(GetSqlType(DateTime.Now)); //DateTime
    }
    static SqlDbType GetSqlType(object obj)
    {
        return new SqlCommand().Parameters.Add("@Search", obj).SqlDbType;
    }
}

You could then pass this type to your SQL and build your query based on that type, but even this seems like overkill, since it is unlikely that you are actually receiving your parameters as an object (especially if from a text input) and you are doing more boxing/unboxing than is necessary. It would be interesting to know the steps leading up to actually calling the SQL to see how you are passing the data from the UI through to the SqlCommand, to see where in this process some smarter typing could take place.

ANSWER ATTEMPT 3

It is possible to get the type of a variable if you select it into a table, then check the metadata for that table, i.e.

DECLARE @Search NVARCHAR(MAX);

IF OBJECT_ID(N'tempdb..#T', 'U') IS NOT NULL
    DROP TABLE #T;

SELECT  Variable = @Search
INTO    #T;

SELECT  t.name,
        c.scale,
        c.precision,
        c.max_length
FROM    tempdb.sys.columns AS c
        INNER JOIN sys.types AS t
            ON t.system_type_id = c.system_type_id
            AND t.user_type_id = c.user_type_id
WHERE   c.object_id = OBJECT_ID(N'tempdb..#T')
AND     c.name = 'Variable';

This is clunky and verbose. The reason that there is no simple way of doing this is that with SQL alone this is never a requirement. The type of a variable/parameter is always known, so having a way of checking something that is know is redundant in SQL. If you use other applications to generate sql dynamically, then can't use that SQL as you like, that is not a limitation of SQL, but a limitation of the tool you are using. This is partly why AddWithValue() is generally advised against:

• AddWithValue is Evil
• Can we stop using AddWithValue() already?

Assuming you are stuck with this approach and have used the above to identify the type, it is still not that useful, you then need to do a load of conditional checks to work out when you can actually use it, like:

IF @TypeName IN ('char', 'nchar', 'varchar', 'nvarchar')
BEGIN
     -- Run queries that treat @search as a string
END
ELSE IF @TypeName IN ('int', 'smallint', 'tinyint', 'bigint', 'decimal', 'float')
BEGIN 
      --Run queries that treat @search as a number
END
-- etc

This doesn't really offer any benefits to identifying the type in c# and passing it to the procedure as above, it is just a bit more verbose.

So if identifying the type in c#, or SQL is not that useful, then what is? If it were me, I would probably have multiple procedures, but failing that you could create a procedure that takes different parameter types:

CREATE PROCEDURE dbo.UniversalSearch 
       @SearchNVarchar NVARCHAR(200),
       @SearchInt INT,
       @SearchDateTime DATETIME,
       @SearchDecimal DECIMAL(38, 18)
AS
BEGIN

    SELECT SourceTable = 'Part', SourceColumn = 'Part Number', Matches = COUNT(*)
    FROM    Part 
    WHERE   [Part Number] = @SearchInt
    UNION ALL
    SELECT SourceTable = 'Part', SourceColumn = 'Material Name', Matches = COUNT(*)
    FROM    Part 
    WHERE   [Material Name]=@SearchNvarchar
    UNION ALL
    SELECT  SourceTable = 'Part', SourceColumn = 'Sales Order', Matches = COUNT(*)
    FROM    Part
    WHERE   SalesOrder = @SearchInt
    UNION ALL
    SELECT  SourceTable = 'CustomerInfo', SourceColumn = 'CID', Matches = COUNT(*)
    FROM    CustomerInfo
    WHERE   CID = @SearchInt
    UNION ALL
    SELECT  SourceTable = 'CustomerInfo', SourceColumn = 'CID', Matches = COUNT(*)
    FROM    CustomerInfo
    WHERE   CreatedDate = @SearchDateTime;

END

You've not posted your c# code, so will have to make some guesses about how you are getting your dynamic type there, but you could do something like:

using (var connection = new SqlConnection("connectionString"))
using (var command = new SqlCommand("dbo.UniversalSearch", connection))
{
     connection.Open();
     var intParam = command.Parameters.Add("@SearchInt", SqlDbType.Int);
     var nvarcharParam = command.Parameters.Add("@SearchNVarchar", SqlDbType.NVarChar, 200);
     var decParam = command.Parameters.Add("@SearchInt", SqlDbType.Decimal);
     var dateTimeParam = command.Parameters.Add("@SearchInt", SqlDbType.DateTime);

    if (obj is int)
         intParam.Value = obj;
    else if (obj is decimal)
         decParam.Value = obj;
    else if (obj is DateTime)
         dateTimeParam.Value = obj;
    else
         nvarcharParam.Value = obj;

}

It is really difficult to suggest the right way of doing this for you though, as you've only shown us a very small piece of the puzzle.

Finally, if your question is simply, can I get the type of the variable in SQL or is it a limitation that I can't?

Then Yes you can (as above), but it is not simple. I would not consider this a limitation of SQL though. Suppose I send my car off to another company to make it amphibious, but when it hits the water I realise that there is no rudder so I can't steer it. It is not a limitation of my car that it does not have a rudder, my car when used as built does not need a rudder. It's not a great analogy, but the best I could think of. Basically, Microsoft have created this awful AddWithValue method that has given the ability to people to do something that SQL was never designed to do. If this then generates problems, it lies solely at the door of AddWithValue and not with SQL.