Stored Procedure
-
Hi,
I am creating stored procedures for the first time. Could someone please tell me how to test a stored procedure once it has been created?
Cheers
Olivia
-
This shows how to check for existence of the procedure, how to delete it, create syntaxe. Input and output parameters, return value, calling method and the nocount option :
USE Master
GO
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE Name = 'DemoSP' AND XType = 'P' AND USER_NAME(uid) = 'dbo')
DROP PROCEDURE dbo.DemoSP
GO
CREATE PROCEDURE dbo.DemoSP @SomeParam AS INT, @OutValue AS INT OUTPUT
AS
SET NOCOUNT ON
SELECT ID, Name, XType FROM dbo.SysObjects WHERE ID > @SomeParam ORDER BY Xtype, Name
SET @OutValue = @@ROWCOUNT
RETURN -10
SET NOCOUNT OFF
GO
DECLARE @OutV AS INT
DECLARE @RetValue AS INT
SET @OutV = -99
SET @RetValue = -99
EXEC @RetValue = dbo.DemoSP 4000, @OutV OUTPUT
SELECT @OutV AS OutputParam, @RetValue AS ReturnedValue
GO
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE Name = 'DemoSP' AND XType = 'P' AND USER_NAME(uid) = 'dbo')
DROP PROCEDURE dbo.DemoSP
GO
-
You can add "print" or select statements inside for testing as well.
create procedure mytest
@myvar int, @myvar2
as
select @myvar, @myvar2, 'parameters'
declare @mysum int
select @mysum = @myvar + @myvar2
print @mysum
select @mysum
return
This should return the sum as a result set, but there are select and print statements inside to help test. These would be removed before moving this to production.
-
Thanks to u both!!!
-
In case you're not sure of how to unit test it without running the full application use the Query Analyzer, or in the case of 2005 click the 'New Query' tab to open a query window, and use this syntax and run the SP:
Use 'DataBase' '-- What ever the name of the database where the SP is placed
exec MyStoreProc ['parameter'],['parameter']... --Assuming yiu have any parameters
-
Unit testing stored procs has always been a crux of db developement.
Microsoft has just released the first veriosn of Visual studio for Data base professionsals.
They have integrated a unit testing frame work in to this that will allow you to set up andrun unit tests just like you do with your app code.
If this interests you I would check out the Microsoft web stie and see if you can get a trial download of the software, they were giving oout free trials at SQL Pass, I haven't been to the site yet but I have been playing around with the tools, ans so far I reall like what I see.
The best thing aobut the new tools is that you can test for a variety of cases, and you can run them over and over again.
-
Hi,
I've created my stored procedure but now I still have one problem.
I want to compare a value from the input with a value in the table where I do a select from.
How do I do this??
Do I use a counter - How would I use the counter to determine if the select statement yields any result?
Or do I simple test against the value from the returned select against the input value - how to I do this??
PLease help!!
-
Does this do the trick :
SELECT ID, Name, XType FROM dbo.SysObjects WHERE ID = @SomeParam ORDER BY Xtype, Name
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply