Syntax of stored procedure using output variable.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Syntax of stored procedure using output variable.

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-03-26 : 11:44:07

The following stored procedure parses if I leave the AppCount and @AppCount variables out but gives me "Incorrect syntax near the keyword 'As'". At first I didn't have an Output variable for AppCount because I don't need to pass it. I need to pass AppID and AppName to a detail oriented stored procedure. This is just a summary. Can you help me with these lines:

CREATE PROCEDURE sp_InstalledAppNamesAndCounts 
	-- Add the parameters for the stored procedure here
	@AppID Varchar(255) OUTPUT,
	@AppName Varchar(255) OUTPUT,
	@AppCount Varchar(255) OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT @AppID = v_CommonApp.AppID
	, @AppName =  v_CommonApp.AppName
	, @AppCount = (COUNT(v_SMSAppdata.appID)) AS CountOfApps
	FROM v_CommonApp
	LEFT JOIN v_SMSAppData ON v_SMSAppData.appID = v_CommonApp.AppID
	GROUP BY v_CommonApp.AppName
	HAVING COUNT(v_SMSAppData.AppID) > 0
	ORDER BY v_CommonApp.AppName
	END
GO

The error occurs on the red line. I have eliminated the parentheses and moved them around to no effect. Thank you if you can help me.

Duane

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-26 : 11:57:58

take away AS CountOfApps

No, you're never too old to Yak'n'Roll if you're too young to die.

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-03-26 : 12:05:11

That worked. Stupidly simple. Can you tell me how to call it now? Do I select from it? Just executing it didn't work and I didn't think it would.

Duane

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-26 : 12:38:59

Here you can see how it works:
http://www.sqlteam.com/article/stored-procedures-returning-data

No, you're never too old to Yak'n'Roll if you're too young to die.

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-03-26 : 12:52:01

I really appreciate you trying to help me here. I have a problem viewing some of the pages on SQLTeam. I get an Operation Aboorted error. It happens on this page, too. Would you, or somebody be so kind as to copy and paste the appropriate material on this page back into this post. I looked up this problem and Microsoft recommended upgrading to IE8 except where I work, I don't think I can do such an upgrade. Thank you.

Duane

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-03-26 : 12:58:58

Nevermind - I have a workaround for this. I am going to read the page now. Thanks anyway.

Duane

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-26 : 13:00:21

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources