Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 8th, 2007, 04:26 AM
Authorized User
 
Join Date: Oct 2006
Posts: 39
Thanks: 0
Thanked 0 Times in 0 Posts
Default Stored Proc that returns a value

Hi,
Is there a way to create a Store Proc that returns a non-integer value? e.g.

ALTER PROCEDURE dbo.spGetProjectDateOfWeek
( @ProjectID nvarchar(10),
  @BatchCode nvarchar(10),
  @WeekNo int,
  @DateOfWeekN datetime OUTPUT
   )
AS
    SELECT @DateOfWeekN = DateOfWeekN FROM ProjectDateOfWeekTable
    WHERE ProjectID = @ProjectID
    AND BatchCode = @BatchCode
    AND WeekNo = @WeekNo


I would like the stored proc to return the @DateOfWeekN. I tried this code but it doesn't return any value.

When I used "RETURN @DateOfWeekN", @DateOfWeekN must be integer. This returns an integer value and not the actual date of @DateOfWeekN. I would like the actual date to be returned by the stored proc not the integer type.

Thanks so much in advance!

 
Old May 8th, 2007, 06:34 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Why dont you use a function to do that rather than using a stored procedure?

_________________________
- Vijay G
Strive for Perfection
 
Old May 8th, 2007, 02:51 PM
Authorized User
 
Join Date: Oct 2005
Posts: 72
Thanks: 0
Thanked 0 Times in 0 Posts
Default

To get the output parameter of your SP, you do it this way:

declare @DateofWeekN DATETIME
execute dbo.spGetProjectDateOfWeek 'ProjectID', 'BatchCode', 1, @DateofWeekN OUTPUT
select @DateofWeekN

SQL Server Helper
How well do you know SQL? Find out with the free test assessment from SQL Server Helper!!!
http://www.sql-server-helper.com/free-test/default.aspx

Got a SQL Server Question? Ask us here: http://www.sql-server-helper.com/forums/default.asp
 
Old May 8th, 2007, 08:00 PM
Authorized User
 
Join Date: Oct 2006
Posts: 39
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Vijay G,
I have created a UDF in SQL and it worked. But how do I call that in my application, ASP.Net for example. Thanks again! :)



 
Old May 9th, 2007, 01:05 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

You should be calling it using
Code:
Select dbo.YourFunctionName(Parameters....)
Hope that helps.
Cheers.

_________________________
- Vijay G
Strive for Perfection





Similar Threads
Thread Thread Starter Forum Replies Last Post
Calling an insert stored proc from a select stored dzitam SQL Language 10 April 2nd, 2007 12:39 PM
How are Carriage Returns stored? SQLScott SQL Server 2005 2 December 1st, 2006 04:07 PM
How are Carriage Returns stored? SQLScott SQL Server 2000 3 November 24th, 2006 10:37 AM
How to get value from stored proc busybee ASP.NET 1.0 and 1.1 Basics 4 April 2nd, 2006 01:06 AM
Stored Proc returns closed recordset ? xgbnow Pro VB Databases 10 March 26th, 2004 06:00 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.