Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to store result at variable? Expand / Collapse
Author
Message
Posted Thursday, August 1, 2013 5:03 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 11:41 PM
Points: 1,058, Visits: 2,991
Hi,

create proc sp_temp
--@temp varchar(10)
as
begin
declare @Result varchar(5)
select COUNT(*) from #temptable
end

this simple SP working fine.. I want store the result in one variable..

thanks
ananda
Post #1479879
Posted Thursday, August 1, 2013 5:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 1, 2013 12:41 PM
Points: 17, Visits: 61
You can use an output parameter to get the result



create proc sp_temp
@count INT OUTPUT = NULL
as
begin
declare @Result varchar(5)
select @count = COUNT(*) from #temptable
end


Now your @count variable will have the count result
Post #1479881
Posted Thursday, August 1, 2013 5:19 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 11:41 PM
Points: 1,058, Visits: 2,991

exec sp_temp @count

--Msg 102, Level 15, State 1, Procedure sp_temp, Line 2
--Incorrect syntax near '='.


also tried as below code

alter proc sp_temp
@count INT OUTPUT
as
begin
declare @Result varchar(5)
select @count = COUNT(*) from #temptable
end

--Msg 137, Level 15, State 2, Line 1
--Must declare the scalar variable "@count".
Post #1479883
Posted Thursday, August 1, 2013 5:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 1, 2013 12:41 PM
Points: 17, Visits: 61
create proc sp_temp
@count INT = NULL OUTPUT
as
begin
declare @Result varchar(5)
select @count = COUNT(*) from #temptable
end


Try this out

Post #1479893
Posted Thursday, August 1, 2013 6:10 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 11:41 PM
Points: 1,058, Visits: 2,991
same error

exec sp_temp @count

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@count".
Post #1479896
Posted Thursday, August 1, 2013 6:20 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
Try in this manner-

create procedure test_sp
AS
BEGIN
declare @count int
Select @count = COUNT(*) from test
RETURN @count
END


Declare @storevalue int
EXECute @storevalue = test_sp
SELECT @storevalue




_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1479899
Posted Thursday, August 1, 2013 6:26 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 11:41 PM
Points: 1,058, Visits: 2,991
Thanks
Post #1479900
Posted Thursday, August 1, 2013 8:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 13,302, Visits: 12,168
ananda.murugesan (8/1/2013)
same error

exec sp_temp @count

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@count".


That is because you didn't declare the variable. You are passing it to the proc but it isn't defined. All you need to do is declare it.

declare @count int

exec sp_temp @count OUTPUT

select @count



_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1479984
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse