Multiple variables in fetch

I can not assign more than one variable using this cursor:
DECLARE Itemid_Cursor CURSOR FOR
select distinct itemid,itemname from items
OPEN Itemid_Cursor
FETCH NEXT FROM Itemid_Cursor
INTO @.itemid,@.itemnameWhat does "can not" mean? Do you get an error message? If so, what is it?
<uri@.bwayphoto.com> wrote in message
news:1139251594.154562.102910@.g43g2000cwa.googlegroups.com...
>I can not assign more than one variable using this cursor:
>
> DECLARE Itemid_Cursor CURSOR FOR
> select distinct itemid,itemname from items
> OPEN Itemid_Cursor
> FETCH NEXT FROM Itemid_Cursor
> INTO @.itemid,@.itemname
>|||The @.itemname variable does not get populated.|||Can you post all of the relevant actual code?
Andrew J. Kelly SQL MVP
<uri@.bwayphoto.com> wrote in message
news:1139251594.154562.102910@.g43g2000cwa.googlegroups.com...
>I can not assign more than one variable using this cursor:
>
> DECLARE Itemid_Cursor CURSOR FOR
> select distinct itemid,itemname from items
> OPEN Itemid_Cursor
> FETCH NEXT FROM Itemid_Cursor
> INTO @.itemid,@.itemname
>|||Well, you're going to have to provide DDL and sample data... what you've
provided so far is not enough to give you an answer.
See http://www.aspfaq.com/5006
<uri@.bwayphoto.com> wrote in message
news:1139251923.570483.247690@.o13g2000cwo.googlegroups.com...
> The @.itemname variable does not get populated.
>|||<uri@.bwayphoto.com> wrote in message
news:1139251923.570483.247690@.o13g2000cwo.googlegroups.com...
> The @.itemname variable does not get populated.
Maybe you have NULLs in that column.|||Thanks Andrew, I removed the non-relevant code and it worked.
I had some other bug.
Thanks again.|||*doh*
<uri@.bwayphoto.com> wrote in message
news:1139253728.769033.207820@.g14g2000cwa.googlegroups.com...
> Thanks Andrew, I removed the non-relevant code and it worked.
> I had some other bug.
> Thanks again.
>|||I tried this and works fine:
-- CREATE TEST TABLE
create table items
(
itemid int
, itemname varchar(50)
)
-- POPULATE TEST TABLE
insert into items
values (1, 'Item1')
insert into items
values (2, 'Item2')
insert into items
values (3, 'Item3')
-- BEGIN SCRIPT
declare @.itemid int
, @.itemname varchar(50)
DECLARE Itemid_Cursor CURSOR FOR
select distinct itemid,itemname from items
OPEN Itemid_Cursor
FETCH NEXT FROM Itemid_Cursor
INTO @.itemid,@.itemname
while @.@.fetch_status = 0
begin
select @.itemid,@.itemname
FETCH NEXT FROM Itemid_Cursor INTO @.itemid,@.itemname
end
close Itemid_Cursor
deallocate Itemid_Cursor
-- DROP TEST TABLE
drop table items
"uri@.bwayphoto.com" wrote:

> I can not assign more than one variable using this cursor:
>
> DECLARE Itemid_Cursor CURSOR FOR
> select distinct itemid,itemname from items
> OPEN Itemid_Cursor
> FETCH NEXT FROM Itemid_Cursor
> INTO @.itemid,@.itemname
>

multiple variables from query

Hi,
I want to assign multiple values from a query to local variables.
I can do it with a single one like:
SET @.Myvar = (SELECT FirstNameFROM People)
But if I want to select more then one field, I don't know how to do this.
SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't work.
I could do two queries, or write a cursor, but that sounds inefficient to
me,
Does anyone have a solution?
Thanks,
Leo
SELECT @.myVar = FirstName, @.MyVar2 = LastName FROM People
Jacco Schalkwijk
SQL Server MVP
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>
|||Leo
Declare @.var1 INT, @.var2 INT
SELECT @.var1=col1,@.var2=col2 FROM Table
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>
|||Hi,
You can write the query like the below,
Select @.Myvar = FirstName, @.Myvar2 = LastName FROM People
Thanks
Hari
MCDBA
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>
|||Thank you for all the replies.
It works now!
Leo
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>

multiple variables from query

