473,320 Members | 2,080 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

How can we add bulk data into a variable in SQL Server using Stored procedure?

This is my code for the calculation of the increment provided to the employee. when i try to execute it im gettin an error that "Server: Msg 512, Level 16, State 1, Procedure SampleProcedure, Line 7
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
Expand|Select|Wrap|Line Numbers
  1. create procedure SampleProcedure 
  2. AS
  3. begin
  4.     Declare    @Inc money
  5.     Declare @exp int
  6.     Declare @sal money
  7.     Set @exp = (Select Experience
  8.             From Employee 
  9.             Where Experience > 2)
  10.     set @sal= (Select Salary
  11.             From Employee
  12.             Where Experience > 2)
  13. while @exp > 2
  14. set @Inc = (@sal*0.2)
  15. end 
  16. go
  17.  
So i thgt of making my variables as array... but looks lik i cant declare an array in sql...
pl help me.....
Feb 4 '11 #1

✓ answered by JamieHowarth0

Hi Janu,

What you really want is to go over every employee record where Experience > 2, right?
You can't do this using a plain select query into a variable, because as the error describes, you can't set a variable to be a collection of rows. You need to use something called a cursor, which will select all the rows you want according to your criteria, then one by one, do something with the data from each row.

Will post back with an example a little later.

Best,

codegecko

2 1809
JamieHowarth0
533 Expert 512MB
Hi Janu,

What you really want is to go over every employee record where Experience > 2, right?
You can't do this using a plain select query into a variable, because as the error describes, you can't set a variable to be a collection of rows. You need to use something called a cursor, which will select all the rows you want according to your criteria, then one by one, do something with the data from each row.

Will post back with an example a little later.

Best,

codegecko
Feb 4 '11 #2
Rabbit
12,516 Expert Mod 8TB
What are you trying to do? As far as I can tell that stored procedure returns nothing, creates nothing, and updates nothing. All you're doing is setting a bunch of variables that don't get used to accomplish a task.

Having said that. If you're trying to update a table then you can accomplish the same thing with
Expand|Select|Wrap|Line Numbers
  1. UPDATE Employee SET Salary=Salary*1.2 WHERE Experience > 2
Feb 4 '11 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: marc | last post by:
I've been developing a stored procedure that uses a user defined function in the query portion of the procedure. However, since the end product needs to allow for dynamic table names, the UDF will...
1
by: deepdata | last post by:
Hi, I am trying to fetch data from db2 (express version) database by calling stored procedure. I have tried to use both cursor and for loop but still i am getting error. --======Start...
2
by: Godzilla | last post by:
Dear all, I have a challenge in hand and am not too sure how to accomplish this using stored procedure. I have a table containing about 3 fields, but I need to reorder/renumber a field value...
1
by: HemaSMD | last post by:
i am trying to use gridview to display and edit data. i am using stored procedures in select command and update command of sqldatasourrce. i m trying to use session parameter which works well for the...
1
hariharanmca
by: hariharanmca | last post by:
How to Shift or Copy Data from Sqlserver to MS Access using Stored procedure I am passing the clint MS Access file path in that i want to insert or update data in that MS Access file using...
0
by: Srireddy | last post by:
Hi all, I am trying to read an xml using stored procedure and upload it in to the DB. I am using DB2 v8.0. can anyone help me complete my assignment... Thanks in advance..
1
by: madhuparimi | last post by:
I am working on jasper reports.First i want to know how to generate a ireport using stored procedure. Can u please help me out and tell me how to generate an ireport using stored procedure
0
by: madhusr | last post by:
I have a requirement where in in I need to call a SQL Server 2000 stored procedure from DB2 (ver 8.x) based on a value in a column. I can create a trigger in DB2 to monitor the column and after the...
2
by: qwedster | last post by:
Folk! How to programattically check if null value exists in database table (using stored procedure)? I know it's possble in the Query Analyzer (see last SQL query batch statements)? But how...
2
by: hemantc87 | last post by:
i have created this function with a parameter offset and i want to access the database using stored procedure but the code and stored procedure i have written below is not working...what is the right...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.