473,689 Members | 2,839 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

problem returning rows from SPROC

Heres my problem, the first part selects a row from the database, if
there is no row with the criteria it inserts a row and then returns it,
the problem is the IF statement that inserts the row, never returns the
select after it. if there is a row initially in the database, it
returns the right information, I just can't get it to return the row
after inserting it. Anyone know what the problem could be?

Stored procedure:

ALTER PROCEDURE dbo.CheckCurren tPayPeriod
(@UserID varchar(50))
AS

BEGIN
-- This SP checks to see if the current PayPeriod exists,
-- if not it will create the payperiod for them and return

DECLARE @appStartDate DATETIME
DECLARE @dt DATETIME
DECLARE @rows int
SET @appStartDate = (SELECT PayPeriodStart FROM PayPeriodStart)
SET @dt = GETDATE()

SELECT
UserID
FROM
PayPeriod
WHERE
(PeriodStart <= CONVERT(varchar (10), @dt, 101)) AND (PeriodEnd >=
CONVERT(varchar (10), @dt, 101)) AND (UserID = @UserID)

-- Inserts their new PayPeriod
DECLARE @PayPeriodID int
if (@@ROWCOUNT = 0)
BEGIN
DECLARE @sDate datetime
DECLARE @eDate datetime
SET @sDate = @appStartDate
SET @eDate = DATEADD(day, 13, @sDate)

INSERT INTO
PayPeriod
(UserID, PeriodStart, PeriodEnd)
VALUES
(@UserID, @sDate, @eDate)

/*EXEC @PayPeriodID = InsertPayPeriod @UserID, @sDate, @eDate*/

SET @PayPeriodID = @@IDENTITY

SELECT * FROM PayPeriod WHERE PayPeriodID = @PayPeriodID

RETURN

END
else
RETURN
END

Jul 23 '05 #1
1 1596
(dk****@gmail.c om) writes:
Heres my problem, the first part selects a row from the database, if
there is no row with the criteria it inserts a row and then returns it,
the problem is the IF statement that inserts the row, never returns the
select after it. if there is a row initially in the database, it
returns the right information, I just can't get it to return the row
after inserting it. Anyone know what the problem could be?


So how do you run the procedure? If you run it from Query Analyzer,
you will see something like:

UserID
------

(0 rows affected)

(1 row affected)

PayPeriodID UserID PeriodStart PeriodEnd
----------- ------ ----------- ---------
1 <value> <value> <value>

(1 row affected)

If you run this from a client program, you must be able to handle these
three result sets. This means that if you use ADO - to take an example -
you should have to use .NextRecordset to navigate through the record sets.
Note here that the second record set is closed - that record sets consists
of the rowcount only.

However, it's probably better to rewrite the procedure:

CREATE PROCEDURE dbo.CheckCurren tPayPeriod (@UserID varchar(50)) AS
BEGIN
-- This SP checks to see if the current PayPeriod exists,
-- if not it will create the payperiod for them and return
DECLARE @appStartDate DATETIME
DECLARE @dt DATETIME
DECLARE @rowc int
DECLARE @PayPeriodID int

SET NOCOUNT ON

SET @appStartDate = (SELECT PayPeriodStart FROM PayPeriodStart)
SET @dt = GETDATE()

SELECT @PayPeriodID = PayPeriodID
FROM PayPeriod
WHERE PeriodStart <= CONVERT(char(8) , @dt, 112)
AND PeriodEnd >= CONVERT(char(8) , @dt, 112)
AND UserID = @UserID
SELECT @rowc = @@rowcount

-- Inserts their new PayPeriod
IF @rowc = 0
BEGIN
INSERT INTO PayPeriod (UserID, PeriodStart, PeriodEnd)
VALUES (@UserID, @appStartDate, DATEADD(day, 13, @sDate))
SET @PayPeriodID = @@IDENTITY
END

SELECT PayPeriodId, UserId, PeriodStart, PeriodEnd
FROM PayPeriod
WHERE PayPeriodID = @PayPeriodID
END

Observations:

o SET NOCOUNT ON removes the closed recordset for the rowcount from
the INSERT statement.

o Use style 112 when chopping of time from datetime values. 112 gives
you the format YYYYMMDD, which is always interpreted the same. Format
could be reinterpreted if the user has an unexpected language setting.

o Since @@rowcount is volatile - update after each statement, I catch
into a local variable immeidately, and glue that SELECT directly to
the SELECT I'm catching rowcount for.

o SELECT * in production is not good practice. Always explicitly list
which columns you want returned.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
749
by: Bruce Stockwell | last post by:
the setup: Webservice/WinClient application/SQL server. VS.Net (visual basic) winform wizard creates a simple form with load cancel cancelall and datagrid bound to a simple Dataset with one Datatable. No coding by programer, All wizardry. User starts app, opens form, adds (4) records and clicks update.
1
2044
by: teddysnips | last post by:
SQL Server 2000 I have a stored procedure that uses an extended SPROC to send an email notification to my customers when a document is distributed. However, the SPROC has an unexpected side effect. If I run it in its current incarnation, it only sends one email and then exits. However, if I remove or comment out the block
5
7033
by: SSP | last post by:
Dear ASP.NETers, How would I insert multiple rows of data from a web form? Are there any tute's and stuff around. Couldn't find any myself. Thanks in advance. SSP
3
9012
by: John Pether (john | last post by:
I have the following code which loads a dataset into a datagrid: <code> ' Obtain Link information from Links table ' and bind to the datagrid control Dim links As New DNSite.LinksIndexDB() ' DataBind Links to DataGrid Control DG.DataSource = links.GetLinks(CatID)
2
1494
by: Brian Piotrowski | last post by:
Hi all, Is there a way I can return an SQL server response message through an ASP page? Right now when I run a command through the Query Analyzer (such as an insert, update, delete, etc. statement), I will get a message in the response window (ie: 1 row(s) affected). What is the variable that I would need to add to my ASP page to show this result.
7
1791
by: Bob | last post by:
Hi, I have a typed unbound dataset that is passed to a datahandling class to be filled. The datahandling class fills it from a sproc using an oledbDataAdapter (SQLAnywhere database) The only table in the dataset has two int32 columns. There is one row of data and as expected, column 0 contains an int and column 1 contains a null. I want to update column1. The following code executes but the row cell remains unchanged.
0
1004
by: Elmo Watson | last post by:
Let's say I have a tableAdapter which uses an Insert sProc -- the last line in the sProc is a Select statement, returning the identity in code, I write something like: Dim myTA as (TableAdapter) label1.text=MyTA.Insert_sproc However, the label always gets 0, though the row is getting inserted - - anyone know how I can fix this?
12
2248
by: Light | last post by:
Hi all, I posted this question in the sqlserver.newusers group but I am not getting any response there so I am going to try it on the fine folks here:). I inherited some legacy ASP codes in my office. The original code's backend is using the SQL Server 2000 and I am testing to use it on the Express edition. And I run into the following problem.
8
21653
by: Mike P | last post by:
How do you return a datareader from a stored procedure, but also return an output parameter? Here is my code, which is just returning a data reader at the moment : _conn.Open(); SqlCommand _comm = new SqlCommand(); _comm.Connection = _conn; _comm.CommandType = CommandType.StoredProcedure; _comm.CommandText = "usp_CE_User_Authenticate";
0
8594
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8528
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8947
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8786
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7621
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6454
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5810
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
2965
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
1952
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.