Even the simplest SP does not return any result.

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Even the simplest SP does not return any result.

Author

Topic

emrdesign
Starting Member

3 Posts

Posted - 2011-08-09 : 04:46:18

Hello, I have build an SQL 2005 database for our webportal and we use ASP and PHP to get the data out of there and that all works fine. Now I need to create an SP and I have looked at even the simplest examples but for some reason the SP does not give any result back when I execute the SP via Microsoft SQL Server Management Studio. All I get is the messages tab saying "commands completed succesfully" when I click the execute button. The SP is created and saved in the "Programmability - Stored procedures" folder.

Below is my code. I hope anybody can tell me why I don't get any results from the SP when I execute it. I have all the rights in the database:

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================

CREATE PROCEDURE sptest
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
firstname
FROM employer
END
GO

lappin
Posting Yak Master

182 Posts

Posted - 2011-08-09 : 05:03:34

I tried your procedure as you described and it works fine. Try executing from SQL:
Exec sptest

If this does not return anything - verify that the select statement returns something. Run:
SELECT firstname FROM employer

emrdesign
Starting Member

3 Posts

Posted - 2011-08-09 : 05:09:36

Executing it from SQL with "Exec sptest" works OK. But I don't understand why it does not execute via the SP Execute button or via the Execute in the Query menu.

emrdesign
Starting Member

3 Posts

Posted - 2011-08-09 : 05:28:40

Oh I see what I am doing wrong. This script just creates the SP, but does not actually execute the SELECT query in the SP. I thought it would execute the SELECT query as well and show me the result.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources