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.
| Author |
Topic |
|
LarryDC
Starting Member
6 Posts |
 Posted - 2013-07-25 : 23:10:27
|
| I have been searching for over 15 straight hours in MSDN, TechNet, Forums, Google, Bing, Yahoo etc. etc...What I don't understand is why instructions are so top secret and its like people expect a new person to have some sort of magical wisdom. or maybe developers just love screwing with people because they get a kick out of making newbies struggle, I don't know but I am so frustrated I cant find a simple straight forward answer anywhere!I cant get a sql stored procedure to produce output at all. All that happens is I get a command completed successfully but I want to see the results.I have a very simple newly created procedure that I want to see the output for all records in a table. It has to be a stored procedure as there is an interface that will ONLY allow a stored procedure be called. If I run the below code in a query it runs just great and I see all the resultsWhen I execute the below stored procedure I only get a command completed successfully but no results are returned. What is the secret here? Would someone be will to help a complete newbie (that's me) get the below stored procedure to return actual records? USE [KwikTag]GO/****** Object: StoredProcedure [dbo].[UserReport] Script Date: 07/25/2013 18:38:51 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: Larry Casey-- Create date: 07/25/2013-- Description: User Report-- =============================================ALTER PROCEDURE [dbo].[UserReport] -- Add the parameters for the stored procedure here @p1 int = 0, @p2 int = 0ASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT * from drw1037Return END |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-07-25 : 23:28:30
|
| By executing the steps you described, you are merely altering an existing Stored Procedure. To execute it you have to do this:[CODE]EXEC [dbo].[UserReport] [/CODE] |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-26 : 00:03:36
|
| When you run the code you posted it just modifies the procedure in the database (compiling) but doesnt actually execute it. See the previous suggestion on how to execute it and you'll get the results from procedure to display as output.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-26 : 09:48:16
|
| Once you run the code as you did, it simply stores the code for the stored procedure into SQL Server. It does not execute the stored procedure and it does not even compile (i.e., does not generate an execution plan) for it.See previous to previous suggestion on how to execute it and you will get the result. Although not noticeable most of the time, the first time you run it, it may be slower than subsequent runs for couple of reasons: 1) SQL Server compiles the code (i.e., generates an execution plan) and 2) It may need to load the data into memory from disk.I hope I am adding some value with my long-winded explanation and not just adding to my post count. If you think otherwise, feel free to shoot me down ;) |
|
|
|
|
|
|
|
|
Cant get stored procedure to display anything