July 13, 2011 at 6:26 pm
hi, please help
Need some help, i´ve this query
---------------------------------
DECLARE @_type INT;
SELECT max(turno)+1 INTO@_type FROM u_contadores;
insert into u_contadores (b1,b1cont,turno)
VALUES
('00003','00003',@_type)
----------------------------------------
the insert statment does not work for the field turno with @_type
u_contadores table structure
b1 b1cont turno
10 10000 1
10 10003 2
10 10000 3
I need to insert another row of data like this, beeing 4 the number that results from
SELECT max(turno)+1 INTO@_type FROM u_contadores;
10 10003 4
July 13, 2011 at 6:38 pm
if i run
SELECT max(turno)+1 INTO@_type FROM u_contadores;
i get the desired value
please help
July 13, 2011 at 7:27 pm
.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 13, 2011 at 7:28 pm
carlos cachulo-318532 (7/13/2011)
if i runSELECT max(turno)+1 INTO@_type FROM u_contadores;
i get the desired value
I beg to differ. The statement:
select @max-2(turno)+1 into @_type from u_contadores
is not valid syntax. The correct syntax is:
select @_type = @max-2(turno) from u_contadores
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 14, 2011 at 1:48 am
hi
my problem is in the insert statment
declare @_type int
select @_type = max(turno) from u_contadores
insert into u_contadores (b1,b1cont,turno)
VALUES
('00003','00003',@_type)
returns
Msg 137, Level 15, State 2, Line 4
Must declare the scalar variable "@_type".
July 14, 2011 at 5:02 am
Are you trying to select only the insert statement and only execute that? If so, execute the entire script and not just insert statement.
July 14, 2011 at 5:07 am
Also, you might as well save time, your and others as well, using google 😀 for the insert syntax rather than ask for such basics answer here and wait for its reply when we have hundreds of examples out there.
July 14, 2011 at 6:48 am
thats the problem i´m executing it all
July 14, 2011 at 7:23 am
The last code you posted (which is repeated below) works on fine for me. Are you posting ALL the code you're executing. Are you sure there isn't a GO statement in there somewhere? Is it possible you just aren't highlighting the first line with your declare statement before executing? Forgive me for being skeptical, but you've already posted up one statement that simply wouldn't compile and insisted that it worked correctly.
create table u_contadores (b1 int, b1cont int, turno int) --- added to test
declare @_type int
select @_type = max(turno) from u_contadores
insert into u_contadores (b1,b1cont,turno)
VALUES ('00003','00003',@_type)
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 14, 2011 at 8:09 am
Hi
you are completly right, it works, the problem was a null value on the table
thk for the patient that you all had.
July 14, 2011 at 8:28 am
Glad it's working, but a null value in the table wouldn't cause the error below:
Msg 137, Level 15, State 2, Line 4
Must declare the scalar variable "@_type".
A word of advice: When something is frustrating you, take a deep breath and slow down. 😉
Have a good day, all.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 14, 2011 at 8:32 am
many,many tks
July 15, 2011 at 10:29 am
As a syntax thing use SET to assign value to the variable
set @_type = (select max(turno) from u_contadores)
Although it does work the way you wrote it is a little more definite this way.
It made me revisit the difference between SET and SELECT,
http://vyaskn.tripod.com/differences_between_set_and_select.htm
thank you for the revival. 🙂
July 15, 2011 at 10:59 am
jw: I can't seem to use the link you posted up.
But when populating variables from columns in a table, would you seriously use
set @a = (select column_A from someTable where colKey = 1);
set @b-2 = (select column_B from someTable where colKey = 1);
set @C = (select column_C from someTable where colKey = 1);
instead of
select @a = column_A, @b-2 = column_B, @C = column_C
from someTable
where colKey = 1
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 15, 2011 at 12:13 pm
I can get to that article but I wouldn't use it as a reference for anything current. It was written when 2005 was a baby and the tests were all performed on 7, 2000, and 2005. The author did a good job though of explaining how each different method works and the advantages/disadvantages of both. There was a total lack of performance results but the following quote sums up this quite nicely.
There is hardly any performance difference between SET and SELECT, when initializing/assigning values to variables. BUT, I made one startling discovery. As you all know, one single SELECT statement can be used to assign values to multiple variables. This very feature of SELECT makes it a winner over SET, when assigning values to multiple variables. A single SELECT statement assigning values to 3 different variables, is much faster than 3 different SET statements assigning values to 3 different variables. In this scenario, using a SELECT is at least twice as fast, compared to SET.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply