Hey everyone,
I have a unique need to utilize C# Web Api which connects to a destination SQL Server.
I am facing few issues as I’m still trying to learn C# & thought of reaching out to this community.
I have a stored procedure in destination SQL Server which accepts 2 parameters let’s say Param1 (VARBINARY(60)), Param2 INT
This C# Web Api is being called by another process which sends out these values to it.

Let’s say Param1 gets its value as 0x4869. This value itself is varbinary(60) without any encoding. Now, I would like to send this exact value without any leading and trailing single quotes from the web application to destination stored procedure which accepts varbinary value for this parameter.

But somehow my web application is sending 0x4869 as ‘0x4869’. Because of this my stored procedure is giving incorrect results. Per my understanding, this is happening because I have kept the parameter Param1 as String. In C# is there a way to pass the value as it is i.e. in this case 0x4869 instead of single quotes?

I have tried the byte datatype in C# but it does not seem to be working in this scenario when I execute my Get method where I execute this destination stored procedure.
Thanks!

How much control do you have over the stored procedures? If you can modify this stored procedure, I suggest changing the first parameter to varchar then converting the passed-in string value to varbinary in the stored procedure. That way you don’t have to worry about handing SQL-specific types in your C# code.

Here’s a good thread on converting varchar to varbinary in T-SQL: https://dba.stackexchange.com/questions/63743/converting-a-varchar-to-varbinary

If you can’t change the existing stored procedure but you are able to create a new one, you could create a now one that just accepts the parameters (as varchar, int), converts the varchar to varbinary, then calls the existing stored proc.

I’m sure there are more elegant solutions, but in my experience it’s just simpler to use fundamental data types (string, numeric) when calling SQL Server stored procedures from C#.
Thank you. I have full control over the stored procedure. I tried changing it to varchar and converting to varbinary but it generates a different value altogether.

For example,
From Web App, I pass 0x4869 as ‘0x4869’ into the stored proc parameter.
Now within stored proc if I try to do convert varbinary(60) for param1, it will give me output as 0x307834383639 which is not expected. At the end I just want 0x4869 to be passed in the entirety.

source