April 7, 2009 at 9:33 am
I have a sql stored procedure, which I want to return one integer value from.
The sp is meant to return an integer. It calls a function (which compiles fine). This function gets the UserID for a Username.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[GetDiscount1]
declare @Username VARCHAR(255)
@ret int
AS
SET @ret = (
declare @Username VARCHAR(255)
SELECT Discount.DiscountAmount
FROM Discount
WHERE DiscountCode = dbo.GetUserIdFromUsername(@Username)
)
GO
This doesn't compile:
Msg 156, Level 15, State 1, Procedure GetDiscount1, Line 7
Incorrect syntax near the keyword 'declare'.
Msg 156, Level 15, State 1, Procedure GetDiscount1, Line 9
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Procedure GetDiscount1, Line 13
Incorrect syntax near the keyword 'declare'.
Msg 102, Level 15, State 1, Procedure GetDiscount1, Line 16
Incorrect syntax near ')'.
I've seen examples (like on sqlserverperformance) which don't use the declare keyword for the parameter. In my calling code (C#) would I just declare the return value parameter as null?
If I want to return varchar or another data type not supported by SET/return values, what are my options?
Also, what is the difference between output and return values?
Thanks
April 7, 2009 at 9:46 am
Try this:
ALTER PROCEDURE [dbo].[GetDiscount1]
@Username VARCHAR(255)
@ret int output
AS
SET @ret = (
SELECT Discount.DiscountAmount
FROM Discount
WHERE DiscountCode = dbo.GetUserIdFromUsername(@Username)
)
You don't use "declare" in parameters. You need to include "output" after the parameter you want to output/return. You don't re-declare a parameter as a variable.
With those three things fixed, the proc should work.
I don't know how to call a proc from C#, but in VB, you set one of the properties of the parameter as "output", and that will get the value from the output parameter. C# probably has something similar.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 7, 2009 at 9:51 am
Yeah C# does have certain ways to distinguish between output parameters and non-output parameters.
I now get these errors:
Msg 102, Level 15, State 1, Procedure GetDiscount1, Line 4
Incorrect syntax near '@ret'.
Msg 137, Level 15, State 2, Procedure GetDiscount1, Line 11
Must declare the scalar variable "@Username".
April 7, 2009 at 10:14 am
Hi
Just a little oversight... Snipped of of GSquared's code:
@Username VARCHAR(255), -- < Here you missed the comma
@ret int output
😉
Greets
Flo
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply