April 17, 2012 at 8:32 pm
hi guys,
i need help in my Stored procedure.
select empname ,(select case when empno is null then
begin
declare salary
declare @salary int
declare c1 CURSOR FOR
select distinct salary from dept
open c1
FETCH NEXT FROM c1
INTO @salary
WHILE @@FETCH_STATUS = 0
------some calculation---------------here i need to call another sp
close c1;
end
else
maxsalary
END) AS salary
from dept
can we use like this in sql select , or do i need to use case statement,
i dont have just 1 statement ,i have block of statement .so i think i cant use case
any idea?
April 17, 2012 at 8:39 pm
Please post the entire stored procedure. Can't really help with only partial code.
April 18, 2012 at 8:16 am
It would help if you formatted your code using the IFCode Shortcuts code="sql" so that we could more easily read your code.
select empname ,(
select case when empno is null then
begin
declare @salary int
declare c1 CURSOR FOR
select distinct salary
from dept
open c1
FETCH NEXT FROM c1
INTO @salary
WHILE @@FETCH_STATUS = 0
------some calculation
--here i need to call another sp
close c1;
end
else -- CASE
maxsalary
END -- CASE
) AS salary
from dept
The short answer is NO, you cannot do this.
The long answer is that a SELECT is a single operation that operates on a set. It is necessarily atomic, so you cannot use features like CURSORS, WHILE statements, or calls to stored procedures that are not inherently atomic within a SELECT statement.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 18, 2012 at 8:20 am
thanks drew,is there any other way.
i cannot use udf , as i cannot call sp inside it.
i need to get result of cursor in select statement.
where can i put this code and get the result
April 18, 2012 at 8:21 am
You surely can write your query without the cursor.
You can use table-valued funcions (inlined or not), and make calculations set-by-set,
not row-by-row.
Temp tables or table variables could help.
If you would describe exactly what you want, maybe you will get a more precise answer.
April 18, 2012 at 8:46 am
hbtkp (4/18/2012)
thanks drew,is there any other way.i cannot use udf , as i cannot call sp inside it.
i need to get result of cursor in select statement.
where can i put this code and get the result
hbtkp,
Please don't hijack other forums. We have tried to assist you but have failed to provide any of us with the information we have requested from you in an effort to help you. If you need help, please start a thread in the appropriate forum, ask your question and provide all the relevent information needed to help you.
April 18, 2012 at 8:50 am
inline table valued function can call sp?thats my question
April 18, 2012 at 9:00 am
No, unfortunately you cannot call sp from a function. There are also other limitations (no PRINT, RAISERROR, no try-catch, temp tables not visible, no changing of db tables allowed etc).
But, even without that there always is a way how to elegant do what you want...
April 18, 2012 at 9:05 am
hbtkp (4/18/2012)
inline table valued function can call sp?thats my question
No.
April 18, 2012 at 9:11 am
hbtkp (4/18/2012)
inline table valued function can call sp?thats my question
Please don't hijack other people's threads.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 18, 2012 at 9:19 am
EDIT: nothing constructive said and thinking about it after the post its better not to post it
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 18, 2012 at 9:29 am
j
April 18, 2012 at 10:21 am
ok
April 18, 2012 at 10:56 am
whats wrong with this code,i am getting values again and again.repeating
declare @P1 nvarchar(32)
declare @mtd2 float
declare c1 CURSOR FOR
select account, MTD from #temp2
open c1
FETCH NEXT FROM c1
INTO @P1,@mtd2
WHILE @@FETCH_STATUS = 0
begin
if(@mtd2 IS NULL )
BEGIN
declare @IRR2 float
EXEC pdashboard
select @IRR2 = IRR from pdashboard
SELECT @IRR2 as Last1month
END
ELSE
BEGIN
select @mtd2 as last2month
END
-------------
temp2
create table #temp2(account varchar(31)
mtd float)
i am getting @mtd2 values repeating ,there should no of rows in temp2
April 18, 2012 at 10:59 am
Well the cursor is the problem. 😛
You don't have a fetch_next inside the body of your cursor so it is an endless loop.
Depending on what your proc "pdashboard" does you probably don't need a cursor for this at all.
_______________________________________________________________
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/
Viewing 15 posts - 1 through 15 (of 67 total)
You must be logged in to reply to this topic. Login to reply