Hi,
I want to assign multiple values from a query to local variables.
I can do it with a single one like:
SET @.Myvar = (SELECT FirstNameFROM People)
But if I want to select more then one field, I don't know how to do this.
SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't work.
I could do two queries, or write a cursor, but that sounds inefficient to
me,
Does anyone have a solution?
Thanks,
LeoSELECT @.myVar = FirstName, @.MyVar2 = LastName FROM People
Jacco Schalkwijk
SQL Server MVP
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>|||Leo
Declare @.var1 INT, @.var2 INT
SELECT @.var1=col1,@.var2=col2 FROM Table
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>|||Hi,
You can write the query like the below,
Select @.Myvar = FirstName, @.Myvar2 = LastName FROM People
Thanks
Hari
MCDBA
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>|||Thank you for all the replies.
It works now!
Leo
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>sql

multiple variables from query

Hi,
I want to assign multiple values from a query to local variables.
I can do it with a single one like:
SET @.Myvar = (SELECT FirstNameFROM People)
But if I want to select more then one field, I don't know how to do this.
SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't work.
I could do two queries, or write a cursor, but that sounds inefficient to
me,
Does anyone have a solution?
Thanks,
LeoSELECT @.myVar = FirstName, @.MyVar2 = LastName FROM People
--
Jacco Schalkwijk
SQL Server MVP
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>|||Leo
Declare @.var1 INT, @.var2 INT
SELECT @.var1=col1,@.var2=col2 FROM Table
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>|||Hi,
You can write the query like the below,
Select @.Myvar = FirstName, @.Myvar2 = LastName FROM People
Thanks
Hari
MCDBA
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>|||Thank you for all the replies.
It works now!
Leo
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>

Multiple Variables Assigned To One Select

Hello,

Is there a way to assign multiple variables to one select statement as in the following example?

DECLARE @.FirstName VARCHAR(100)

DECLARE @.MiddleName VARCHAR(100)

DECLARE @.LastName VARCHAR(100)

@.FirstName, @.MiddleName, @.LastName = SELECT FirstName, MiddleName, LastName FROM USERS WHERE username='UniqueUserName'

I don't like having to use one select statement for each variable I need to pull from a query. This is in reference to a stored procedure.

Thank you!

Cody

Hi, you can use the below syntax.

SELECT

@.FirstName = FirstName,

@.MiddleName = MiddleName,

@.LastName = LastName

FROM USERS

WHERE username = 'UniqueUserName'

Eralper

http://www.kodyaz.com

Multiple Value TextBox

Hi Folks,

I'm trying to assign multiple values to a textbox and I'm receiving an error. The error says, "The value expression for the textbox AcctName contains an error." The first value is account number and the second value is account name. An example follows:

1234 - SPC Travel Agency

My expression for the textbox contains the following:

=Fields!AcctNum.Value + ' - ' + Fields!AcctName.Value

Please help.

Hello,

Try this:

=cStr(Fields!AcctNum.Value) + " - " + Fields!AcctName.Value

Hope this helps.

Jarret

|||

Hello:

Jarret thanks for responding however, I figured it out by doing the following:

=Fields!AcctNum.Value & " - " & Fields!AcctName.Value

Best regards

|||

Yes, that does the same as what I posted, except without the cast for string. Since you didn't include the field type on AcctNum, I went ahead and included the cStr(), just in case it was a numeric field.

Jarret

|||

Jarret wrote:

Yes, that does the same as what I posted, except without the cast for string. Since you didn't include the field type on AcctNum, I went ahead and included the cStr(), just in case it was a numeric field.

Jarret

He used & for string concat, while you used + for integer concat/sum though

I do that all the time in SSRS, using + (in T-SQL) instead of & (in VB)

|||

Hello Jerry,

That is true, but the '+' and '&' behave the same when working with strings. Since I casted the field as string, they work the same. The other problem with the expression in his first post was that he was trying to use a single quote (') instead of a double quote (") on the literal string (the dash between the AcctNum and AcctName).

The & does an implicit convert to string on the operators. So, where I did the cast using cStr(), the & handled that itself.

=Fields!AcctNum.Value & " - " & Fields!AcctName.Value

=cStr(Fields!AcctNum.Value) + " - " + Fields!AcctName.Value

Jarret

|||

Jarret wrote:

Hello Jerry,

That is true, but the '+' and '&' behave the same when working with strings. Since I casted the field as string, they work the same. The other problem with the expression in his first post was that he was trying to use a single quote (') instead of a double quote (") on the literal string (the dash between the AcctNum and AcctName).

The & does an implicit convert to string on the operators. So, where I did the cast using cStr(), the & handled that itself.

=Fields!AcctNum.Value & " - " & Fields!AcctName.Value

=cStr(Fields!AcctNum.Value) + " - " + Fields!AcctName.Value

Jarret

Thanks Jarret

That's nice to know that it knows how to interpret the + concat as well