Stored Procedures, ExecuteScalar and Parametes

Question

I have a stored procedure that I call like this:

string proc = "SpCreate '00111', 3";

 using (SqlCommand command = new SqlCommand(proc, conn))
 {
       command.CommandType = CommandType.Text;
       command.CommandTimeout = 1000;

       string returnCode = command.ExecuteScalar().ToString();
 }

The above gode works fine. But once I add in a parameter, I get the incorrect syntax near 'SpCreate'. What gives? (Code below causes error.)

string proc = "SpCreate '00111', @myId";

 using (SqlCommand command = new SqlCommand(proc, conn))
 {
       SqlParameter paramName = new SqlParameter("@myId", SqlDbType.Int) { Value = 3 };
       command.Parameters.Add(paramName);

       command.CommandType = CommandType.Text;
       command.CommandTimeout = 1000;

       string returnCode = command.ExecuteScalar().ToString();
 }

Which DB interface are you using? OleDb likes parameters that start with @, but SqlDb prefers place-holders like ?. Also, have you tried using "exec SpCreate '00111', @myId"? — tgolisch, Feb 19 '13 at 18:52

Justin C · Answer 1 · 2013-02-22T02:18:01.600

0

Edited my answer after looking at some of my own code and realizing I had some errors.

You need to set the CommandType to StoredProcedure.

Edit: Example Source Code added

 string proc = "SpCreate";

 using (SqlCommand command = new SqlCommand(proc, conn))
 {
       SqlParameter paramName = new SqlParameter("@myId", SqlDbType.Int);
       paramName.Value = 3;
       command.Parameters.Add(paramName);

       command.CommandType = CommandType.StoredProcedure;
       command.CommandTimeout = 1000;

       string returnCode = command.ExecuteScalar().ToString();
 }

edited Feb 22 '13 at 02:18

answered Feb 19 '13 at 18:55

Justin C

1,924
4
28
43

Neither removing/adding the @ did anything. And adding EXEC made a errror converting from nvarchar to int. – cdub Feb 19 '13 at 19:44
can you describe the value you are putting into proc? Is SpCreate the name of a stored procedure? If it is, is the parameter declared in the Stored Procedure? – Justin C Feb 19 '13 at 20:17
This is how it needs to be done: http://stackoverflow.com/questions/14905917/best-practice-for-handling-sql-injections-when-calling-a-stored-procedure/14905927#14905927 – cdub Feb 19 '13 at 20:29
@chris - I agree that is a correct example, but it is not exactly what you are doing. I still need you to answer my question. Is "SpCreate" the name of the stored procedure, and if so, in that stored procedure do you initialize a parameter called "@myId"? – Justin C Feb 19 '13 at 23:34
Yes spCreate is a name of the stored procedure and the myId is a value passed in (for security reasons I change my code to dummy text) – cdub Feb 20 '13 at 19:40
@chris - then try the sample code I added to my answer. You need to set your CommandType as StoredProcedure and then just give the name of the stored procedure to the Command. Then set the parameter as I showed and it should work. This format matches the link you provided earlier in these comments. – Justin C Feb 20 '13 at 20:45

Stored Procedures, ExecuteScalar and Parametes

1 Answers1