I'm having problems running a stored procedure, I'm getting an error that I don't understand. My procedure is this:
ALTER PROC sp_get_allowed_growers
@.GrowerList varchar(500)
AS
BEGIN
SET NOCOUNT ONDECLARE @.SQL varchar(600)
SET @.SQL =
'SELECT nu_code, nu_description, nu_master
FROM nursery WHERE nu_master IN (' + @.GrowerList + ') ORDER BY nu_code ASC'EXEC(@.SQL)
END
GO
and the code I'm using to execute the procedure is this:
public DataSet GetGrowers(string Username)
{
System.Text.StringBuilder UserRoles = new System.Text.StringBuilder();
UsersDB ps = new UsersDB();
SqlDataReader dr = ps.GetRolesByUser(Username);
while(dr.Read())
{
UserRoles.Append(dr["RoleName"]+",");
}
UserRoles.Remove(UserRoles.Length-1,1);
//Create instance of Connection and Command objects
SqlConnection transloadConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionStringTARPS"]);
SqlDataAdapter transloadCommand = new SqlDataAdapter("sp_get_allowed_growers",transloadConnection);
//Create and fill the DataSet
SqlParameter paramList = new SqlParameter("@.GrowerList",SqlDbType.VarChar);
paramList.Value = UserRoles.ToString();
transloadCommand.SelectCommand.Parameters.Add(paramList);
DataSet dsGrowers = new DataSet();
transloadCommand.Fill(dsGrowers);
return dsGrowers;}
The UserRoles stringbuilder has an appropriate value when it is passed to the stored procedure. When I run the stored procedure in query analyser it runs just fine. However, when I step through the code above, I get the following error:
Line 1: Incorrect syntax near 'sp_get_allowed_growers'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'sp_get_allowed_growers'.
Anyone with any ideas would be very helpful...Try surrounding your parameter list (just the one parameter) in your stored procedure in parentheses. Also, I've never used Alter proc, I use Create Procedure (not to say that Alter proc doesn't work, I don't know - just an observation)|||CREATE PROCEDURE dbo.sp_get_allowed_growers
(
@.GrowerList varchar(500)
)
AS
I always use dbo.sp"NAME" because when you push to production your account may or may not be the dbo.|||Although, technically, you should never preface your stored procedure names with sp_. sp_ is used by sql server to designate system stored procedures. When you preface your own sprocs with sp_, you cause sql server to first search through all system sprocs, then through the local sprocs to find your procedure. In other words, you're adding in a bit of inefficiency to each and every procedure call.|||Thanks for the suggestions guys, I tried that and I still get the same error. It's a bit of an odd one because the stored procedure seems fine. I compiled it in query analyser and I can run it in query analyser by right-clicking and selecting "Open", I'm then prompted for the paramter and it returns the expected result so, as far as I know, it's functioning correctly. When I step through the code calling the procedure, the Autos window shows the value of the parameter as "'Admins','NH'" which is exactly what I'd expect.
I'm pretty sure it's something to do with the way that the value of the parameter is being assigned. In query analyser, when I run the stored proc and I am prompted for the value of the @.GrowerList parameter, I have to type the values in exactly like this 'Admins','NH' and it works.
The odd thing is that the database is raising the error and saying that there's a syntax error in the stored proc when there definitely isn't. It would be different if it was raising a type conversion error but it isn't.
Keep thinking folks, I'll send a small prize to whoever can help me crack it (don't get too excited, it will probably be a company pen or something!)
Cheers,|||Add
transloadCommand.SelectCommand.CommandType=CommandType.StoredProcedure;
before you call .Fill()
And I would also concur that naming an sp with an sp_ prefix will slow things down...|||Thanks doug, that cracked it straight away, I'm very grateful. If you email me your address to imacleverbloke@.mcowan.info I'll pop the freebies in the post. Don't get too excited but they're worth having!
As for the sp_ naming convention, I hear about that performance issue too late and I'm kinda stuck with it until the next comprehensive overhaul.
Cheers guys.
rpack79: