Stored procedures - SQL Server Forums

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

Stored procedures

Author

Topic

kareem100
Starting Member

10 Posts

Posted - 2011-06-30 : 15:24:10

Hi,

I tring to write a new store procedure. Actually I'm copying it from a VB .net book. I am using sql server 2005 with the sql server management studio express. However there is a problem. When I try to write a new stored procedure, the sql server gives me a template, which seems to have a different structure, and has commands which I'm not sure if they are needed and what they are used for. I have attempted to incorporate my stored into the template given, but I keep getting errors. I am going to list my stored procedure, and the given template below, and I hope someone can tell me how to write this stored procedure.

Regards, Kareem

STORED PROCEDURE
-------------------------------------

CreateProcedure " Five most expensive Products " AS
SET ROWCOUNT 5
SELECT Products.Productname AS FiveMostExpensiveProducts,
Products.UnitPrice
From Products
ORDER BY Products.UnitPrice DESC

TEMPLATE GIVEN
---------------

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 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 <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-06-30 : 15:30:17

quote:

Originally posted by kareem100

CreateProcedure " Five most expensive Products " AS
SET ROWCOUNT 5
SELECT Products.Productname AS FiveMostExpensiveProducts,
       Products.UnitPrice
From Products
ORDER BY Products.UnitPrice DESC

That must be a really old book!

CREATE PROCEDURE dbo.usp_Five_Most_Expensive_Products
AS

SET NOCOUNT ON

SELECT TOP(5)	ProductName AS FiveMostExpensiveProducts,
		UnitPrice
FROM		dbo.Products
ORDER BY	UnitPrice DESC

N 56°04'39.26"
E 12°55'05.63"

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-06-30 : 18:12:59

CREATE PROCEDURE dbo.Most_Expensive_Products (@in_top_n INTEGER)
AS
SELECT *
FROM (SELECT product_name, unit_price,
DENSE_RANK() OVER (ORDER BY unit_price DESC) AS prod_position
FROM dbo.Products)
WHERE prod_position <= @in_top_n;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

kareem100
Starting Member

10 Posts

Posted - 2011-07-01 : 18:38:27

Hi,

Jcelko, you sugestion didn't work. the system cam back with an error message

Msg 156, Level 15, State 1, Procedure Most_Expensive_Products, Line 18
Incorrect syntax near the keyword 'WHERE'.

Regards,
Kareem.

kareem100
Starting Member

10 Posts

Posted - 2011-07-01 : 19:06:29

Hi,

Peso, your suggestion did work, in that there was no error message.

But it might be because I'm a beginner and I have just started using sql server, but the problem was there was no results in the results panel.

Do you think there is a setting or other option that I need to change on my sql server.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-01 : 22:29:18

Do you have data in your products table yet?

N 56°04'39.26"
E 12°55'05.63"

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-07-03 : 17:58:12

T-SQL requires an alias on derived tables. My bad. The DENSE_RANK will report ties and port. TOP(n) does not do either, but does not tell you about the dropped data.

CREATE PROCEDURE dbo.Most_Expensive_Products (@in_top_n INTEGER)
AS
SELECT X.*
FROM (SELECT product_name, unit_price,
DENSE_RANK() OVER (ORDER BY unit_price DESC) AS prod_position
FROM dbo.Products) AS X
WHERE prod_position <= @in_top_n;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

kareem100
Starting Member

10 Posts

Posted - 2011-07-05 : 16:41:25

Hi,

Sorry about the delay, I have not been able to reply since I have been away for a few days.

Swepeso, the products table does have data in it. I'm not that stupid, really.

jcelko, I'm afraid I didn't understand your last post. Could you possible re-phrase and try to explain to me, what you meant.

Regards,
Kareem.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-05 : 17:45:19

quote:
Originally posted by kareem100

Swepeso, the products table does have data in it. I'm not that stupid, really.

When you copied my code above you did nothing but storing the procedure for later access.

The CREATE PROCEDURE doesn't execute the content.

Write this

EXEC dbo.usp_Five_Most_Expensive_Products

and press F5 to execute the stored procedure. Do you get a result now?

N 56°04'39.26"
E 12°55'05.63"

kareem100
Starting Member

10 Posts

Posted - 2011-07-07 : 17:30:06

Hi,

Swepeso, You asked me to write the following line

EXEC dbo.usp_Five_Most_Expensive_Products.

I'm a little confused, I'm not entirely sure if you wanted me to add this line to the query itself, or if you wanted me to write this line somewhere else to execute the query.

I did add this line to the end of the query, and then pressed F5. But it didn't seem to work. there was no data in the results window.I got the message "Command(s) completed successfully.". But I used to get this message before.

Regards,
Kareem.

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2011-07-08 : 04:31:07

What Peso is telling you is that you have created the stored procedure in your database, and can now run it when you wish. In SQL a stored procedure is a little like a function that you might create in another programming language, when you have set it up you can call it as you wish.

So having created it, you should now see it in your database - check the object explorer. If you open your database you should see a programmability "folder", in there you have a stored procedures "folder" which should contain your stored procedure (you may need to refresh).

To call it e.g. for testing purposes, you can create a new query making sure you are on the right database. If you then type EXEC dbo.usp_Five_Most_Expensive_Products and press F5 it should run and show your results, alternatively from the object explorer you can right click on the stored procedure and select Execute Stored Procedure, or you can select SCRIPT STORED PROCEDURE AS and then click EXECUTE TO and then New Query Edit Window which will basically recreate the exec statement that peso gave you.

The template is merely some generic code that is intended to help you write a new stored procedure though for complete beginners it can be baffling. If you have a template you can press CTRL-SHIFT-M and it allows you to enter various elements to create the skeleton of your stored procedure. I would suggest you do this for your example above. Create a new stored procedure template, then press CTRL-SHIFT-M and fill in all the details from your book, the resulting stored procedure should look reasonably similar to what is in your book i.e. it should be less confusing.

Steve

-----------

What color do you want that database?

kareem100
Starting Member

10 Posts

Posted - 2011-07-08 : 18:48:57

Hi,

Elwoos, I did exactly what you told me. but it still doesn't work, there is no data in the results window. And I can't work out why. I will list the two stored procedures here, and hope someone can tell me what I'm doing wrong. I do have a table called dbo.products , and there is plenty of data in it.

------------------------------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[usp_Four_Most_Expensive_Products]
-- Add the parameters for the stored procedure here
AS

SET NOCOUNT ON

SELECT TOP(4) ProductName AS FourMostExpensiveProducts,
UnitPrice
FROM dbo.Products
ORDER BY UnitPrice DESC

---------------------------------------------------------------

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[usp_Four_call]
As

EXEC dbo.usp_Four_Most_Expensive_Products
-------------------------------------------

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-07-08 : 19:14:02

You are missing the point.

Run this and only this: EXEC dbo.usp_Four_Most_Expensive_Products. Put that into a new query window and hit F5. Do not save it into a stored procedure.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources