Monday, 9 September 2013

Stored procedure in SQL server-syntax error

Stored procedure in SQL server-syntax error

I have 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!

No comments:

Post a Comment