Creating parameterized stored procedure
CREATE/ALTER PROCEDURE [dbo].[CategoryGet]
(
@CATEGORYID int
)
AS
BEGIN
SELECT * FROM CATEGORY
WHERE CATEGORYID=@CATEGORYID
END
GO
Stored procedure using output parameter
CREATE PROCEDURE OutPutParameter @intInput int, @intOutput int OUTPUT AS -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; set @intOutput = @intInput + 1 GO --Call it like this: declare @intResult int exec OutPutParameter 3 ,@intResult OUT select @intResult
Using SET NOCOUNT ON
Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned.
If your stored procedure performs one or more DML operations that cause informational resultsets to be generated, then these will confuse the task. An informational resultset will be familiar to anyone that uses Query Analyser, as you will have seen them as messages in results, for example (1 row(s) affected). These messages are indistinguishable from the SELECT resultset you are really interested in, and since the task reads the first resultset it encounters, an informational resultset can mask your real data. To suppress this behaviour add the SET NOCOUNT ON statement to the top or your stored procedure or script.