How to store result of SELECT in local var.

Question

How to store result of SELECT in local var.

Bart VW

SSC Enthusiast

Points: 186
More actions
June 27, 2003 at 7:03 am

#82785

Hi there,
As a novice in writing stored procedures, I'm stuck all day now on this issue. I've got to add rows from one table to another, which isn't that difficult. But, for some of the destination fields, I run a SELECT qry against another table (which works fine in Access, btw.)
So, I thought I'd declare a local variable to store the result of the SELECT stmt., and than use the local var. to complete the field in the INSERT part of the sp.
But I can't get it to work. This is what I do:
DECLARE @TechGest int
EXEC ('@TechGest = SELECT ID_gestionnaire FROM Gestionnaires WHERE Role = 2 AND Cellule = ''K''')
INSERT INTO Polices
(ID_police, Date_lettre, Date_proposition, [Montant_prime EUR], Code_campagne,
Societe, Active, nombre_contrat, ID_gestionnaire_defaut,
ID_type_contrat, Zone01, Zone02, Zone03, Zone04, Zone05, Zone06, Zone07, Zone08, Zone09, Zone10, Zone11,
Zone12, Zone13, Zone14, Zone15)
SELECT ContractNbr, GETDATE(), GETDATE(), MonthlyPay,
CASE
WHEN BIV < 100 THEN 'M'
WHEN BIV >=100 THEN 'A'
END AS BivType,
'S' AS Expr3, - 1 AS Expr7, Status,
@TechGest AS Expr4,
etc..
I hope ther's anyone who can help, I've been googling for nearly a day now, and still haven't found a solution.
Tia,
Bart

Viewing 7 posts - 1 through 7 (of 7 total)

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hi Bart

quote:
DECLARE @TechGest int
EXEC ('@TechGest = SELECT ID_gestionnaire FROM Gestionnaires WHERE Role = 2 AND Cellule = ''K''')

SELECT @TechGest = ID_gestionnaire ... should work.

Take a look in BOL for local variables

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Bart VW SSC Enthusiast Points: 186 More actions · Answer 2

Thanks Frank, this works fine. I've been browsing BOL, but didn't really find what I was looking for (didn't see your solution either).

Now the last thing that won't work properly, is the CASE statement: whatever the value of the BIV field, I always get 'A' filled in. Anyone an idea how come?

Thanks,

Bart

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Hi Bart,

quote:
Now the last thing that won't work properly, is the CASE statement: whatever the value of the BIV field, I always get 'A' filled in. Anyone an idea how come?

not sure about this one, but according to BOL (lookup CASE) try

SELECT <all your fields>, and at last CASE ....

wait, a second...

You write CASE. CASE what??? Please lookup BOl to see what I mean!

HTH

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Bart VW SSC Enthusiast Points: 186 More actions · Answer 4

Hi Frank,

I did lookup CASE in BOL. There are two types of CASE stmts: the simple CASE stmt., and the searched CASE stmt. If you want to check a 'not equal' expression, you've got to use the searched CASE stmt.

So, in my "lookup" table (the one in the FROM part), I've got a field [BIV], which contains a numeric value. When the value of this field's <= 100, I want to write 'M' to the output field, otherwise 'A'.

But I always get 'A' written to my destination table. What am I doing wrong?

Thanks for your help so far,

Bart

David Burrows SSC Guru Points: 65144 More actions · Answer 5

The syntax of the CASE statement looks OK.

I would write the statement like this

CASE WHEN BIV < 100 THEN 'M' ELSE 'A' END AS BivType,

Pendantic I know but then that's me.

Your first post shows WHEN BIV < 100 THEN 'M' but your last post you stated <= 100 = 'M'?????

What datatype is BIV?

Are your you have data to satisfy 'M'?

Far away is close at hand in the images of elsewhere.
Anon.

Bart VW SSC Enthusiast Points: 186 More actions · Answer 6

I've got 4 records to test, with the BIV field containing 90, 300, 120 and 80. It should work, shouldn't it?

Well, now it does! Thanks for your remark David: the BIV field is of Varchar type... A little CASTing does the job nicely.

You see, once I got stuck with this sp., I didn't look further to see the obvious.

Thanks to all for helping me on this one.

Bart