Stored Procedure ( simple ) - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Stored Procedure ( simple )

Author

Topic

andrewcw
Posting Yak Master

133 Posts

Posted - 2009-09-07 : 11:06:37

Yestrday I created a simple function to return an integer. I need to use that value in another stored procedure. However SQL timed out. Next idea - I read the section on stored procedure here at SQLTeam - the part on using Output Variables seemed to fit. But I cant seem to save that in VSudio's SQL pane it complains about AS. { I have tried inserting a Declare @InValue int following the AS line and removing it from the CREATE PROCEDURE [dbo].MaxItem(@InValue int), but that does not save.

CREATE PROCEDURE [dbo].MaxItem(@InValue int)

AS

SELECT @InValue= MAX(dbo.Customer.qty) AS MaxNum
FROM dbo.Customer INNER JOIN
dbo.[nnn] ...... .ResultsProcessed = 'True')
SET NOCOUNT ON
RETURN @InValue

Ideas ??? Thanks

andrewcw

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-07 : 11:24:39

[code]
CREATE PROCEDURE [dbo].MaxItem
@InValue int OUTPUT
AS

SELECT @InValue= MAX(dbo.Customer.qty) AS MaxNum
FROM dbo.Customer INNER JOIN
dbo.[nnn] ...... .ResultsProcessed = 'True'
[/code]

KH
[spoiler]Time is always against us[/spoiler]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-07 : 11:29:17

quote:
Originally posted by khtan

CREATE PROCEDURE [dbo].MaxItem(
@InValue int OUTPUT)
AS

SELECT @InValue= MAX(dbo.Customer.qty) AS MaxNum
FROM dbo.Customer INNER JOIN
dbo.[nnn] ...... .ResultsProcessed = 'True'

KH
[spoiler]Time is always against us[/spoiler]

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

andrewcw
Posting Yak Master

133 Posts

Posted - 2009-09-07 : 13:14:40

What is causing this "INCORRECT SYNTAX NEAR THE KEYWORD AS " - the query absolutely works when tested - from the insert SQL and test panel. I tried modifying the SQL query to make the most simple query of all and I get no further

CREATE PROCEDURE [dbo].MaxItem(@InValue int OUTPUT)

AS

SELECT @InValue=COUNT(Customer) AS CustCount
FROM dbo.CustomerLineNumber

RETURN

Ideas ????

andrewcw

andrewcw
Posting Yak Master

133 Posts

Posted - 2009-09-07 : 13:37:54

This useless sample will save-
ALTER PROCEDURE dbo.StoredProcedure4(@InValue int)
AS
Return @Invalue
RETURN

If I insert the QUERY it is OK BUT if I INSERT @InValue=

SELECT @InValue=MAX(... The attempt to assign the value fails. Is this a known Bug ? A Visual Studio 2008 Issue ????

andrewcw

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-07 : 16:40:41

take away that AS MaxNum or AS CustCount

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-07 : 17:14:49

And then you use it like this:
declare @test int
exec MaxItem @test output
select @test

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

andrewcw
Posting Yak Master

133 Posts

Posted - 2009-09-07 : 17:27:49

Almost - now it will save but when I execute I get null...
ALTER PROCEDURE [dbo].MaxThis

@InValue int OUTPUT

AS
SELECT @InValue=COUNT(Customer)
FROM dbo.CustomerLineNumber

RETURN @InValue

Procedure or function 'MaxThis' expects parameter '@InValue', which was not supplied.
No rows affected.
(0 row(s) returned)
@InValue = <NULL>
@RETURN_VALUE =

Ideas .... ? Thanks

andrewcw

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-07 : 17:32:17

once again:
declare @test int
exec MaxThis @test output
select @test

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

andrewcw
Posting Yak Master

133 Posts

Posted - 2009-09-07 : 19:37:31

My environment to execute the stored procedure - is just a right mouse click to "Execute". Different than the command line you showed me. I found the pop up panel had a default and also a null to select. When I selected to execute with the null it did give me back a value - and best of all the CORRECT VALUE

The output varible sp must feed another stored query. I tried to insert the value from the output variable stored procedure.

WHERE (dbo.CustomerLineNumber.LineNumber BETWEEN dbo.MaxThis - 55 AND dbo.MaxThis)

But it did not like that. How should a stored procedure that returns an output variable like dbo.MaxThis be referenced ??

[ Thanks for all your help, because I have no idea where this information can be found, not in books, searches I have made ]

andrewcw

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-07 : 21:33:53

[code]
declare @test int
exec MaxThis @test output
select @test

-- another query
WHERE (dbo.CustomerLineNumber.LineNumber BETWEEN ~~dbo.MaxThis~~ @test - 55 AND ~~dbo.MaxThis~~ @test)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

andrewcw
Posting Yak Master

133 Posts

Posted - 2009-09-08 : 09:56:51

The first stored procedure seemed to execute, but the second part did not act as if it had the number:

ALTER PROCEDURE [dbo].LastXIssues
AS

declare @test int
exec MaxThis @test output
select @test

SELECT dbo.CustomerLineNumber.LineNumber, dbo.CustomerLineNumber.Customer,

...... subquery that works when actual numbers used for @test

WHERE (dbo.CustomerLineNumber.ModelEnum = 0) AND (dbo.CustomerLineNumber.LineNumber BETWEEN @test-55 AND @test)
ORDER BY dbo.CustomerLineNumber.LineNumber DESC

RETURN

// output:
Running [dbo].[LastXIssues].

The 'MaxThis' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.
MaxThis // Should I change the procedure to do something anout this complaint ??-------
3045 // this is correct,
No rows affected.
(1 row(s) returned)
Column1
-------
NULL
No rows affected.
(1 row(s) returned)
LineNumber Customer ISSUES
---------- -------- -------
No rows affected.
(0 row(s) returned) // not correct - I should have 5 rows

andrewcw

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources