S.P Help - 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

SQL Server 2008 Forums

Transact-SQL (2008)

S.P Help

Author

Topic

tooba
Posting Yak Master

224 Posts

Posted - 2012-09-19 : 19:17:32

I have a question,

Here is my query,

SELECT SID FROM chartdDB
WHERE ADate = @ADate
AND SID NOT IN (SELECT SID FROM UPLOADRequest )

when i run this query sometime i get 1 SID or sometime more then One.

I have two question.

1) How i can set my Parameter to Previous day
Set @ADate = (If i run today @ADate should be Previous day) ?

2) Once i run this query
SELECT SID FROM chartdDB
WHERE ADate = @ADate
AND SID NOT IN (SELECT SID FROM UPLOADRequest )

THEN EXECUTE THIS STORE PROCEDURE

exec Save_Request @SID

How i can create a S.P to run to throug loop if i get more then one SID?

Please let me know if my question is not clear.

Thank You.

tooba
Posting Yak Master

224 Posts

Posted - 2012-09-19 : 19:41:44

Before I run, Need guide, is below Cursor work, any adivse?

DECLARE @SID int

Declare InsertSampleID Cursor For

SELECT SID FROM chartdDB
WHERE ADate = dateadd(day,datediff(day,1,GETDATE()),0)
AND SID NOT IN (SELECT SID FROM UPLOADRequest )

OPEN InsertSampleID
FETCH NEXT FROM InsertSampleID
INTO @SID
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC [my Store Procedure] @Sampleid

Fetch Next from CurLab
into @SId

close CurLab
deallocate CurLab
END

tooba
Posting Yak Master

224 Posts

Posted - 2012-09-20 : 11:39:07

Any advise guys?

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-09-20 : 11:58:53

Q1: "How i can set my Parameter to Previous day"?
A1: set @ADate = DateAdd(Day, -1, @ADate)

Q2: How to handle multiple records being returned by the SELECT statement?
A2: The normal advise is to NOT use a cursor, for perfromance reasons. The exception is when you need to run the results through a stored procedure. Since you are running the resulting number into a stored procedure, a cursor, like you have, will work. If you are returning a large number of records, your performance will not be great. However, it sounds like you are returning one record as a normal result. If performance is critical and you can integrate the logic of the stored procedure into your SELECT statement, perhaps a set based solution could be found.

=================================================
We are far more concerned about the desecration of the flag than we are about the desecration of our land. -Wendell Berry

tooba
Posting Yak Master

224 Posts

Posted - 2012-09-20 : 14:07:04

Thank you for you guys for best advise. I created Below Cursor and its work fine,

DECLARE @SID int

Declare InsertSID Cursor For
Select DISTINCT Sid from MDB
where ADate = dateadd(day,datediff(day,1,GETDATE()),0)
AND SID NOT IN (SELECT SID FROM MRequest )

OPEN InsertSID
FETCH NEXT FROM InsertSampleID
INTO @SID
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@SID <> 0)
BEGIN
EXEC [INSERT_SP] @Sid
END
Fetch Next from InsertSID
into @SId

END

close InsertSID
deallocate InsertSID

Please let me know or share your Exp, how i can avoide cusor in this case.

Thank You.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-09-21 : 11:13:36

Can't say how to get rid of the cursor unless you can show us what the INSERT_SP is doing.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources