Declare SQL Server not working

Question

I am using razor with C# and I need to declare a variable in SQL but it will not work.

insert_data = db.Query("DECLARE @myid INT");

I get the following error

There was an error parsing the query. [ Token line number = 1,Token line offset = 1,Token in error = DECLARE ]

EDIT:

I am trying to put data into one table, then get its unique ID and insert it into another.

insert_data = db.Query("INSERT INTO sorted (sorted_words) SELECT '" + sorted_word + "' WHERE NOT EXISTS (SELECT sorted_words FROM sorted WHERE sorted_words = '" + sorted_word + "')");
insert_data = db.Query("DECLARE @myid BIGINT");
insert_data = db.Query("SET @myid = scope_identity()");
insert_data = db.Query("INSERT INTO words (words, sorted_id) SELECT '" + word + "', @myid WHERE NOT EXISTS (SELECT words FROM words WHERE words = '" + word + "')");

This isn't going to work. Can you explain what you are trying to accomplish? — David, Oct 23 '13 at 20:41

score 1 · Answer 1 · edited May 23 '17 at 12:28

1

Try the ExecuteStoreCommand instead of using Query

Refrence: Entity Framework : Set MySQL custom environment variables

edited May 23 '17 at 12:28

Community

1
1

answered Oct 23 '13 at 20:41

Jeremi Stadler

2,567
4
21
22

Im connecting with simply 'var db = Database.Open("database_name");' where would I set that? Also i am using SQL Server compact 4.0 – wazzaday Oct 23 '13 at 20:44
woopps my bad... thought you where using MySQL.... Are you using Entity Framework? – Jeremi Stadler Oct 23 '13 at 20:46

score 0 · Answer 2 · answered Oct 23 '13 at 20:57

0

I would suggest to write a stored procedure with all statements inside and execute this stored procedure from your code. This will be much easier to manage.

answered Oct 23 '13 at 20:57

Szymon

42,577
16
96
114

score 0 · Accepted Answer · answered Oct 23 '13 at 21:26

SQL Compact parameters are not declared in the same way as with SQL Server. When working with the Database helper, you should name your parameters @0, @1, @2 etc, incrementing by 1 each time. Also, the Database.Query method is for selecting multiple rows of data. You should use Execute for inserts and GetLastInsertId for getting the most recent row's identity value:

db.Execute("INSERT INTO sorted (sorted_words) SELECT @0 WHERE NOT EXISTS (SELECT sorted_words FROM sorted WHERE sorted_words = @0)", sorted_word);
var id = (int)db.GetLastInsertId();
db.Execute("INSERT INTO words (words, sorted_id) SELECT @0, @1 WHERE NOT EXISTS (SELECT words FROM words WHERE words = @0)", word, id);

Read more about working with data in Razor web pages here: http://www.asp.net/web-pages/tutorials/data

Declare SQL Server not working

3 Answers3