@RowFrom int
@RowTo int
are both Global Input Params for the Stored Procedure, and since I am compiling the SQL query inside the Stored Procedure with T-SQL then using Exec(@sqlstatement) at the end of the stored procedure to show the result, it gives me this error when I try to use the @RowFrom or @RowTo inside the @sqlstatement variable that is executed.. it works fine otherwise.. please help.
"Must declare the scalar variable "@RowFrom"."
Also, I tried including the following in the @sqlstatement variable:
'Declare @Rt int'
'SET @Rt = ' + @RowTo
but @RowTo still doesn’t pass its value to @Rt and generates an error.
hofnarwillie
3,54310 gold badges47 silver badges73 bronze badges
asked Aug 24, 2011 at 20:39
1
You can’t concatenate an int to a string. Instead of:
SET @sql = N'DECLARE @Rt int; SET @Rt = ' + @RowTo;
You need:
SET @sql = N'DECLARE @Rt int; SET @Rt = ' + CONVERT(VARCHAR(12), @RowTo);
To help illustrate what’s happening here. Let’s say @RowTo = 5.
DECLARE @RowTo int;
SET @RowTo = 5;
DECLARE @sql nvarchar(max);
SET @sql = N'SELECT ' + CONVERT(varchar(12), @RowTo) + ' * 5';
EXEC sys.sp_executesql @sql;
In order to build that into a string (even if ultimately it will be a number), I need to convert it. But as you can see, the number is still treated as a number when it’s executed. The answer is 25, right?
In your case you can use proper parameterization rather than use concatenation which, if you get into that habit, you will expose yourself to SQL injection at some point (see this and this:
SET @sql = @sql + ' WHERE RowNum BETWEEN @RowFrom AND @RowTo;';
EXEC sys.sp_executesql @sql,
N'@RowFrom int, @RowTo int',
@RowFrom, @RowTo;
answered Aug 24, 2011 at 21:01
Aaron BertrandAaron Bertrand
268k36 gold badges457 silver badges485 bronze badges
4
You can also get this error message if a variable is declared before a GOand referenced after it.
See this question and this workaround.
answered Mar 25, 2019 at 22:11
Pierre CPierre C
2,43132 silver badges31 bronze badges
Just FYI, I know this is an old post, but depending on the database COLLATION settings you can get this error on a statement like this,
SET @sql = @Sql + ' WHERE RowNum BETWEEN @RowFrom AND @RowTo;';
if for example you typo the S in the
SET @sql = @***S***ql
sorry to spin off the answers already posted here, but this is an actual instance of the error reported.
Note also that the error will not display the capital S in the message, I am not sure why, but I think it is because the
Set @sql =
is on the left of the equal sign.
answered Apr 1, 2015 at 19:13
htm11hhtm11h
1,7198 gold badges46 silver badges103 bronze badges
0
Sometimes, if you have a ‘GO’ statement written after the usage of the variable, and if you try to use it after that, it throws such error. Try removing ‘GO’ statement if you have any.
answered May 24, 2021 at 6:12
This is most likely not an answer to the issue itself, but this question pops up as first result when searching for Sql declare scalar variable hence I want to share a possible solution to this error.
In my case this error was caused by the use of ; after a SQL statement. Just remove it and the error will be gone.
I guess the cause is the same as @IronSean already posted in a comment above:
it’s worth noting that using GO (or in this case 😉 causes a new branch where declared variables aren’t visible past the statement.
For example:
DECLARE @id int
SET @id = 78
SELECT * FROM MyTable WHERE Id = @var; <-- remove this character to avoid the error message
SELECT * FROM AnotherTable WHERE MyTableId = @var
answered Nov 5, 2020 at 16:25
ViRuSTriNiTyViRuSTriNiTy
4,9122 gold badges31 silver badges56 bronze badges
5
Just adding what fixed it for me, where misspelling is the suspect as per this MSDN blog…
When splitting SQL strings over multiple lines, check that that you are comma separating your SQL string from your parameters (and not trying to concatenate them!) and not missing any spaces at the end of each split line. Not rocket science but hope I save someone a headache.
For example:
db.TableName.SqlQuery(
"SELECT Id, Timestamp, User " +
"FROM dbo.TableName " +
"WHERE Timestamp >= @from " +
"AND Timestamp <= @till;" + [USE COMMA NOT CONCATENATE!]
new SqlParameter("from", from),
new SqlParameter("till", till)),
.ToListAsync()
.Result;
EBH
10.3k3 gold badges32 silver badges58 bronze badges
answered Jun 21, 2017 at 15:46
Tim TylerTim Tyler
2,1612 gold badges17 silver badges12 bronze badges
1
Case Sensitivity will cause this problem, too.
@MyVariable and @myvariable are the same variables in SQL Server Man. Studio and will work. However, these variables will result in a «Must declare the scalar variable «@MyVariable» in Visual Studio (C#) due to case-sensitivity differences.
answered Jun 9, 2016 at 11:20
Just an answer for future me (maybe it helps someone else too!). If you try to run something like this in the query editor:
USE [Dbo]
GO
DECLARE @RC int
EXECUTE @RC = [dbo].[SomeStoredProcedure]
2018
,0
,'arg3'
GO
SELECT month, SUM(weight) AS weight, SUM(amount) AS amount
FROM SomeTable AS e
WHERE year = @year AND type = 'M'
And you get the error:
Must declare the scalar variable «@year»
That’s because you are trying to run a bunch of code that includes BOTH the stored procedure execution AND the query below it (!). Just highlight the one you want to run or delete/comment out the one you are not interested in.
marc_s
721k173 gold badges1320 silver badges1442 bronze badges
answered Jul 21, 2019 at 18:05
saiyancodersaiyancoder
1,2551 gold badge13 silver badges20 bronze badges
If someone else comes across this question while no solution here made my sql file working, here’s what my mistake was:
I have been exporting the contents of my database via the ‘Generate Script’ command of Microsofts’ Server Management Studio and then doing some operations afterwards while inserting the generated data in another instance.
Due to the generated export, there have been a bunch of «GO» statements in the sql file.
What I didn’t know was that variables declared at the top of a file aren’t accessible as far as a GO statement is executed. Therefore I had to remove the GO statements in my sql file and the error «Must declare the scalar variable xy» was gone!
answered Oct 19, 2020 at 10:33
pburpbur
656 bronze badges
As stated in https://learn.microsoft.com/en-us/sql/t-sql/language-elements/sql-server-utilities-statements-go?view=sql-server-ver16 , the scope of a user-defined variable is batch dependent .
—This will produce the error
GO
DECLARE @MyVariable int;
SET @MyVariable = 1;
GO --new batch of code
SELECT @MyVariable--CAST(@MyVariable AS
int);
GO
—This will not produce the error
GO
DECLARE @MyVariable int;
SET @MyVariable = 1;
SELECT @MyVariable--CAST(@MyVariable AS int);
GO
We get the same error when we try to pass a variable inside a dynamic SQL:
GO
DECLARE @ColumnName VARCHAR(100),
@SQL NVARCHAR(MAX);
SET @ColumnName = 'FirstName';
EXECUTE ('SELECT [Title],@ColumnName FROM Person.Person');
GO
—In the case above @ColumnName is nowhere to be found, therefore we can either do:
EXECUTE ('SELECT [Title],' +@ColumnName+ ' FROM Person.Person');
or
GO
DECLARE @ColumnName VARCHAR(100),
@SQL NVARCHAR(MAX);
SET @ColumnName = 'FirstName';
SET @SQL = 'SELECT ' + @ColumnName + ' FROM Person.Person';
EXEC sys.sp_executesql @SQL
GO
answered Sep 15, 2022 at 10:39
Give a ‘GO’ after the end statement and select all the statements then execute
answered Dec 29, 2021 at 15:23
1
@RowFrom int
@RowTo int
are both Global Input Params for the Stored Procedure, and since I am compiling the SQL query inside the Stored Procedure with T-SQL then using Exec(@sqlstatement) at the end of the stored procedure to show the result, it gives me this error when I try to use the @RowFrom or @RowTo inside the @sqlstatement variable that is executed.. it works fine otherwise.. please help.
"Must declare the scalar variable "@RowFrom"."
Also, I tried including the following in the @sqlstatement variable:
'Declare @Rt int'
'SET @Rt = ' + @RowTo
but @RowTo still doesn’t pass its value to @Rt and generates an error.
hofnarwillie
3,54310 gold badges47 silver badges73 bronze badges
asked Aug 24, 2011 at 20:39
1
You can’t concatenate an int to a string. Instead of:
SET @sql = N'DECLARE @Rt int; SET @Rt = ' + @RowTo;
You need:
SET @sql = N'DECLARE @Rt int; SET @Rt = ' + CONVERT(VARCHAR(12), @RowTo);
To help illustrate what’s happening here. Let’s say @RowTo = 5.
DECLARE @RowTo int;
SET @RowTo = 5;
DECLARE @sql nvarchar(max);
SET @sql = N'SELECT ' + CONVERT(varchar(12), @RowTo) + ' * 5';
EXEC sys.sp_executesql @sql;
In order to build that into a string (even if ultimately it will be a number), I need to convert it. But as you can see, the number is still treated as a number when it’s executed. The answer is 25, right?
In your case you can use proper parameterization rather than use concatenation which, if you get into that habit, you will expose yourself to SQL injection at some point (see this and this:
SET @sql = @sql + ' WHERE RowNum BETWEEN @RowFrom AND @RowTo;';
EXEC sys.sp_executesql @sql,
N'@RowFrom int, @RowTo int',
@RowFrom, @RowTo;
answered Aug 24, 2011 at 21:01
Aaron BertrandAaron Bertrand
268k36 gold badges457 silver badges485 bronze badges
4
You can also get this error message if a variable is declared before a GOand referenced after it.
See this question and this workaround.
answered Mar 25, 2019 at 22:11
Pierre CPierre C
2,43132 silver badges31 bronze badges
Just FYI, I know this is an old post, but depending on the database COLLATION settings you can get this error on a statement like this,
SET @sql = @Sql + ' WHERE RowNum BETWEEN @RowFrom AND @RowTo;';
if for example you typo the S in the
SET @sql = @***S***ql
sorry to spin off the answers already posted here, but this is an actual instance of the error reported.
Note also that the error will not display the capital S in the message, I am not sure why, but I think it is because the
Set @sql =
is on the left of the equal sign.
answered Apr 1, 2015 at 19:13
htm11hhtm11h
1,7198 gold badges46 silver badges103 bronze badges
0
Sometimes, if you have a ‘GO’ statement written after the usage of the variable, and if you try to use it after that, it throws such error. Try removing ‘GO’ statement if you have any.
answered May 24, 2021 at 6:12
This is most likely not an answer to the issue itself, but this question pops up as first result when searching for Sql declare scalar variable hence I want to share a possible solution to this error.
In my case this error was caused by the use of ; after a SQL statement. Just remove it and the error will be gone.
I guess the cause is the same as @IronSean already posted in a comment above:
it’s worth noting that using GO (or in this case 😉 causes a new branch where declared variables aren’t visible past the statement.
For example:
DECLARE @id int
SET @id = 78
SELECT * FROM MyTable WHERE Id = @var; <-- remove this character to avoid the error message
SELECT * FROM AnotherTable WHERE MyTableId = @var
answered Nov 5, 2020 at 16:25
ViRuSTriNiTyViRuSTriNiTy
4,9122 gold badges31 silver badges56 bronze badges
5
Just adding what fixed it for me, where misspelling is the suspect as per this MSDN blog…
When splitting SQL strings over multiple lines, check that that you are comma separating your SQL string from your parameters (and not trying to concatenate them!) and not missing any spaces at the end of each split line. Not rocket science but hope I save someone a headache.
For example:
db.TableName.SqlQuery(
"SELECT Id, Timestamp, User " +
"FROM dbo.TableName " +
"WHERE Timestamp >= @from " +
"AND Timestamp <= @till;" + [USE COMMA NOT CONCATENATE!]
new SqlParameter("from", from),
new SqlParameter("till", till)),
.ToListAsync()
.Result;
EBH
10.3k3 gold badges32 silver badges58 bronze badges
answered Jun 21, 2017 at 15:46
Tim TylerTim Tyler
2,1612 gold badges17 silver badges12 bronze badges
1
Case Sensitivity will cause this problem, too.
@MyVariable and @myvariable are the same variables in SQL Server Man. Studio and will work. However, these variables will result in a «Must declare the scalar variable «@MyVariable» in Visual Studio (C#) due to case-sensitivity differences.
answered Jun 9, 2016 at 11:20
Just an answer for future me (maybe it helps someone else too!). If you try to run something like this in the query editor:
USE [Dbo]
GO
DECLARE @RC int
EXECUTE @RC = [dbo].[SomeStoredProcedure]
2018
,0
,'arg3'
GO
SELECT month, SUM(weight) AS weight, SUM(amount) AS amount
FROM SomeTable AS e
WHERE year = @year AND type = 'M'
And you get the error:
Must declare the scalar variable «@year»
That’s because you are trying to run a bunch of code that includes BOTH the stored procedure execution AND the query below it (!). Just highlight the one you want to run or delete/comment out the one you are not interested in.
marc_s
721k173 gold badges1320 silver badges1442 bronze badges
answered Jul 21, 2019 at 18:05
saiyancodersaiyancoder
1,2551 gold badge13 silver badges20 bronze badges
If someone else comes across this question while no solution here made my sql file working, here’s what my mistake was:
I have been exporting the contents of my database via the ‘Generate Script’ command of Microsofts’ Server Management Studio and then doing some operations afterwards while inserting the generated data in another instance.
Due to the generated export, there have been a bunch of «GO» statements in the sql file.
What I didn’t know was that variables declared at the top of a file aren’t accessible as far as a GO statement is executed. Therefore I had to remove the GO statements in my sql file and the error «Must declare the scalar variable xy» was gone!
answered Oct 19, 2020 at 10:33
pburpbur
656 bronze badges
As stated in https://learn.microsoft.com/en-us/sql/t-sql/language-elements/sql-server-utilities-statements-go?view=sql-server-ver16 , the scope of a user-defined variable is batch dependent .
—This will produce the error
GO
DECLARE @MyVariable int;
SET @MyVariable = 1;
GO --new batch of code
SELECT @MyVariable--CAST(@MyVariable AS
int);
GO
—This will not produce the error
GO
DECLARE @MyVariable int;
SET @MyVariable = 1;
SELECT @MyVariable--CAST(@MyVariable AS int);
GO
We get the same error when we try to pass a variable inside a dynamic SQL:
GO
DECLARE @ColumnName VARCHAR(100),
@SQL NVARCHAR(MAX);
SET @ColumnName = 'FirstName';
EXECUTE ('SELECT [Title],@ColumnName FROM Person.Person');
GO
—In the case above @ColumnName is nowhere to be found, therefore we can either do:
EXECUTE ('SELECT [Title],' +@ColumnName+ ' FROM Person.Person');
or
GO
DECLARE @ColumnName VARCHAR(100),
@SQL NVARCHAR(MAX);
SET @ColumnName = 'FirstName';
SET @SQL = 'SELECT ' + @ColumnName + ' FROM Person.Person';
EXEC sys.sp_executesql @SQL
GO
answered Sep 15, 2022 at 10:39
Give a ‘GO’ after the end statement and select all the statements then execute
answered Dec 29, 2021 at 15:23
1
Цитата из документации Database Identifiers:
Rules for Regular Identifiers
- Embedded spaces or special characters are not allowed.
В именах параметров не разрешены пробелы.
Уберём из них пробелы и квадратные скобки.
Попутно исправим другие ошибки и недочёты: имя соединения (у вас оно почему-то названо connectionString), опасность потери ресурсов (используем using для устранения этого), использование устаревшего и опасного метода AddWithValue (заменим его на Add с указанием точного типа).
Кроме того, время_входаTextBox используется дважды. Будьте внимательны!
string sql = @"UPDATE Табель SET Статус = @Статус, [Код смены] = @КодСмены, [Время входа] = @ВремяВхода, [Время выхода] = @ВремяВыхода WHERE [Код сотрудника] = @КодСотрудника AND Дата = @Дата AND [Код табеля] = @КодТабеля";
using var connection = new SqlConnection(_connectionString);
connection.Open();
using var command = new SqlCommand(sql, connection);
command.Parameters.Add("КодТабеля", SqlDbType.Int).Value = textBox5.Text; // int.Parse(textBox5.Text)
command.Parameters.Add("КодСотрудника", SqlDbType.Int).Value = код_сотрудникаTextBox.Text;
command.Parameters.Add("Дата", SqlDbType.DateTime2).Value = датаDateTimePicker.Value;
command.Parameters.Add("Статус", SqlDbType.NVarChar).Value = comboBox1.Text;
command.Parameters.Add("КодСмены", SqlDbType.Int).Value = comboBox2.Text;
command.Parameters.Add("ВремяВхода", SqlDbType.DateTime2).Value = время_входаTextBox.Text;
command.Parameters.Add("ВремяВыхода", SqlDbType.DateTime2).Value = время_выходаTextBox.Text;
command.ExecuteNonQuery();
Благодаря использованию using соединение будет гарантировано закрыто, даже в случае возникновения исключения. Вызывать метод Close() не нужно.
Я не знаю, какие именно типы используются у вас в таблице, поэтому сами укажите правильные типы SqlDbType. При необходимости примените int.Parse и тому подобные методы. А лучше используйте NumericUpDown для ввода чисел вместо TextBox.
Почему не стоит использовать метод AddWithValue:
Can we stop using AddWithValue() already?
AddWithValue is evil!
AddWithValue is Evil
Достаточно прочитать любую из этих статей.
Вы не можете объединить int со строкой. Вместо того:
SET @sql = N'DECLARE @Rt int; SET @Rt = ' + @RowTo;
Вам потребуется:
SET @sql = N'DECLARE @Rt int; SET @Rt = ' + CONVERT(VARCHAR(12), @RowTo);
Чтобы проиллюстрировать, что здесь происходит. Допустим, @RowTo = 5.
DECLARE @RowTo int;
SET @RowTo = 5;
DECLARE @sql nvarchar(max);
SET @sql = N'SELECT ' + CONVERT(varchar(12), @RowTo) + ' * 5';
EXEC sys.sp_executesql @sql;
Чтобы превратить это в строку (даже если в конечном итоге это будет число), мне нужно преобразовать его. Но, как видите, число по-прежнему обрабатывается как число при выполнении. Ответ 25, не так ли?
В вашем случае вам действительно не нужно повторно объявлять @Rt и т. Д. Внутри строки @sql, вам просто нужно сказать:
SET @sql = @sql + ' WHERE RowNum BETWEEN '
+ CONVERT(varchar(12), @RowFrom) + ' AND '
+ CONVERT(varchar(12), @RowTo);
Хотя было бы лучше иметь правильную параметризацию, например
SET @sql = @sql + ' WHERE RowNum BETWEEN @RowFrom AND @RowTo;';
EXEC sys.sp_executesql @sql,
N'@RowFrom int, @RowTo int',
@RowFrom, @RowTo;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 |
--МАССОВАЯ ВСТАВКА НЕСКОЛЬКИХ ФАЙЛОВ_CSV ИЗ ПАПКИ --Пока что ругается на курсор IF (OBJECT_ID('tempdb..#csv_temp') IS NOT NULL) DROP TABLE #csv_temp; CREATE TABLE #csv_temp( [Times] VARCHAR (100), [Caller_Name] INT, [Caller_Number] INT, [Callee_Name] INT, [Callee_Numbers] VARCHAR(100), [DOD] VARCHAR(100), [DID] VARCHAR(100), [Call_Duration_(s)] INT, [Talk_Duration_(s)] INT, [STATUS] VARCHAR(100), [Source_Trunk] VARCHAR(50), [Destination_Trunk] VARCHAR(100), [Communication_Type] VARCHAR(100), [PIN_Code] VARCHAR(10), [Caller_IP_Address] VARCHAR(200), [Cost] VARCHAR(100), [Billing_Account] VARCHAR(100) ) -- Переменые DECLARE @filename VARCHAR(255), @path VARCHAR(255), @SQL VARCHAR(8000), @cmd VARCHAR(1000), @Times VARCHAR(1000), @Caller_Name INT, @Caller_Number INT, @Callee_Name INT, @Callee_Numbers VARCHAR(100), @DOD VARCHAR(100), @DID VARCHAR(100), @Call_Duration_(s)INT, @Talk_Duration_(s) INT, @STATUS VARCHAR(100), @Source_Trunk VARCHAR(50), @Destination_Trunk VARCHAR(100), @Communication_Type VARCHAR(100), @PIN_Code VARCHAR(10), @Caller_IP_Address VARCHAR(200), @Cost VARCHAR(100), @Billing_Account VARCHAR(100) --получить список файлов для обработки: SET @path = 'C:serg' SET @cmd = 'dir ' + @path + '*.csv /b' INSERT INTO #csv_temp ( [Times] , [Caller_Name], [Caller_Number], [Callee_Name], [Callee_Numbers] , [DOD], [DID], [Call_Duration_(s)], [Talk_Duration_(s)], [Status], [Source_Trunk], [Destination_Trunk], [Communication_Type], [PIN_Code], [Caller_IP_Address], [Cost], [Billing_Account] ) --SELECT '17' VALUES ( (SELECT [Times] FROM #csv_temp ), (SELECT [Caller_Name] FROM #csv_temp ), (SELECT [Caller_Number] FROM #csv_temp ), (SELECT [Callee_Name] FROM #csv_temp ), (SELECT [Callee_Numbers] FROM #csv_temp ), (SELECT [DOD] FROM #csv_temp ), (SELECT [DID] FROM #csv_temp ), (SELECT [Call_Duration_(s)] FROM #csv_temp ), (SELECT [Talk_Duration_(s)] FROM #csv_temp ), (SELECT [Status] FROM #csv_temp ), (SELECT [Source_Trunk] FROM #csv_temp ), (SELECT [Destination_Trunk] FROM #csv_temp ), (SELECT [Communication_Type] FROM #csv_temp ), (SELECT [PIN_Code] FROM #csv_temp ), (SELECT [Caller_IP_Address] FROM #csv_temp ), (SELECT [Cost] FROM #csv_temp ), (SELECT [Billing_Account] FROM #csv_temp ) ) EXEC Master..xp_cmdShell @cmd UPDATE #csv_temp SET [Times] = @path where [Times] is null -- Курсор declare c1 cursor for SELECT [Times], [Caller_Name], [Caller_Number], [Callee_Name], [Callee_Numbers] , [DOD], [DID], [Call_Duration_(s)], [Talk_Duration_(s)], [Status], [Source_Trunk], [Destination_Trunk], [Communication_Type], [PIN_Code], [Caller_IP_Address], [Cost], [Billing_Account] FROM #csv_temp where Times like '%.csv%' --Открываем Курсор open c1 --- выборка данных ---fetch next from c1 into @path,@filename fetch next from c1 into @Times,@Caller_Name,@Caller_Number, @Callee_Name,@Callee_Numbers,@DOD,@DID,@Call_Duration_(s), @Talk_Duration_(s),@Status,@Source_Trunk,@Destination_Trunk, @Communication_Type,@PIN_Code,@Caller_IP_Address,@Cost,@Billing_Account While @@fetch_status <> -1 begin --bulk insert won't take a variable name, so make a SQL AND EXECUTE it instead: SET @SQL = 'BULK INSERT #csv_temp FROM ''' + @path + @filename + ''' ' + ' WITH ( FIELDTERMINATOR = '';'', ROWTERMINATOR = ''0x0a'', FIRSTROW = 2 ) ' -- Вывод результата print @SQL EXEC (@SQL) -- fetch NEXT FROM c1 INTO @path,@filename END close c1 deallocate c1 -------------------------------------------------------------------------------------- |
Необходимо объявить скалярную переменную
@RowFrom int
@RowTo int
являются ли оба глобальных входных параметра для хранимой процедуры, и поскольку я компилирую SQL-запрос внутри хранимой процедуры с помощью T-SQL, то с помощью Exec(@sqlstatement) в конце хранимой процедуры, чтобы показать результат, он дает мне эту ошибку, когда я пытаюсь использовать @RowFrom или @RowTo внутри @sqlstatement переменной, которая выполняется.. в противном случае он отлично работает.. пожалуйста помочь.
"Must declare the scalar variable "@RowFrom"."
кроме того, я попытался включить следуя в @sqlstatement переменной:
'Declare @Rt int'
'SET @Rt = ' + @RowTo
но @RowTo по-прежнему не пропускает его значение @Rt и выдает ошибку.
2375
4
4 ответов:
вы не можете объединить int в строку. Вместо:
SET @sql = N'DECLARE @Rt INT; SET @Rt = ' + @RowTo;вам нужно:
SET @sql = N'DECLARE @Rt INT; SET @Rt = ' + CONVERT(VARCHAR(12), @RowTo);чтобы проиллюстрировать, что происходит здесь. Допустим, @RowTo = 5.
DECLARE @RowTo INT; SET @RowTo = 5; DECLARE @sql NVARCHAR(MAX); SET @sql = N'SELECT ' + CONVERT(VARCHAR(12), @RowTo) + ' * 5'; EXEC sp_executeSQL @sql;чтобы построить это в строку (даже если в конечном итоге это будет число), мне нужно преобразовать его. Но, как вы можете видеть, число по-прежнему рассматривается как число, когда оно выполняется. Ответ 25, верно?
в вашем случае вам действительно не нужно повторно объявлять @Rt так далее. внутри строки @sql вам просто нужно сказать:
SET @sql = @sql + ' WHERE RowNum BETWEEN ' + CONVERT(VARCHAR(12), @RowFrom) + ' AND ' + CONVERT(VARCHAR(12), @RowTo);хотя было бы лучше иметь правильную параметризацию, например
SET @sql = @sql + ' WHERE RowNum BETWEEN @RowFrom AND @RowTo;'; EXEC sp_executesql @sql, N'@RowFrom INT, @RowTo INT', @RowFrom, @RowTo;
просто FYI, я знаю, что это старый пост, но в зависимости от настроек сортировки базы данных вы можете получить эту ошибку в таком заявлении,
SET @sql = @Sql + ' WHERE RowNum BETWEEN @RowFrom AND @RowTo;';если, например, вы опечатка S в
SET @sql = @***S***qlизвините, что отклонил ответы, уже опубликованные здесь, но это фактический экземпляр сообщения об ошибке.
обратите внимание также, что ошибка не будет отображать капитал S в сообщении, я не уверен, почему, но я думаю, что это потому, что элемент
Set @sql =слева от знака равенства.
просто добавляя то, что исправлено для меня, где опечатка является подозреваемым согласно это блог MSDN…
при разбиении строк SQL на несколько строк проверьте, что вы запятая, отделяющая вашу строку SQL от ваших параметров (и не пытаетесь их объединить!) и не пропуская пробелы в конце каждой строки. Не ракетостроение, но надеюсь, что я спасу кого-то от головной боли.
например:
db.TableName.SqlQuery( "SELECT Id, Timestamp, User " + "FROM dbo.TableName " + "WHERE Timestamp >= @from " + "AND Timestamp <= @till;" + [USE COMMA NOT CONCATENATE!] new SqlParameter("from", from), new SqlParameter("till", till)), .ToListAsync() .Result;
чувствительность к регистру также вызовет эту проблему.
@MyVariable и @myvariable-это одни и те же переменные в SQL Server Man. Студия так и будет работать. Однако эти переменные приведут к тому, что «необходимо объявить скалярную переменную» @MyVariable » в Visual Studio (C#) из-за различий в чувствительности к регистру.
Задача: разделить данные за сегодня и вчера по столбцам. Если использовать этот запрос без переменных то все работает. Анализ синтаксиса в excel пишет «необходимо объявить скалярную переменную @today» хотя я вроде его объявил в начале Используется MSSQL 2016
DECLARE @today as Date, @yesterday as Date;
Set @today = convert(date, getdate());
Set @yesterday = convert(date, dateadd(day, -1, getdate()));
SELECT n.Name, o.Created,
Count(DISTINCT(CASE WHEN Status = 'N' And o.Date = @today Then ID END)) as NewQ,
Count(DISTINCT(CASE WHEN Status = 'N' And o.Date = @yesterday Then ID END)) as YdNewQ,
Count(DISTINCT(CASE WHEN Status = 'W' And o.Date = @today Then ID END)) as WaitingQ,
Count(DISTINCT(CASE WHEN Status = 'W' And o.Date = @yesterday Then ID END)) as YDWaitingQ,
Count(DISTINCT(CASE WHEN Status = 'U' And o.Date = @today Then ID END)) as ProblemQ,
Count(DISTINCT(CASE WHEN Status = 'U' And o.Date = @yesterday Then ID END)) as YdProblemQ,
Count(DISTINCT(CASE WHEN Status = 'Z' And o.Date = @today Then ID END)) as CancelledQ,
Count(DISTINCT(CASE WHEN Status = 'Z' And o.Date = @yesterday Then ID END)) as YdCancelledQ
FROM Orders i
LEFT JOIN OrderItems o ON o.OrderID = i.ID
LEFT JOIN NomenclUS m ON m.ID = o.ProductID
WHERE i.Status <> 'Z' AND o.Created >= dateadd(day, -2, getdate())
GROUP BY o.Created, n.CatID, n.CatName
ORDER BY o.Created, n.CatID
Как «объявить скалярную переменную» в представлении в Sql Server (2005)
Im пытается создать посмотреть на SQL Server 2005.
код SQL работает как таковой (Im использует его в VS2008), но в SQL Server Im не удается сохранить его, так как появляется сообщение об ошибке «объявить скалярную переменную @StartDate» и «объявить скалярную переменную @EndDate».
и мой вопрос конечно — как именно я должен заявить о них?
Я попытался поставить после первого в коде:
но это не сделало трюк, как я и ожидал — он только дал мне еще одно всплывающее сообщение:
» конструкция или инструкция Declare cursor SQL не поддерживается.»
4 ответов
Как упоминал Алекс К, вы должны написать его как встроенную табличную функцию. Вот это статьи что описывает об этом.
короче говоря, синтаксис будет чем-то вроде
у вас может быть один запрос выбора (каким бы сложным он ни был, можно использовать CTE). И тогда вы будете использовать его как
Если посмотреть вы имеете в виду собственное представление SQL Server ( CREATE VIEW . ), то вы не можете использовать локальные переменные вообще (вместо этого вы бы использовали табличное значение udf).
Если вы имеете в виду что-то другое, то добавляем DECLARE @StartDate DATETIME, @EndDate DATETIME делает этот оператор разбором отлично, это вся SQL?
вот пример запроса, который использует CTE для хорошей эмуляции внутренней конструкции переменных. Вы можете протестировать его в своей версии SQL Server.
и через CROSS APPLY
попробуйте заменить все ваши @X, @Y на A. X и A. Y, добавьте в свой код: Из (выберите X = ‘literalX’, Y = ‘literalY’) A тогда вы поместили все свои литералы в одно место и имеете только одну их копию.
Просто о Transact-SQL
SQL (Structured Query Language) — это универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных (язык структурированных запросов).
SQL в его исходном виде является информационно-логическим языком, а не языком программирования, но вместе SQL предусматривает возможность его процедурных расширений, с учётом которых язык уже вполне может рассматриваться в качестве языка программирования.
В настоящее время широко распространенны следующие спецификации SQL:
| Тип базы данных | Спецификация SQL |
| Microsoft SQL | Transact-SQL |
| Microsoft Jet/Access | Jet SQL |
| MySQL | SQL/PSM (SQL/Persistent Stored Module) |
| Oracle | PL/SQL (Procedural Language/SQL) |
| IBM DB2 | SQL PL (SQL Procedural Language) |
| InterBase/Firebird | PSQL (Procedural SQL) |
Базы данных и спецификации SQL
В данной статье будет рассмотрена спецификация Transact-SQL, которая используется серверами Microsoft SQL. А так как база у всех спецификаций SQL одинаковая, то большинство команд и сценариев с легкостью переносятся на другие типы SQL.
Transact-SQL — это процедурное расширение языка SQL компаний Microsoft. SQL был расширен такими дополнительными возможностями как:
- управляющие операторы,
- локальные и глобальные переменные,
- различные дополнительные функции для обработки строк, дат, математики и т.п.,
- поддержка аутентификации Microsoft Windows
Язык Transact-SQL является ключом к использованию SQL Server. Все приложения, взаимодействующие с экземпляром SQL Server, независимо от их реализации и пользовательского интерфейса, отправляют серверу инструкции Transact-SQL.
Для того, чтобы усвоить теоретический материал, его, конечно же, нужно применить на практике. Для практических занятий создадим базу данных и заполним ее небольшим количеством значений.
Итак, чтобы создать базу данных и заполнить ее значениями, необходимо открыть консоль выполнения команд и запросов SQL сервера и выполнить следующий сценарий:
В результате работы сценария на SQL сервере будет создана база данных TestDatabase с пятью пользовательскими таблицами: Users, Departments, Positions, Local Customers, Local Orders.
Users
| UserID | UserName | UserSurname | DepartmentID | PositionID |
| 1 | Ivan | Petrov | 1 | 1 |
| 2 | Ivan | Sidorov | 1 | 2 |
| 3 | Petr | Ivanov | 1 | 3 |
| 4 | Nikolay | Petrov | 1 | 3 |
| 5 | Nikolay | Ivanov | 2 | 1 |
| 6 | Sergey | Sidorov | 2 | 3 |
| 7 | Andrey | Bukin | 2 | 3 |
| 8 | Viktor | Rybakov | 4 | 1 |
| PositionID | PositionName | BaseSalary |
| 1 | Manager | 1000 |
| 2 | Senior analyst | 650 |
| 3 | Analyst | 400 |
Positions Local Orders
| OrderID | CustomerID | UserID | Description |
| 1 | 1 | 1 | Special parts |
| DepartmentID | DepartmentName |
| 1 | Production |
| 2 | Distribution |
| 3 | Purchasing |
Departments Local Customers
| CustomerID | CustomerName | CustomerAddress |
| 1 | Alex Company | 606443, Russia, Bor, Lenina str., 15 |
| 2 | Potrovka | 115516, Moscow, Promyshlennaya str., 1 |
Директивы сценария — это специфические команды, которые используются только в MS SQL. Эти команды помогают серверу определять правила работы со скриптом и транзакциями. Типичные представители: GO — сигнализирует SQL-серверу об окончании сценария, EXEC (или EXECUTE) — выполняет процедуру или скалярную функцию.
Комментарии используются для создания пояснений для блоков сценариев, а также для временного отключения команд при отладке скрипта. Комментарии бывают как строковыми, так и блоковыми:
- — — строковый комментарий исключает из выполнения только одну строку, перед которой стоят два минуса.
- /* */ — блоковый комментарий исключает из выполнения целый блок команд, заключенный в указанную конструкцию.
Как и в языках программирования, в SQL существуют различные типы данных для хранения переменных:
- Числа — для хранения числовых переменных (int, tinyint, smallint, bigint, numeric, decimal, money, smallmoney, float, real).
- Даты — для хранения даты и времени (datetime, smalldatetime).
- Символы — для хранения символьных данных (char, nchar, varchar, nvarchar).
- Двоичные — для хранения бинарных данных (binary, varbinary, bit).
- Большеобъемные — типы данных для хранения больших бинарных данных (text, ntext, image).
- Специальные — указатели (cursor), 16-байтовое шестнадцатиричное число, которое используется для GUID (uniqueidentifier), штамп изменения строки (timestamp), версия строки (rowversion), таблицы (table).
Идентификаторы — это специальные символы, которые используются с переменными для идентифицирования их типа или для группировки слов в переменную. Типы идентификаторов:
- @ — идентификатор локальной переменной (пользовательской).
- @@ — идентификатор глобальной переменной (встроенной).
- # — идентификатор локальной таблицы или процедуры.
- ## — идентификатор глобальной таблицы или процедуры.
- [ ] — идентификатор группировки слов в переменную.
Переменные используются в сценариях и для хранения временных данных. Чтобы работать с переменной, ее нужно объявить, притом объявление должно быть осуществлено в той транзакции, в которой выполняется команда, использующая эту переменную. Иначе говоря, после завершения транзакции, то есть после команды GO, переменная уничтожается.
Объявление переменной выполняется командой DECLARE, задание значения переменной осуществляется либо командой SET, либо SELECT:
Операторы — это специальные команды, предназначенные для выполнения простых операций над переменными:
- Арифметические операторы: «*» — умножить, «/» — делить, «%» — модуль от деления, «+» — сложить , «-» — вычесть, «()» — скобки.
- Операторы сравнения: «=» — равно, «>» — больше, «<» — меньше, «>=» — больше или равно, «<=» меньше или равно, «<>» — не равно.
- Операторы соединения: «+» — соединение строк.
- Логические операторы: «AND» — и, «OR» — или , «NOT» — не.
Спецификация Transact-SQl значительно расширяет стандартные возможности SQL благодаря встроенным функциям:
- Агрегативные функции- функции, которые работают с коллекциями значений и выдают одно значение. Типичные представители: AVG — среднее значение колонки, SUM — сумма колонки, MAX — максимальное значение колонки, COUNT — количество элементов колонки.
- Скалярные функции- это функции, которые возвращают одно значение, работая со скалярными данными или вообще без входных данных. Типичные представители: DATEDIFF — разница между датами, ABS — модуль числа, DB_NAME — имя базы данных, USER_NAME — имя текущего пользователя, LEFT — часть строки слева.
- Функции-указатели- функции, которые используются как ссылки на другие данные. Типичные представители: OPENXML — указатель на источник данных в виде XML-структуры, OPENQUERY — указатель на источник данных в виде другого запроса.
Выражение — это комбинация символов и операторов, которая получает на вход скалярную величину, а на выходе дает другую величину или исполняет какое-то действие. В Transact-SQL выражения делятся на 3 типа: DDL, DCL и DML.
- DDL (Data Definition Language)- используются для создания объектов в базе данных. Основные представители данного класса: CREATE — создание объектов, ALTER — изменение объектов, DROP — удаление объектов.
- DCL (Data Control Language)- предназначены для назначения прав на объекты базы данных. Основные представители данного класса: GRANT — разрешение на объект, DENY — запрет на объект, REVOKE — отмена разрешений и запретов на объект.
- DML (Data Manipulation Language)- используются для запросов и изменения данных. Основные представители данного класса: SELECT — выборка данных, INSERT — вставка данных, UPDATE — изменение данных, DELETE — удаление данных.
В Transact-SQL существуют специальные команды, которые позволяют управлять потоком выполнения сценария, прерывая его или направляя в нужную логику.
- Блок группировки — структура, объединяющая список выражений в один логический блок (BEGIN … END).
- Блок условия — структура, проверяющая выполнения определенного условия (IF … ELSE).
- Блок цикла — структура, организующая повторение выполнения логического блока (WHILE … BREAK … CONTINUE).
- Переход — команда, выполняющая переход потока выполнения сценария на указанную метку (GOTO).
- Задержка — команда, задерживающая выполнение сценария (WAITFOR)
- Вызов ошибки — команда, генерирующая ошибку выполнения сценария (RAISERROR)
Итак, поняв основы Transact-SQL и попрактиковавшись на простых примерах, можно перейти к более сложным структурам. Обычно базы данных создаются и заполняются с помощью сценариев (скриптов) — хотя визуальный редактор прост в обращении, но им никогда быстро и без недочетов не создашь большую базу данных и не заполнишь ее данными. Если вспомнить начало статьи, то опытная база данных как раз создавалась и заполнялась с помощью сценария. Сценарий — это одно или более выражений, объединенных в логический блок, которые автоматизируют работу администратора.
Обычно сценарии пишутся как универсальное средство для выполнения стандартных задач, поэтому в них применяется динамическое конструирование логики — в запросы и команды вставляются переменные, а не конкретные названия объектов, что позволяет быстро изменять параметры скрипта.
В языках SQL выборка данных из таблиц осуществляется с помощью команды SELECT:
По умолчанию в команде SELECT используется параметр ALL, который можно не указывать. Если в команде указать параметр DISTINCT, то в результат попадут только уникальные (неповторяющиеся) записи из выборки.
Для того, чтобы изменить имена объектов в командах к SQL-серверу, используется команда AS. Использование этой команды помогает сокращать длину строки запроса, а так же получать результат в более удобочитаемом виде.
| CustomerID | CustomerName | CustomerAddress |
|---|---|---|
| 1 | Alex Company | 606443, Russia, Bor, Lenina str., 15′) |
| 2 | Potrovka | 115516, Moscow, Promyshlennaya str., 1 |
| Department Name |
|---|
| Production |
| Distribution |
| Purchasing |
| UserName |
|---|
| Andrey |
| Ivan |
| Nikolay |
| Petr |
| Sergey |
| Viktor |
Фильтрация данных осуществляется с помощью команды WHERE, в которой используются следующие операторы и команды сравнения: =, <, >, <=, >=, <>, LIKE, NOT LIKE, AND, OR, NOT, BETWEEN, NOT BETWEEN, IN, NOT IN, IS NULL, IS NOT NULL. В общем виде команда SELECT с фильтром выглядит так:
В строке сравнения разрешается использовать подстановочные символы:
- % — любое количество символов;
- _ — один символ;
- [] — любой символ, указанный в скобках;
- [^] — любой символ, не указанный в скобках.
Фильтрация позволяет использовать подзапросы, то есть конструировать запрос из нескольких подзапросов:
| PositionID |
|---|
| 3 |
| UserID | UserName | UserSurname | DepartmentID | PositionID |
|---|---|---|---|---|
| 4 | Nikolay | Petrov | 1 | 3 |
| 6 | Sergey | Sidorov | 2 | 3 |
| UserID | UserName | UserSurname | DepartmentID | PositionID |
|---|---|---|---|---|
| 1 | Ivan | Petrov | 1 | 1 |
| 2 | Ivan | Sidorov | 1 | 2 |
| 7 | Andrey | Bukin | 2 | 2 |
| Department name | Summary salary |
|---|---|
| Production | 2700.0000 |
Для сортировки данных в выборке используется командаORDER BY, но следует учесть, что эта команда не сортирует данные типа text, ntext и image. По умолчанию сортировка производится по возрастанию, поэтому параметр ASC в этом случае можно не указывать:
Для того, чтобы ограничить количество строк в результате запроса, используется командаTOP:
Внутри запроса можно проводить вычисления над полученными данными. Для этого используюся функции агрегирования:
- AVG(колонка) — среднее значение колонки;
- COUNT(колонка) — количество не NULL элементов колонки;
- COUNT(*) — количество элементов запроса;
- MAX(колонка) — максимальное значение в колонке;
- MIN(колонка) — минимальное значение в колонке;
- SUM(колонка) — сумма значений в колонке.
Примеры использования команд ORDER, TOP и функций агрегирования:
| UserName |
|---|
| Andrey |
| Ivan |
| Nikolay |
| UserID | UserName | UserSurname | DepartmentID | PositionID |
|---|---|---|---|---|
| 1 | Ivan | Petrov | 1 | 1 |
| 2 | Ivan | Sidorov | 1 | 2 |
| (No column name) |
|---|
| 1000.0000 |
| PositionID | PositionName | BaseSalary |
|---|---|---|
| 1 | Manager | 1000.0000 |
| UserID | UserName | UserSurname | DepartmentID | PositionID |
|---|---|---|---|---|
| 1 | Ivan | Petrov | 1 | 1 |
| 5 | Nikolay | Ivanov | 2 | 1 |
| 8 | Viktor | Rybakov | 4 | 1 |
| (No column name) |
|---|
| 3 |
SQL позволяет производить группировку данных по определенным полям таблицы. Чтобы сгруппировать данные по какому-нибудь параметру, в SQL-запросе необходимо написать команду GROUP BY, в которой указать имя колонки, по которой производится группировка. Колонки, упомянутые в команде GROUP BY, должны присутствовать в команде SELECT, а так же команда SELECT должна содержать функцию агрегирования, которая будет применена к сгруппированным данным.
| DepartmentID | Number of users |
|---|---|
| 1 | 4 |
| 2 | 3 |
| 4 | 1 |
Чтобы отфильтровать строки в запросе с группировкой применяется специальная команда HAVING, в которой указывается условие фильтрации. Колонки, по которым производится фильтрация, должны присутствовать в команде GROUP BY. Команда HAVING может использоваться и без GROUP BY, в этом случае она работает аналогично команде WHERE, но она разрешает применять в условиях фильтрации только функции агрегирования.
| DepartmentID | Number of users |
|---|---|
| 1 | 4 |
Команда группировки может дополняться оператором WITH ROLLUP, который дополняет результат группировки сводной строкой с суммой значений колонок.
| DepartmentID | Number of users |
|---|---|
| 1 | 4 |
| 2 | 3 |
| 4 | 1 |
| NULL | 8 |
| DepartmentID | PositionID | Number of users |
|---|---|---|
| 1 | 1 | 1 |
| 1 | 2 | 2 |
| 1 | 3 | 1 |
| 1 | NULL | 4 |
| 2 | 1 | 1 |
| 2 | 2 | 1 |
| 2 | 3 | 1 |
| 2 | NULL | 3 |
| 4 | 1 | 1 |
| 4 | NULL | 1 |
| NULL | NULL | 8 |
Команда группировки также может дополняться оператором WITH CUBE, который дополняет формирует всевозможные комбинации из группируемых колонок: если есть N колонок, то получится 2^N комбинаций.
| DepartmentID | PositionID | Number of users |
|---|---|---|
| 1 | 1 | 1 |
| 1 | 2 | 2 |
| 1 | 3 | 1 |
| 1 | NULL | 4 |
| 2 | 1 | 1 |
| 2 | 2 | 1 |
| 2 | 3 | 1 |
| 2 | NULL | 3 |
| 4 | 1 | 1 |
| 4 | NULL | 1 |
| NULL | NULL | 8 |
| NULL | 1 | 3 |
| NULL | 2 | 3 |
| NULL | 3 | 2 |
Функция агрегирования GROUPING позволяет определить, была ли запись добавлена командами ROLLUP и CUBE, или это запись получена из источника данных.
| DepartmentID | Number of users | Added row |
|---|---|---|
| 1 | 4 | 0 |
| 2 | 3 | 0 |
| 4 | 1 | 0 |
| NULL | 8 | 1 |
Еще одна команда группировки COMPUTE позволяет группировать данные и выводить по ним отчет в разные таблицы. То есть команда GROUP BY с операторами ROLLUP и CUBE группирует данные и дописывает в таблицу дополнительны строки с отчетом, а команда COMPUTE группирует данные, разрывая исходную таблицу на несколько подтаблиц, а также формирует подтаблицы с отчетами. Команда COMPUTE может использоваться в двух режимах:
- как простая функция агрегирования, выводящая результат в отдельную таблицу;
- с параметром BY как команда группировки, разрезающая таблицу на несколько подтаблиц
Команда COMPUTE с параметром BY может использоваться только совместно с командой ORDER BY, причем столбцы сортировки должны совпадать со столбцами группировки.
| UserID | UserName | UserSurname | DepartmentID | PositionID |
|---|---|---|---|---|
| 1 | Ivan | Petrov | 1 | 1 |
| 2 | Ivan | Sidorov | 1 | 2 |
| 3 | Petr | Ivanov | 1 | 2 |
| 4 | Nikolay | Petrov | 1 | 3 |
| 5 | Nikolay | Ivanov | 2 | 1 |
| 6 | Sergey | Sidorov | 2 | 3 |
| 7 | Andrey | Bukin | 2 | 2 |
| 8 | Viktor | Rybakov | 4 | 1 |
| cnt |
|---|
| 8 |
| UserID | UserName | UserSurname | DepartmentID | PositionID |
|---|---|---|---|---|
| 1 | Ivan | Petrov | 1 | 1 |
| 2 | Ivan | Sidorov | 1 | 2 |
| 3 | Petr | Ivanov | 1 | 2 |
| 4 | Nikolay | Petrov | 1 | 3 |
| cnt |
|---|
| 4 |
| UserID | UserName | UserSurname | DepartmentID | PositionID |
|---|---|---|---|---|
| 5 | Nikolay | Ivanov | 2 | 1 |
| 6 | Sergey | Sidorov | 2 | 3 |
| 7 | Andrey | Bukin | 2 | 2 |
| cnt |
|---|
| 3 |
| UserID | UserName | UserSurname | DepartmentID | PositionID |
|---|---|---|---|---|
| 8 | Viktor | Rybakov | 4 | 1 |
| cnt |
|---|
| 1 |
Самые важные и нужные запросы в SQL — это с запросы с соединением таблиц, когда выборка осуществляется сразу из нескольких источников. Такие запросы более сложны в написании, но и более удобны в обработке, так как часто выдают в программу уже готовый результат, который остается только вывести на экран.
Соединять таблицы в SQL можно двумя способами: вертикально и горизонтально.
Вертикальное соединение осуществляется командой UNION, которая в конец первой таблицы допишет вторую таблицую. При таком соединении количество колонок соединяемых таблиц должно быть одинаковым, а сами колонки должны иметь одинаковые названия и типы данных. При соединении одинаковые строки, встречающиеся в обоих таблицах, будут удалены, если в команде не указан параметр ALL.
| UserID | UserName | UserSurname | DepartmentID | PositionID |
|---|---|---|---|---|
| 1 | Ivan | Petrov | 1 | 1 |
| 2 | Ivan | Sidorov | 1 | 2 |
| 4 | Nikolay | Petrov | 1 | 3 |
| UserID | UserName | UserSurname | DepartmentID | PositionID |
|---|---|---|---|---|
| 1 | Ivan | Petrov | 1 | 1 |
| 2 | Ivan | Sidorov | 1 | 2 |
| 1 | Ivan | Petrov | 1 | 1 |
| 4 | Nikolay | Petrov | 1 | 3 |
Горизонтальное соединение производится путем сцепки нескольких таблиц по ключевым колонкам. Самое простое горизонтальное соединение выполняется с помощью команды INNER JOIN, которая сцепляет таблицы, выбирая строки по ключевому полю, которое встречается в обоих таблицах.
Чтобы выполнить сцепление по всем полям левой таблицы, независимо, есть ли такие записи в правой таблице, необходимо использовать команду LEFT JOIN. Эта команда соединяет таблицы, выбирая все строки из левой таблицы, а отсутствующие данные правой таблицы заполняются значением NULL.
Команда RIGHT JOIN аналогична предыдущей, разница заключается лишь в том, что она соединяет таблицы, выбирая все строки из правой таблицы, а отсутствующие данные левой таблицы заполняются значением NULL.
Команда FULL JOIN объединяет в себе левое и правое сцепление, то есть она соединяет таблицы, выбирая строки из обоих таблиц, а отсутствующие данные заполняются значением NULL.
Последняя и редкоиспользуемая команда соединения таблиц — это CROSS JOIN. Эта команда сцепляет таблицы без использования ключевого поля, а результат — это комбинация из всевозможных строк исходных таблиц.
Сцепление не ограничивается только двумя таблицами, запрос может содержать несколько команда JOIN, что очень удобно при формировании конечных отчетов. Ниже приведены примеры для всех команд соединения таблиц.
| UserID | UserName | UserSurname | DepartmentID | PositionID | DepartmentID | DepartmentName |
|---|---|---|---|---|---|---|
| 1 | Ivan | Petrov | 1 | 1 | 1 | Production |
| 2 | Ivan | Sidorov | 1 | 2 | 1 | Production |
| 3 | Petr | Ivanov | 1 | 2 | 1 | Production |
| 4 | Nikolay | Petrov | 1 | 3 | 1 | Production |
| 5 | Nikolay | Ivanov | 2 | 1 | 2 | Distribution |
| 6 | Sergey | Sidorov | 2 | 3 | 2 | Distribution |
| 7 | Andrey | Bukin | 2 | 2 | 2 | Distribution |
| UserID | UserName | UserSurname | DepartmentID | PositionID | DepartmentID | DepartmentName |
|---|---|---|---|---|---|---|
| 1 | Ivan | Petrov | 1 | 1 | 1 | Production |
| 2 | Ivan | Sidorov | 1 | 2 | 1 | Production |
| 3 | Petr | Ivanov | 1 | 2 | 1 | Production |
| 4 | Nikolay | Petrov | 1 | 3 | 1 | Production |
| 5 | Nikolay | Ivanov | 2 | 1 | 2 | Distribution |
| 6 | Sergey | Sidorov | 2 | 3 | 2 | Distribution |
| 7 | Andrey | Bukin | 2 | 2 | 2 | Distribution |
| 8 | Viktor | Rybakov | 4 | 1 | NULL | NULL |
| UserID | UserName | UserSurname | DepartmentID | PositionID | DepartmentID | DepartmentName |
|---|---|---|---|---|---|---|
| 1 | Ivan | Petrov | 1 | 1 | 1 | Production |
| 2 | Ivan | Sidorov | 1 | 2 | 1 | Production |
| 3 | Petr | Ivanov | 1 | 2 | 1 | Production |
| 4 | Nikolay | Petrov | 1 | 3 | 1 | Production |
| 5 | Nikolay | Ivanov | 2 | 1 | 2 | Distribution |
| 6 | Sergey | Sidorov | 2 | 3 | 2 | Distribution |
| 7 | Andrey | Bukin | 2 | 2 | 2 | Distribution |
| NULL | NULL | NULL | NULL | NULL | 3 | Purchasing |
| UserID | UserName | UserSurname | DepartmentID | PositionID | DepartmentID | DepartmentName |
|---|---|---|---|---|---|---|
| 1 | Ivan | Petrov | 1 | 1 | 1 | Production |
| 2 | Ivan | Sidorov | 1 | 2 | 1 | Production |
| 3 | Petr | Ivanov | 1 | 2 | 1 | Production |
| 4 | Nikolay | Petrov | 1 | 3 | 1 | Production |
| 5 | Nikolay | Ivanov | 2 | 1 | 2 | Distribution |
| 6 | Sergey | Sidorov | 2 | 3 | 2 | Distribution |
| 7 | Andrey | Bukin | 2 | 2 | 2 | Distribution |
| NULL | NULL | NULL | NULL | NULL | 3 | Purchasing |
| 8 | Viktor | Rybakov | 4 | 1 | NULL | NULL |
| UserID | UserName | UserSurname | DepartmentID | PositionID | DepartmentID | DepartmentName |
|---|---|---|---|---|---|---|
| 1 | Ivan | Petrov | 1 | 1 | 1 | Production |
| 2 | Ivan | Sidorov | 1 | 2 | 1 | Production |
| 3 | Petr | Ivanov | 1 | 2 | 1 | Production |
| 4 | Nikolay | Petrov | 1 | 3 | 1 | Production |
| 5 | Nikolay | Ivanov | 2 | 1 | 1 | Production |
| 6 | Sergey | Sidorov | 2 | 3 | 1 | Production |
| 7 | Andrey | Bukin | 2 | 2 | 1 | Production |
| 8 | Viktor | Rybakov | 4 | 1 | 1 | Production |
| 1 | Ivan | Petrov | 1 | 1 | 2 | Distribution |
| 2 | Ivan | Sidorov | 1 | 2 | 2 | Distribution |
| 3 | Petr | Ivanov | 1 | 2 | 2 | Distribution |
| 4 | Nikolay | Petrov | 1 | 3 | 2 | Distribution |
| 5 | Nikolay | Ivanov | 2 | 1 | 2 | Distribution |
| 6 | Sergey | Sidorov | 2 | 3 | 2 | Distribution |
| 7 | Andrey | Bukin | 2 | 2 | 2 | Distribution |
| 8 | Viktor | Rybakov | 4 | 1 | 2 | Distribution |
| 1 | Ivan | Petrov | 1 | 1 | 3 | Purchasing |
| 2 | Ivan | Sidorov | 1 | 2 | 3 | Purchasing |
| 3 | Petr | Ivanov | 1 | 2 | 3 | Purchasing |
| 4 | Nikolay | Petrov | 1 | 3 | 3 | Purchasing |
| 5 | Nikolay | Ivanov | 2 | 1 | 3 | Purchasing |
| 6 | Sergey | Sidorov | 2 | 3 | 3 | Purchasing |
| 7 | Andrey | Bukin | 2 | 2 | 3 | Purchasing |
| 8 | Viktor | Rybakov | 4 | 1 | 3 | Purchasing |
| Department | User name | Position |
|---|---|---|
| NULL | Viktor Rybakov | Manager |
| Production | Ivan Petrov | Manager |
| Production | Ivan Sidorov | Senior analyst |
| Production | Petr Ivanov | Senior analyst |
| Production | Nikolay Petrov | Analyst |
| Distribution | Nikolay Ivanov | Manager |
| Distribution | Andrey Bukin | Senior analyst |
| Distribution | Sergey Sidorov | Analyst |
Прежде, чем рассказывать о командах изменения данных, нужно пояснить особенность диалекта Transact-SQL. Как видно из самого названия, этот механизм основан на транзакциях, то есть на последовательности операций, объединенных в один логический модуль, будь то запрос на выбоку данных, изменения данных или структуры таблиц. На время транзакции все используемые в сценарии данные блокируются, что позволяет избежать несоотвествия данных во время начала работы с таблицей и завершением сценария.
За транзакции в Transact-SQL отвечает структура BEGIN TRANSACTION . COMMIТ TRANSACTION. Эту структуру использовать необязательно, но тогда все команды сценария являются необратимыми, то есть нельзя сделать «откат» к предыдущему состоянию. Полная структура блока транзакций:
Ниже приведен пример использования этого блока:
Для вставки данных в таблицы SQL-сервера используется команда INSERT INTO:
Вторая часть комнады является необязательной для MS SQL Server 2003, но MS JET SQL без этого слова будет выдавать ошибку синтаксиса. Вставка обычно производиться целострочно, то есть в комнаде указываются все колонки таблицы и значения, которые нужно в них занести. Если же колонка имеет значение по умолчанию или разрешает пустое значения, то в команде вставки эту колонку можно не указывать. Команда INSERT INTO также разрешает указывать вносимые данные не по порядку следования колонок, но в этом случае нужно обозначить используемый порядок колонок.
Для того, чтобы изменить значение ячейки таблицы, используется команда UPDATE:
Обновление (изменение) значений в таблице можно производить безусловно, с условием или с выборкой данных из другой таблицы.
Удаление данных производится командой DELETE:
Удаление данных обычно производится по какому-то критерию. Так как удаление данных — это достаточно опасная операция, то перед выполнением такой команды лучше всего произвести тестовую выборку командой SELECT, которая выведет в результат те данные, которые будут стерты. Если это то, что требуется, тогда можно смело заменять SELECT на DELETE и выполнять удаление данных.
Более быстрая команда для очистки таблицы — это TRUNCATE TABLE.
Пример удаления всех данных:
Transact-SQL позволяет использовать временные таблицы, то есть таблицы, которые создаются в памяти сервера на время работы пользователя с базой данных. Временные таблицы могут иметь любое имя, но начинаться обязаны с символа #.
Хранимые процедуры и функции представляют собой набор SQL-операторов, которые можно сохранять на сервере. Если сценарий сохранен на сервере, то клиентам не придется повторно задавать одни и те же отдельные операторы, вместо этого они смогут обращаться к хранимой процедуре. Ситуации, когда хранимые процедуры особенно полезны:
- Многочисленные клиентские приложения написаны на разных языках или работают на различных платформах, но должны выполнять одинаковые операции с базами данных.
- Безопасность играет первостепенную роль. Хранимые процедуры используются для всех стандартных операций, что обеспечивает совместимость и безопасность среды, а процедуры гарантируют надлежащую регистрацию каждой операции. При таком типе установки приложения и пользователи не получают непосредственный доступ к таблицам базы данных и могут выполнять только конкретные хранимые процедуры.
- Необходимо снизить сетевой трафик между клиентом и сервером. Объем пересылаемой информации между сервером и клиентом существенно снижается, но увеличивается нагрузка на систему сервера баз данных, так как в этом случае на стороне сервера выполняется большая часть работы по обработке данных.
Пример создания хранимой процедуры и хранимой функции:
Итак, хранимые процедуры и функции дают следующие преимущества:
- производительность;
- общая логика для всез запросов;
- уменьшение трафика;
- безопасность — доступ пользователю дается не к таблице, а к процедуре;
Для увеличения производительности, то есть для быстрого выполнения запросов, следует помнить некоторые правила составления строк запросов:
#sql-server #vb.net
Вопрос:
У меня есть ошибка на моем vb.net процедура после выполнения команды я получаю сообщение об ошибке:
необходимо объявить @именованную переменную
Таблица в SQL Server выглядит так:
name: TAB,
ID integer unique,
NameD varchar(50)
Я не могу понять, почему я получаю эту ошибку.
Это потому, что я использую OLEdb в своей локальной системе? Я просто конвертирую проект в SQL Server или в запросе есть ошибка?
Обратите внимание, что я использую, чтобы открыть соединение с этими параметрами:
Dim strsql As String = ""
Dim strConn As String = "Provider=MSOLEDBSQL;Server=0.0.0.0;Database=****;UID=sa;PWD=***;"
Dim Conn As New OleDbConnection
и это функция
Public Function Ins() as integer
strsql = "INSERT INTO TAB (nameD) VALUES (@nameD)"
Dim CMD as New OleDbCommand(strsql, Conn)
With CMD
.Parameters.Add("@nameD", OleDbType.VarChar).Value = "aaa"
.ExecuteNonQuery()
End With
CMD = Nothing
Dim COnt As Long
Dim cmdC As OleDbCommand = New OleDbCommand("SELECT@@IDENTITY", Conn)
COnt = CType(cmdC.ExecuteScalar, Integer)
Return COnt
end function
Я пытался даже с
CMD.Parameters.AddWithValue("@nameD", "aaa")
Комментарии:
1. Oledbпараметр отмечает : «OLE DB.NET Поставщик данных платформы использует позиционные параметры, отмеченные знаком вопроса (?) вместо именованных параметров.»
2. Я не на 100% уверен, что это поддерживается с помощью
OleDb(это не с доступом , но это может быть что-то вроде Jet/ACE), но, если вы используетеSqlClient, вы можете выполнитьINSERTи туSELECTже команду одним вызовомExecuteScalar.3. На самом деле это не повредит, но как именно имеет смысл привести результат вашего вызова к
ExecuteScalarтипу asInteger, а затем назначить егоLongпеременной? Это просто показывает отсутствие мыслей.4. да, это был знак вопроса (?), я переношу проект , он работает
5. @jmcilhinney С доступом и его поставщиками OleDb вы можете использовать именованные параметры (с
SELECT,INSERT, независимо от того). Имя просто игнорируется, единственное, что учитывается, конечно, в позиции, связанной с порядком добавления параметров в команду.
Ответ №1:
Я не уверен, почему вы использовали OleDb бы для подключения к SQL Server, а не SqlClient , но, если вы собираетесь это сделать, я не думаю, что именованные параметры поддерживаются. Вам необходимо использовать заполнитель универсального параметра в своем коде SQL:
strsql = "INSERT INTO TAB (nameD) VALUES (?)"
Комментарии:
1. да, это был знак вопроса (?), я переношу проект , он работает
Я написал этот SQL в хранимой процедуре, но не работал,
declare @tableName varchar(max) = 'TblTest'
declare @col1Name varchar(max) = 'VALUE1'
declare @col2Name varchar(max) = 'VALUE2'
declare @value1 varchar(max)
declare @value2 varchar(200)
execute('Select TOP 1 @value1='+@col1Name+', @value2='+@col2Name+' From '+ @tableName +' Where ID = 61')
select @value1
execute('Select TOP 1 @value1=VALUE1, @value2=VALUE2 From TblTest Where ID = 61')
Этот SQL выдает эту ошибку:
Необходимо объявить скалярную переменную «@ value1».
Я генерирую SQL динамически и хочу получить значение переменной. Что мне делать?
5 ответов
Лучший ответ
Причина, по которой вы получаете ошибку DECLARE из своего динамического оператора, заключается в том, что динамические операторы обрабатываются отдельными пакетами, что сводится к вопросу области действия. Хотя может быть более формальное определение областей, доступных в SQL Server, я счел достаточным, как правило, иметь в виду следующие три, упорядоченные от наивысшей доступности до самой низкой доступности:
< Сильный > Global :
Объекты, доступные для всего сервера, такие как временные таблицы, созданные с помощью двойного решетки / решетки (##GLOBALTABLE, как бы вы ни называли #). Будьте очень осторожны с глобальными объектами, как и с любым приложением, SQL Server или другим; таких вещей лучше вообще избегать. По сути, я говорю, что нужно иметь в виду эту область видимости специально как напоминание о том, что не стоит в нее входить.
IF ( OBJECT_ID( 'tempdb.dbo.##GlobalTable' ) IS NULL )
BEGIN
CREATE TABLE ##GlobalTable
(
Val BIT
);
INSERT INTO ##GlobalTable ( Val )
VALUES ( 1 );
END;
GO
-- This table may now be accessed by any connection in any database,
-- assuming the caller has sufficient privileges to do so, of course.
Сессия :
Объекты, ссылки на которые привязаны к определенному spid. Вне всяких сомнений, единственный тип объекта сеанса, о котором я могу думать, — это обычная временная таблица, определенная как #Table. Нахождение в области сеанса по существу означает, что после завершения пакета (завершенного GO) ссылки на этот объект продолжат успешно разрешаться. Эти технически доступны другим сеансам, но программно сделать это было бы неким подвигом, так как они получают в базе данных tempdb своего рода рандомизированные имена, и доступ к ним в любом случае затруднен.
-- Start of session;
-- Start of batch;
IF ( OBJECT_ID( 'tempdb.dbo.#t_Test' ) IS NULL )
BEGIN
CREATE TABLE #t_Test
(
Val BIT
);
INSERT INTO #t_Test ( Val )
VALUES ( 1 );
END;
GO
-- End of batch;
-- Start of batch;
SELECT *
FROM #t_Test;
GO
-- End of batch;
Открывая новый сеанс (соединение с отдельным spid), второй пакет выше завершится ошибкой, так как этот сеанс не сможет разрешить имя объекта #t_Test.
Пакет :
Обычные переменные, такие как ваши @value1 и @value2, имеют область видимости только для пакета, в котором они объявлены. В отличие от таблиц #Temp, как только ваш блок запроса достигает GO, эти переменные перестают быть доступными для сеанса. Это уровень объема, на котором возникает ваша ошибка.
-- Start of session;
-- Start of batch;
DECLARE @test BIT = 1;
PRINT @test;
GO
-- End of batch;
-- Start of batch;
PRINT @Test; -- Msg 137, Level 15, State 2, Line 2
-- Must declare the scalar variable "@Test".
GO
-- End of batch;
Хорошо, и что?
Что происходит здесь с вашим динамическим оператором, так это то, что команда EXECUTE() эффективно оценивается как отдельный пакет, не прерывая пакет, из которого вы ее выполнили. EXECUTE() — это хорошо и все такое, но с момента появления sp_executesql() я использую первый только в самых простых случаях (явно, когда в моих утверждениях очень мало «динамических» элементов, в первую очередь для того, чтобы «обмануть» иначе неаккомодирующие операторы DDL CREATE для запуска в середине других пакетов). Ответ @ AaronBertrand, приведенный выше, аналогичен и по производительности будет аналогичен следующему, используя функцию оптимизатора при оценке динамические операторы, но я подумал, что стоит расширить, ну, параметр @param.
IF NOT EXISTS ( SELECT 1
FROM sys.objects
WHERE name = 'TblTest'
AND type = 'U' )
BEGIN
--DROP TABLE dbo.TblTest;
CREATE TABLE dbo.TblTest
(
ID INTEGER,
VALUE1 VARCHAR( 1 ),
VALUE2 VARCHAR( 1 )
);
INSERT INTO dbo.TblTest ( ID, VALUE1, VALUE2 )
VALUES ( 61, 'A', 'B' );
END;
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR( MAX ),
@PRM NVARCHAR( MAX ),
@value1 VARCHAR( MAX ),
@value2 VARCHAR( 200 ),
@Table VARCHAR( 32 ),
@ID INTEGER;
SET @Table = 'TblTest';
SET @ID = 61;
SET @PRM = '
@_ID INTEGER,
@_value1 VARCHAR( MAX ) OUT,
@_value2 VARCHAR( 200 ) OUT';
SET @SQL = '
SELECT @_value1 = VALUE1,
@_value2 = VALUE2
FROM dbo.[' + REPLACE( @Table, '''', '' ) + ']
WHERE ID = @_ID;';
EXECUTE dbo.sp_executesql @statement = @SQL, @param = @PRM,
@_ID = @ID, @_value1 = @value1 OUT, @_value2 = @value2 OUT;
PRINT @value1 + ' ' + @value2;
SET NOCOUNT OFF;
23
Community
23 Май 2017 в 13:30
Declare @v1 varchar(max), @v2 varchar(200);
Declare @sql nvarchar(max);
Set @sql = N'SELECT @v1 = value1, @v2 = value2
FROM dbo.TblTest -- always use schema
WHERE ID = 61;';
EXEC sp_executesql @sql,
N'@v1 varchar(max) output, @v2 varchar(200) output',
@v1 output, @v2 output;
Вы также должны передать свой ввод, например, откуда приходит 61, в качестве правильных параметров (но вы не сможете передавать таким образом имена таблиц и столбцов).
7
Aaron Bertrand
26 Янв 2014 в 17:59
Вот простой пример:
Create or alter PROCEDURE getPersonCountByLastName (
@lastName varchar(20),
@count int OUTPUT
)
As
Begin
select @count = count(personSid) from Person where lastName like @lastName
End;
Выполните приведенные ниже инструкции одним пакетом (выбрав все)
1. Declare @count int
2. Exec getPersonCountByLastName kumar, @count output
3. Select @count
Когда я попытался выполнить инструкции 1,2,3 по отдельности, у меня была такая же ошибка. Но когда они выполнялись все одновременно, все работало нормально.
Причина в том, что SQL выполняет операторы declare, exec в разных сеансах.
Открыт для дальнейших исправлений.
0
S’chn T’gai Spock
13 Апр 2017 в 09:51
Это произойдет и в SQL Server, если вы не запустите все операторы сразу. Если вы выделяете набор операторов и выполняете следующее:
DECLARE @LoopVar INT
SET @LoopVar = (SELECT COUNT(*) FROM SomeTable)
А затем попробуйте выделить другой набор утверждений, таких как:
PRINT 'LoopVar is: ' + CONVERT(NVARCHAR(255), @LoopVar)
Вы получите эту ошибку.
0
Curryjl
31 Май 2017 в 23:05
— СОЗДАТЬ ИЛИ ИЗМЕНИТЬ ПРОЦЕДУРУ
ИЗМЕНИТЬ ПРОЦЕДУРУ out (
@age INT,
@salary INT OUTPUT)
КАК НАЧАТЬ
SELECT @salary = (SELECT SALARY FROM new_testing where AGE = @age ORDER BY AGE OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY);
КОНЕЦ
—————— ОБЪЯВЛЕНИЕ ВЫХОДНОЙ ПЕРЕМЕННОЙ —————————— —-
ОБЪЯВИТЬ @test INT
——————— ЗАТЕМ ВЫПОЛНИТЕ ЗАПРОС ————————- ———
ВЫПОЛНИТЬ 25, @salary = @test OUTPUT
Печать @test
——————- тот же результат получить без процедуры ————————— —————— ВЫБРАТЬ * ИЗ new_testing, где ВОЗРАСТ = 25 ПОРЯДОК ПО ВОЗРАСТУ СМЕЩЕНИЕ 0 СТРОК ВЫБРАТЬ ТОЛЬКО СЛЕДУЮЩУЮ 1 СТРОКУ
0
rajat prakash
13 Янв 2020 в 11:10