how to store result at variable?

Question

Post reply

how to store result at variable?

SQL Galaxy

One Orange Chip

Points: 29472
More actions
August 1, 2013 at 5:03 am

#276534

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

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

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

Durai Samuel Mr or Mrs. 500 Points: 551 More actions · Answer 1

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

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 2

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".

Durai Samuel Mr or Mrs. 500 Points: 551 More actions · Answer 3

create proc sp_temp

@count INT = NULL OUTPUT

as

begin

declare @Result varchar(5)

select @count = COUNT(*) from #temptable

end

Try this out

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 4

same error

exec sp_temp @count

Msg 137, Level 15, State 2, Line 1

Must declare the scalar variable "@count".

kapil_kk SSC-Insane Points: 21316 More actions · Answer 5

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/

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 6

SQL Galaxy

One Orange Chip

Points: 29472

August 1, 2013 at 6:26 am

#1637748

Thanks

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

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 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/