How to fetch one by one row into a variable using SQLServer

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

I want to store one by one value into a variable from a table which should be inside an SP in SQLServer.

e.g.

SQL

    declare @GetStudentName nvarchar(128)
set @GetStudentName = (select StudentName from StudentProfile where session = 'ThisSession')

print @GetStudentName
the error shows 
"Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

after execute this statement I have got a list of student name then I want to check one by one. Please help me out from this problem as soon as possible.

Posted 12-Jul-15 2:06am

RTK The Limited Edition

Updated 12-Jul-15 2:48am

v3

Add a Solution

Comments

[no name] 12-Jul-15 8:26am

So add your student name into the where clause.

2 solutions

Solution 2

Unless you really need to go through the rows one-by-one I really advise to try to use set based operations.

SQL is a language optimized for manipulating and handling sets of data. From a performance point of view this is always the fastest way but also from the coding point of view, set operations are really effective.

For example if you need to give a 10% raise to all people working in a certain department, instead of looping through the rows, calculating the new salary and updating the row you could use a single statement like

SQL

UPDATE person
SET Salary = Salary * 1.1
WHERE Department = ...

If creating a single statement for the job becomes to complex you can always utilize temporary tables[^], table variables[^] and so on.

Posted 12-Jul-15 2:50am

Wendelius

Comments

Wendelius 12-Jul-15 9:56am

As a comment, if you explain the check you want to make for each student then it most likely can be transformed to a single statement.

RTK The Limited Edition 12-Jul-15 13:14pm

Actually I want to show profile details according their name,class,roll,sec...
searching should made may be either individually use a particular field or more than one field. If you help for one field I'll do another. Please give an example for that above sample query. I don't like cursor. If you can please avoid it.

Wendelius 12-Jul-15 13:16pm

I'm sorry but I don't quite follow. Could you post some kind of example data?

RTK The Limited Edition 15-Jul-15 6:36am

Though it was an example now I'm saying you the real problem. When I'll give a project to a client and they need to create new table(s) but system'll create audit table with respect to every table but there I won't know how much columns they'll create.I need an iteration to know how much columns he would add. Now I'm asking you how should I know to get rows one by one without using cursor. Please give me the query for that above example as soon as possible.

RTK The Limited Edition 15-Jul-15 6:45am

Though that was an example the real problem is. I'll finally give a project to a client but he needs to create new table and I don't know how many columns he add but my project'll create its' respective audit table using SP. Now I ask you how should I get column one by one without using cursor. Please give the query for the above example as soon as possible.

Wendelius 15-Jul-15 7:33am

I don't quite understand the question. If you don't know the columns you can't create a SQL statement, with cursor or not. Cursor just enables you to go through the rows one-by-one. You still need a statement.

RTK The Limited Edition 15-Jul-15 11:34am

I think I can't make understand you. can you give alternative of cursor??

Wendelius 16-Jul-15 2:52am

Typically alternative to a cursor is a single statement. However, in order to create a sample you'd need to post some kind of example what you actually do with the data fetched with the cursor and how the cursor is defined.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2015-07-12T02:29:00

Solution 1

You could just use a CURSOR:

SQL

DECLARE cur CURSOR FOR SELECT StudentName FROM StudentProfile where session = 'ThisSession'
OPEN cur
FETCH NEXT FROM cur INTO @StudentName
WHILE @@FETCH_STATUS = 0 BEGIN
    PRINT @StudentName
    FETCH NEXT FROM cur INTO @StudentName
END
CLOSE cur
DEALLOCATE cur

Posted 12-Jul-15 2:29am

OriginalGriff

Comments

Kornfeld Eliyahu Peter 12-Jul-15 8:34am

To which I would add, that if you came to use a CURSOR then check your design...
(and that because I'm against CURSORs :-))

RTK The Limited Edition 12-Jul-15 9:12am

Is it possible without use CURSOR ?

Kornfeld Eliyahu Peter 12-Jul-15 9:18am

Yes...

RTK The Limited Edition 12-Jul-15 9:20am

Then please give the solution query for this

Kornfeld Eliyahu Peter 12-Jul-15 9:26am

I first want you consider what Mika told in Solution 2 - think about a set-based operation!!!

RTK The Limited Edition 14-Jul-15 16:03pm

Please give me at least one solution without cursor. because i can't understand Mika's solution.