SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Cant get stored procedure to display anything
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

LarryDC
Starting Member

USA
6 Posts

Posted - 07/25/2013 :  23:10:27  Show Profile  Reply with Quote
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 results

When 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- 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 = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT * from drw1037

Return

END

Edited by - LarryDC on 07/25/2013 23:11:38

MuMu88
Aged Yak Warrior

547 Posts

Posted - 07/25/2013 :  23:28:30  Show Profile  Reply with Quote
By executing the steps you described, you are merely altering an existing Stored Procedure. To execute it you have to do this:


EXEC [dbo].[UserReport] 

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/26/2013 :  00:03:36  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Edited by - visakh16 on 07/26/2013 00:04:31
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3557 Posts

Posted - 07/26/2013 :  09:48:16  Show Profile  Reply with Quote
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 ;)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000