Stored procedure in SQL Server; syntax error

Question

I have a stored procedure called SelFromWeather2 and I need to return values from table. When I perform syntax check I don't get errors, but when I call it from C# I get syntax error near SelFromWeather2.

Here is code:

CREATE PROCEDURE SelFromWeather2  
@location VARCHAR(MAX),
@des VARCHAR(200) OUTPUT,
@min INT OUTPUT,
@max INT OUTPUT,
@humidity INT OUTPUT,
@pressure INT OUTPUT,
@speed INT OUTPUT,
@date Datetime OUTPUT
AS
IF EXISTS(SELECT * FROM Weather2 WHERE LOCATION LIKE @location)
BEGIN

 CREATE TABLE T
 (
 forc XML,
 loc VARCHAR(MAX),
 dat Datetime
 );

 INSERT INTO T(forc, loc, dat) SELECT TOP 1 [FORECAST],[LOCATION],[DATE] FROM Weather2   
 WHERE LOCATION LIKE @location ORDER BY DATE DESC;

 SET @location=(SELECT loc FROM T);
 SET @location =(SELECT loc FROM T);
 SET @des= (SELECT forc.value('(/Weather//Forecast/Description/node())[1]', 'nvarchar(max)')   FROM T);
 SET @min= (SELECT  forc.value('(/Weather//Forecast/MinTemp/node())[1]', 'int') FROM T);
 SET @max=(SELECT forc.value('(/Weather//Forecast/MaxTemp/node())[1]', 'int') FROM T);
 SET @humidity=(SELECT forc.value('(/Weather//Forecast/Humidity/node())[1]', 'int') FROM T);
 SET @pressure= (SELECT forc.value('(/Weather//Forecast/Pressure/node())[1]', 'int') FROM T);
 SET @speed=(SELECT forc.value('(/Weather//Forecast/Speed/node())[1]', 'int') FROM T);
 SET @date= (SELECT forc.value('(/Weather//Forecast/Date/node())[1]', 'Datetime') FROM T);

 DROP TABLE T;
END

The code for calling this procedure is:

           string location = "Paris";

            SqlDataReader myReader = null;
            SqlCommand myComand = new SqlCommand("SelFromWeather2",  myConnection);
            myComand.Parameters.AddWithValue("@location", location);

            SqlParameter min = myComand.Parameters.Add("@min", System.Data.SqlDbType.Int);
            SqlParameter max = myComand.Parameters.Add("@max", System.Data.SqlDbType.Int);
            SqlParameter humidity = myComand.Parameters.Add("@humidity", System.Data.SqlDbType.Int);

            SqlParameter pressure = myComand.Parameters.Add("@pressure", System.Data.SqlDbType.Int);
            SqlParameter speed = myComand.Parameters.Add("@speed", System.Data.SqlDbType.Int);
            SqlParameter dat = myComand.Parameters.Add("@date", System.Data.SqlDbType.DateTime);
            SqlParameter des = myComand.Parameters.Add("@des", System.Data.SqlDbType.VarChar, 200);
            min.Direction = System.Data.ParameterDirection.Output;               
            max.Direction = System.Data.ParameterDirection.Output;
            humidity.Direction = System.Data.ParameterDirection.Output;
            pressure.Direction = System.Data.ParameterDirection.Output;
            speed.Direction = System.Data.ParameterDirection.Output;
            dat.Direction = System.Data.ParameterDirection.Output;
            des.Direction = System.Data.ParameterDirection.Output;

            myComand.ExecuteReader();


            int minTemp = (int)min.Value;
            int maxTemp = (int)max.Value;
            int hum = (int)humidity.Value;
            int press = (int)pressure.Value;
            int wind = (int)speed.Value;
            string description = des.Value.ToString();
            DateTime datum = (DateTime)dat.Value;

Please help, I'm stuck!

What is the exact error message you are getting? Also, you should use a temp table (see here: technet.microsoft.com/en-us/library/ms174979.aspx, search for Temporary Tables). — rsbarro
– rsbarro, Commented Sep 10, 2013 at 1:20
I tried also SELECT @location=loc FROM T but also kept getting same error. — Edna
– Edna, Commented Sep 10, 2013 at 1:21

Comfortably Numb · Accepted Answer · 2013-09-10 01:20:28Z

4

Set CommandType of myCommand to StoredProcedure

answered Sep 10, 2013 at 1:20

Comfortably Numb

39.9k17 gold badges82 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DaveShaw · Accepted Answer · 2013-09-10 01:20:36Z

4

You need to set the CommandType to StoredProcedure

myCommand.CommandType = CommandType.StoredProcedure

The default is Text.

answered Sep 10, 2013 at 1:20

DaveShaw

52.9k18 gold badges118 silver badges142 bronze badges

Collectives™ on Stack Overflow

Stored procedure in SQL Server; syntax error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related