January 21, 2008 at 6:48 am
I have an extended stored procedure in C++ that I am converting to CLR so that it will also work on 64 bit (do not have the original source to just recompile it)
The procedure takes a varchar(n) as one of its inputs. The size of n makes a difference to the output even if the actual data is the same. For exampleDECLARE @i VARCHAR(10)
SET @i 'test'
exec xp_test @i, @o OUTPUTproduces a different result toDECLARE @i VARCHAR(4)
SET @i 'test'
exec xp_test @i, @o OUTPUT
I want to test my functions that are normally called by sql server, in both native C++ and C# with some sample values directly in the code but I can't work out how to pass them varchars of a specific length without the actual data always being that length. For example a varchar(10) with value "test".
How can I mimic a varchar(n) in native C++ and also in C# without always using a string that is actually n characters long?
Thanks
January 22, 2008 at 7:32 am
In C#, would something like this work? Key is setting the length of the input parameter when you create it. You didn't indicate what type @o is so I assumed it was int.
private int CallProc(int inputParmLength, string inputParmValue)
{
int outputValue = -1;
using (SqlConnection conn = new SqlConnection("[your connection string here]"))
{
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "xp_test";
SqlParameter parm1 = new SqlParameter("@i", SqlDbType.VarChar, inputParmLength);
SqlParameter parm2 = new SqlParameter("@o", SqlDbType.Int);
parm1.Value = inputParmValue;
parm2.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parm1);
cmd.Parameters.Add(parm2);
conn.Open();
int affectedRows = cmd.ExecuteNonQuery();
outputValue = (int)cmd.Parameters["@o"].Value;
conn.Close();
}
return outputValue;
}
January 22, 2008 at 9:10 am
Isn't this only used to get the DBMS to execute the procedure and return the result back to the application?
I was hoping to do something more like just call the function as a normal C# method but pass in parameters that would be the same as SQL Server 2005 passes in when it calls the procedure.
What I was looking for was more along the lines of (from memory)
[SqlProcedure]
public void foo(...)
{
...
}
public void testfoo()
{
foo(...); // send a varchar(n) to foo
}
I was also hoping to do this in C++ as well, where I have even less of a clue how to do it.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply