probem with declare

Question

Post reply

probem with declare

carlos cachulo-318532

Say Hey Kid

Points: 697
More actions
July 13, 2011 at 6:26 pm

#244046

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

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

carlos cachulo-318532 Say Hey Kid Points: 697 More actions · Answer 1

if i run

SELECT max(turno)+1 INTO@_type FROM u_contadores;

i get the desired value

please help

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

carlos cachulo-318532 (7/13/2011)
if i run
SELECT 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

carlos cachulo-318532 Say Hey Kid Points: 697 More actions · Answer 4

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".

1Ankit1 Ten Centuries Points: 1253 More actions · Answer 5

Are you trying to select only the insert statement and only execute that? If so, execute the entire script and not just insert statement.

1Ankit1 Ten Centuries Points: 1253 More actions · Answer 6

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.

carlos cachulo-318532 Say Hey Kid Points: 697 More actions · Answer 7

carlos cachulo-318532

Say Hey Kid

Points: 697

July 14, 2011 at 6:48 am

#1352769

thats the problem i´m executing it all

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 8

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

carlos cachulo-318532 Say Hey Kid Points: 697 More actions · Answer 9

Hi

you are completly right, it works, the problem was a null value on the table

thk for the patient that you all had.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 10

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

carlos cachulo-318532 Say Hey Kid Points: 697 More actions · Answer 11

carlos cachulo-318532

Say Hey Kid

Points: 697

July 14, 2011 at 8:32 am

#1352857

many,many tks

jwbart06 Hall of Fame Points: 3291 More actions · Answer 12

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. 🙂

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 13

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 14

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/