Store the value fetched from a query [Resolved]

Posted by Rajujrk under Sql Server on 3/3/2010 | Views : 1574 | Status : [Member] | Replies : 3
Hai all,

I created a stored procedure, there i need to store a value returned from a query. My code is given below
[code]
CREATE PROCEDURE mypro (@users varchar(150))
AS
declare @myvar varchar(150)
declare @mycnt int
BEGIN
set @myvar='select count(*) from ' + @users + '_Catalog'
set @mycnt=exec (@myvar)

PRINT @mycnt
END
[/code]

For the above code, the below ERROR Message shown
[code]
Incorrect syntax near the keyword 'exec'.
[/code]

Thanks & Regards

Rajkumar J



Responses

Posted by: Vuyiswamb on: 3/3/2010 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down

Resolved
Change it to this


ALTER PROCEDURE mypro 

(
@users varchar(150),
@mycnt int OUTPUT
)
AS
declare @myvar varchar(150)
set @myvar='select count(*) from ' + @users + '_Catalog'
execute ( @myvar )


and execute it like this

declare @mycnt int
exec mypro 'Vuyiswa',@mycnt output


Thank you for Posting at Dotnetfunda

Vuyiswa Maseko


Thank you for posting at Dotnetfunda
[Administrator]

Rajujrk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vuyiswamb on: 3/3/2010 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down
What do you expect

set @mycnt=exec (@myvar)



this variable @mycnt to return what ?



Thank you for posting at Dotnetfunda
[Administrator]

Rajujrk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajujrk on: 3/3/2010 [Member] Starter

Up
0
Down
i need the count value, that means record count will store in @mycnt

Thanks & Regards

Rajkumar J

Rajujrk, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response