Compare 2 Stored Proc Results

Jun 10, 2008

Hi guys. I'm trying to compare the results from various stored procedures. Various stored procedures in our database got redone (refactored), and I want to see if they still pull back the same data.

Are there any programs out there that do this? Compare result sets from stored procedures? Any help is appreciated. Thanks!

View 1 Replies


ADVERTISEMENT

Stored Proc Results Are Displaying In The Messages Tab Instead Of Results Tab- URGENT

May 14, 2008




Hi All,
I have a stored proc which is executing successfully...but the results of that stored proc are displaying in the Messages Tab instaed of results Tab. And in the Results Tab the results shows as 0..So, Any clue friends..it is very urgent..I am trying to call this stored proc in my Report in SSRS as well but the stored proc is not displaying there also...Please help me ASAP..

Thanks
dotnetdev1

View 4 Replies View Related

Stored Proc Compare...

Jun 9, 2000

Hi,

I'm using SQL

View 1 Replies View Related

Stored Proc Compare...

Jun 9, 2000

Hi,

I'm using SQL Server 7.0. I have a Production database and a Development database. I have stored procedures with the same name in both databases. Is there a utility that I can use to see if the version that is in the production database is the same as the one in development and, if not, what are the differences between the two?

Thanks in advance,
Darrin

View 1 Replies View Related

Compare Utilities For Comparing Stored Procedure Results?

Dec 10, 2007

I have large stored procedures in SQL Server 2005 that often get updated. Sometimes it is very difficult to recognize how one change could impact the overall results. I would like to have some sample input that I could test during changes and see it compare the data results before & after my changes. This would help me quickly identify if the results are fine to pass through.

So basically I would like an easy way to compare the results of 2 stored procedures. Any suggestions or utilites that would help me do this?

View 6 Replies View Related

Can't See Stored Proc Results

Apr 19, 2006

I have this stored proc:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[usp_CrimRecTest]
-- Add the parameters for the stored procedure here
@caseID [nvarchar]

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 dbo.tblCASENOMASTER.CASENO, dbo.tblCASEMAST.LASTNAME, dbo.tblCASEMAST.FRSTNAME
FROM dbo.tblCASENOMASTER LEFT OUTER JOIN
dbo.tblCASEMAST ON dbo.tblCASENOMASTER.CASENO = dbo.tblCASEMAST.CASENO
WHERE (dbo.tblCASENOMASTER.CASENO = @caseID)
END

When I run this with an EXEC statement, the result pane shows no results but the message pane says it completed successfully and one row is affected. I know my input data is good. I also get nothing when I call this sproc from a VB front end. Any ideas?

Thanks.

View 4 Replies View Related

T-SQL How To Deal With Results From Stored Proc

Jul 23, 2005

Try hard to become familiar with T-SQL.Can anybodey tell me the best way to deal with set's provided by astored procedure. Til yesterday I thougt trapping set in temp tableusing INSERT EXEC is a way out of this, but then I struggeled withnested INSERT EXEC's.What are all the system proc's good for if the results cannot beevaluated? The approach of modular programming is to have code doingsimilar things in one place.If I try to make use of sp_helprolemember to get login names for moreroles, pack the logins in one table and return the result set in a SP,the procedure which calls that is unable to evaluate the set.On the other hand I read the advice, not to access system tablesdirectly.Is there a way out?

View 9 Replies View Related

Stored Proc Failing To Return Results

Aug 9, 2000

I have a search stored proc which fails to return results when called by more than one user.

I have put selects in the various SPs to trace results and if I call from 3 query windows (executnig each as quickly as I can work the mouse) I get the following:
1st query returns the trace values (including correct count of temp table recs) but no result set
2nd query erturns nothing just "The command(s) completed successfully."
3rd query returns full results.

This seems to be consistent.

We are running SQL Server 7 SP1.
Just upgrading to SP2 to see if that helps.

The main SP calls other SPs to build result sets.
These use quite a few temp tables passed between SPs, parse CSV lists, join different other tables, create a SQL string to exec to do the search and get record IDs to return (no cursors).
The result set is built by a called SP using the temp table with IDs to return.

Anyone know of any problems or can suggest anything to try?

View 3 Replies View Related

Indexing Results Of Stored Proc (or New Table Created By One)

Jul 20, 2005

Hi,I am using data from multiple databases and/or queries. It would greatlysimplify and speed things up if I could use CONTAINS in processing theresults. However, "CONTAINS" requires the data to be indexed. Due to theamount of processing, I think it would be faster even if I had to re-indexevery time.For example, I would like to do something like this (simplified toillustrate the desired functionality... This should show all of the wordsfrom one table that are not contained in their current or inflectional formswithin another table):SELECT W1.ContentFROM(SELECT Word AS ContentFROM MyTable) W1LEFT OUTER JOIN(SELECT Phrase AS ContentFROM MyOtherTable) W2ON W2.Content CONTAINS(INFLECTIONAL, W1.Content)WHERE W2.Content IS NULLCan the results of a procedure be indexed? If not, can I drop the resultsinto a new table and trigger an automatic index of it, pausing the procedureuntil the indexing is done?Or, it there another way?Thanks!

View 2 Replies View Related

Strange Results When Calling A Sql Stored Proc From Within An Access2000 Project

Nov 18, 2006

Hi,I have written a stored procedure which includes a DATEPART command, i.e.DATEPART(weekday, <date>)The result when ran from SQL Query Analyser is as expected . i.e. Sundayreturns 1, Monday 2, etcWhen the same proc is called from within the Access 2000 project Sundayis returned as 7, Saturday as 6 instead of 1 and 7 respectively.Basically the same stored proc returns different data depending on fromwhere it has been called.This is causing some issues obviously as the resulting tables andreports are showing incorrect data when presented in Access 2000.Has anyone else experienced this before or have any idea what may cause it.Regards,PB

View 1 Replies View Related

Can You Trace Into A Stored Proc? Also Does RAISERROR Terminate The Stored Proc Execution.

Feb 13, 2008

I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code.
So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message:
Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages.
(1 row(s) affected)
(1 row(s) affected)
I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution?
Also, Is there a way to trace into a stored procedure through Query Analyzer?
-------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised:
SELECT  @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND  Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId
IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END 
 

View 3 Replies View Related

How To Compare Dynamic Variable In Proc

Sep 28, 2007

I have a table with 52 columns named 'Week1', 'Week2' etc. with values 1, 0 etc. I want to check values in each column. I have following lines in my procedure.

Declare @l_str varchar(50),
@l_count int

Select @l_count = 1
Select @l_str = 'Week' + Convert(varchar, @l_count)
Now how do I compare the value stored in the @l_str which should be wither 0 or 1 and not 'Week1'?

Is there any better method to compare read these 52 table variables?

Thanks in advance

View 3 Replies View Related

Compare Results

Jan 30, 2007

Ok need some help here.

What I am doing is trying to add something to my sql statement that will show records that don't match..
Here is what I am looking at.

Rec1 Rec2
DAL021DAL029
DAL032DAL0310
DAL054DAL0511

So what I want
is to select all the rows but either creates field that show me which Records don't match on Second field or some flag so I don't have to visual go throw and find them. Yes I can actualy write the Sql so it only pulls back the records that match on both fields but I need a way so you can look and Really see that result are true.


Hope this makes sence..

Select #Temp1.org_acronym, #Temp1.Maxcount, #Temp2.org_acronym, #Temp2.Maxcount
From #Temp1, #Temp2
Where #Temp1.org_acronym = #Temp2.org_acronym



Thanks
Michael Webb

View 1 Replies View Related

Compare Results From SQL To Textbox?

Dec 16, 2003

I am trying to make a user authentication system which pulls data from a SQL table (that has columns "UserID" and "PIN" in it).

How can I check to see what the user entered into the "UserID" and "PIN" textbox's on the page and compare those entries to the database to see if they match up? I have been able to make the below code to do the query itself, but I don't know how to check back and see if it actually returned a match or not.


Function CheckLogin(ByVal userID As String, ByVal pIN As String) As System.Data.DataSet
Dim connectionString As String = "server='(local)'; trusted_connection=true; database='test'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)

Dim queryString As String = "SELECT [UserAuth].[UserID], [UserAuth].[PIN] FROM [UserAuth] WHERE (([UserAuth].["& _
"UserID] like @UserID) AND ([UserAuth].[PIN] like @PIN))"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_userID As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_userID.ParameterName = "@UserID"
dbParam_userID.Value = userID
dbParam_userID.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_userID)
Dim dbParam_pIN As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_pIN.ParameterName = "@PIN"
dbParam_pIN.Value = pIN
dbParam_pIN.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_pIN)

Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
dataAdapter.SelectCommand = dbCommand
Dim dataSet As System.Data.DataSet = New System.Data.DataSet
dataAdapter.Fill(blah)

Return dataSet
End Function

View 3 Replies View Related

Stored Proc - Calling A Remote Stored Proc

Aug 24, 2006

I am having trouble executing a stored procedure on a remote server. On my
local server, I have a linked server setup as follows:
Server1.abcd.myserver.comSQLServer2005,1563

This works fine on my local server:

Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName

This does not work (Attempting to execute a remote stored proc named 'Data_Add':

Exec [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.Data_Add 1,'Hello Moto'

When I attempt to run the above, I get the following error:
Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'.
No entry found with that name. Make sure that the name is entered correctly.

Could anyone shed some light on what I need to do to get this to work?

Thanks - Amos.

View 3 Replies View Related

Stored Proc Question : Why If Exisits...Drop...Create Proc?

Jun 15, 2006

Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK

View 3 Replies View Related

ASP Cannot Run Stored Proc Until The Web User Has Run The Proc In Query Analyzer

Feb 23, 2007

I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.

View 1 Replies View Related

SQL Server 2012 :: How To Compare List Of Numbers Kind Of Like Lottery Results

Feb 5, 2015

Say you have a table that has records with numbers sort of like lottery winning numbers, say:

TableWinners
num1, num2, num3, num4, num5, num6
33 52 47 23 17 28
... more records with similar structure.

Then you have another table with chosen numbers, same structure as above, TableGuesses.

How could you do the following comparisons between TableGuesses and TableWinners:

1. Compare a single record in TableGuesses to a single record in TableWinners to get a count of the number of numbers that match (kind of a typical lottery type of thing).

2. Compare a single record in TableGuessess to ALL records in TableWinners to see which record in TableWinners is the closest match to the selected record in TableGuesses.

View 8 Replies View Related

Proc Results To Excel Workbook

Jan 12, 2007

Hello,

I have tried to use a DaraReader Source to execute a SQL Server 2000 proc, and send the results to an Excel Destination.

The proc is named spv_CDGetFuelManDuplicates. Within the proc, temporary tables are created and dropped. When I attempt to set 'Exec dbo.spv_CDGetFuelManDuplicates' for the SqlCommand property of the DataReader, I get an error message indicating that my temporary tables are invalid objects.

The DaraReader is using an ADO.NET Connection Manager.

Should I be using an Execute SQL Task instead?

Thank you for your help, the proc code is below.

cdun2

********************************

alter PROCEDURE dbo.spv_CDGetFuelManDuplicates

AS

--Isolate Accounts and Contact Info per the View criteria

SELECT

RTRIM(Acctcode) AS AcctCode,

RTRIM(CompanyName)AS CompanyName,

MMS#, RTRIM(Phone) AS Phone

INTO #CorrectAcctCodes

FROM dbo.DailyAccountsDownload

WHERE

(AcctCode BETWEEN 'IQ001' AND 'IQ999') OR

(AcctCode BETWEEN 'QI001' AND 'QI999') AND (AcctCode NOT IN ('QI125', 'QI601', 'QI603', 'QI613', 'QI623', 'QI653', 'QI697')) OR

(AcctCode BETWEEN 'DQ001' AND 'DQ999') AND (AcctCode NOT IN ('DQ011')) OR

(AcctCode BETWEEN 'FQ002' AND 'FQ999') AND (AcctCode <> 'FQ011')

--Isolate duplicate phone numbers

--per the view criteria

SELECT

RTRIM(Phone)AS Phone, COUNT(*) AS RecordCount

INTO

#DupPhoneNumbers

FROM

dbo.DailyAccountsDownload

WHERE

(AcctCode BETWEEN 'IQ001' AND 'IQ999') OR

(AcctCode BETWEEN 'QI001' AND 'QI999') AND (AcctCode NOT IN ('QI125', 'QI601', 'QI603', 'QI613', 'QI623', 'QI653', 'QI697')) OR

(AcctCode BETWEEN 'DQ001' AND 'DQ999') AND (AcctCode NOT IN ('DQ011')) OR

(AcctCode BETWEEN 'FQ002' AND 'FQ999') AND (AcctCode <> 'FQ011')

GROUP BY

RTRIM(Phone)

HAVING

(COUNT(*)>1)

--Isolate duplicate Company Names

--per the View criteria

SELECT

RTRIM(CompanyName)AS CompanyName,

COUNT(*) AS RecordCount

INTO

#DupCompanyName

FROM

dbo.DailyAccountsDownload

WHERE

(AcctCode BETWEEN 'IQ001' AND 'IQ999') OR

(AcctCode BETWEEN 'QI001' AND 'QI999') AND (AcctCode NOT IN ('QI125', 'QI601', 'QI603', 'QI613', 'QI623', 'QI653', 'QI697')) OR

(AcctCode BETWEEN 'DQ001' AND 'DQ999') AND (AcctCode NOT IN ('DQ011')) OR

(AcctCode BETWEEN 'FQ002' AND 'FQ999') AND (AcctCode <> 'FQ011')

GROUP BY

RTRIM(CompanyName)

HAVING

(COUNT(*)>1)

--Result with duplicate company or phone numbers

SELECT

Acctcode, CompanyName, MMS#, Phone

FROM

#CorrectAcctCodes

WHERE

Phone IN (SELECT Phone FROM #DupPhoneNumbers)

OR

CompanyName IN (SELECT CompanyName FROM #DupCompanyName)

ORDER BY

Phone, companyname

--Drop the temp tables

DROP TABLE #CorrectAcctCodes

DROP TABLE #DupCompanyName

DROP TABLE #DupPhoneNumbers

View 2 Replies View Related

Results To String Array For Reference Within CLR Proc

Jun 13, 2006

I keep running into a problem with the debugger freezing up. I'm trying to pull results from a query into an array (not more than 10 rows)

cmd.ExecuteReader();

while (rdr.Read() == true) {

letter = rdr[0].ToString();

i += 1;

}



if I comment out the "letter = rdr[0].ToString();" portion of code, the project runs fine. With that code there, I can not step into the proc from the SQL test script. No errors are raised. VS just freezes.

View 1 Replies View Related

Calling A Stored Proc From Within Another Stored Proc

Feb 20, 2003

I have seen this done by viewing code done by a SQL expert and would like to learn this myself. Does anyone have any examples that might help.

I guess I should state my question to the forum !

Is there a way to call a stored proc from within another stored proc?

Thanks In Advance.

Tony

View 1 Replies View Related

Stored Proc Calls Another Stored Proc

Jan 13, 2006

Hi all,

I have a stored procedure "uspX" that calls another stored procedure "uspY" and I need to retrieve the return value from uspY and use it within uspX. Does anyone know the syntax for this?

Thanks for your help!
Cat

View 5 Replies View Related

Calling Stored Proc B From Stored Proc A

Jan 20, 2004

Hi all

I have about 5 stored procedures that, among other things, execute exactly the same SELECT statement

Instead of copying the SELECT statement 5 times, I'd like each stored proc to call a single stored proc that executes the SELECT statement and returns the resultset to the calling stored proc

The SELECT statement in question retrieves a single row from a table containing 10 columns.

Is there a way for a stored proc to call another stored proc and gain access to the resultset of the called stored proc?

I know about stored proc return values and about output parameters, but I think I am looking for something different.

Thanks

View 14 Replies View Related

Calling T SQL Stored Proc From CLR Stored Proc

Aug 30, 2007

I would like to know if the following is possible/permissible:

myCLRstoredproc (or some C# stored proc)
{
//call some T SQL stored procedure spSQL and get the result set here to work with

INSERT INTO #tmpCLR EXECUTE spSQL
}

spSQL
(

INSERT INTO #tmpABC EXECUTE spSQL2
)


spSQL2
(
// some other t-sql stored proc
)


Can we do that? I know that doing this in SQL server would throw (nested EXECUTE not allowed). I dont want to go re-writing the spSQL in C# again, I just want to get whatever spSQL returns and then work with the result set to do row-level computations, thereby avoiding to use cursors in spSQL.

View 2 Replies View Related

Different Results When Executing From .NET Component Compare To Executing From SQL Management Studio

Oct 10, 2006

Hi all,I am facing an unusual issue here. I have a stored procedure, that return different set of result when I execute it from .NET component compare to when I execute it from SQL Management Studio. But as soon as I recompile the stored procedure, both will return the same results.This started to really annoying me, any thoughts or solution? Thanks very much guys

View 2 Replies View Related

Execute Stored Procedure Y Asynchronously From Stored Proc X Using SQL Server 2000

Oct 14, 2007

I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.

View 3 Replies View Related

How Can I Call One Or More Stored Procedures Into Perticular One Stored Proc ?

Apr 23, 2008

Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?



How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.

View 1 Replies View Related

Calling A Stored Procedure From Within A Stored Proc

Dec 18, 2007

Hi Peeps
I have a SP that returns xml
I have writen another stored proc in which I want to do something like this:Select FieldOne, FieldTwo, ( exec sp_that_returns_xml ( @a, @b) ), FieldThree from TableName
But it seems that I cant call the proc from within a select.
I have also tried
declare @v xml
set @v = exec sp_that_returns_xml ( @a, @b)
But this again doesn't work
I have tried changing the statements syntax i.e. brackets and no brackets etc...,
The only way Ive got it to work is to create a temp table, insert the result from the xml proc into it and then set @v as a select from the temp table -
Which to be frank is god awful way to do it.
 Any and all help appreciated.
Kal

View 3 Replies View Related

How To Compare Two Stored Procedures

Apr 19, 2001

Hi,

The Process that goes on here is developers send the stored procedure i deloy them on development machine and upon request send them to QA / PROD

The problem here they send sp's quite often and i update them every time..now because of some mismatch they want to revert to the old version...back i don't know how to do it...and the possiblity is if i can compare the version they r having a the version in database that will help me a lot..

How do i campare the storedprocedure to check if both the one are similar or not.

And can any one guide me what's the basic process that normally any of u guys undergo so that u can revert back to the previous with basically saving the script file everytime..what r the standards and name conventions that need to be followed.

I know there r lots of issues i expressed ..thanks a lot for u help and suggestions...

Kavitha

View 2 Replies View Related

Using A Stored Procedure To Query Other Stored Procedures And Then Return The Results

Jun 13, 2007

Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB  multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived,     I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID,    S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName',    T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID    FROM [Item].ItemDetails I    INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID    INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived,     I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID    FROM [Item].ItemDetails IOr something like that... Any thoughts? 

View 3 Replies View Related

Stored Procedure To Compare Dates

Mar 20, 2008

Hi:

Well, I am venturing into new territory for me. I'm very illiterate when it comes to SQL Server and so I need assistance. I have the beginnings of my stored procedure, which is supposed to compare two dates/times and If they are not equal I need to kick off a DTS Package.

So, here's what I have so far (it returns two dates like I would expect):

CREATE PROCEDURE usrCompareDataDownload
AS
BEGIN
SELECT MAX(ASP_ZZ_CHNG_TMST) FROM tbl_MaterialWeeklyData;
SELECT MAX(ZZ_CHNG_TMST) FROM TV_ASP_DPUL_WKLY;

END
GO


Thanks,

Bob Larson

View 5 Replies View Related

Compare Datetime In Stored Procedure

Mar 14, 2008

Hi,

Can i compare date = null in stored procedure? Does this work? The syntax works but it never get into my if else statement? The weird thing here is it has been working and just this morning, it stopped. I don't know why? Could you please help? Thanks,


set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go











ALTER PROCEDURE [dbo].[UpdateActiveStates1]

@PROVIDERID INT

AS

DECLARE @STARTDATE DATETIME

DECLARE @ENDDATE DATETIME

DECLARE @ACTIVE BIT

SET @ACTIVE = 0

SET @STARTDATE = (SELECT ProhibitionStartDate FROM Providers WHERE ProviderID=@PROVIDERID)

SET @ENDDATE = (SELECT ProhibitionEndDate FROM Providers WHERE ProviderID=@PROVIDERID)

IF ((@STARTDATE = NULL OR @STARTDATE = '1753-01-01') AND (@ENDDATE = NULL OR @ENDDATE = '1753-01-01'))

BEGIN

PRINT 'Setting Inactive due to NULL Start Date and NULL End Date'

SET @ACTIVE=0

END

ELSE IF (@STARTDATE != NULL AND @STARTDATE <= GETDATE())

BEGIN

IF (@ENDDATE = NULL)

BEGIN

PRINT 'Setting Active due to NON-NULL Start Date and NULL End Date'

SET @ACTIVE=1

END

ELSE IF (@ENDDATE >= GETDATE())

BEGIN

PRINT 'Setting Active due to NON-NULL Start Date and NON-EXPIRED End Date'

SET @ACTIVE=1

END

ELSE

BEGIN

PRINT 'Setting Inactive due to NON-NULL Start Date and EXPIRED End Date'

SET @ACTIVE=0

END

END

UPDATE Providers SET Active=@ACTIVE

WHERE ProviderID=@PROVIDERID

UPDATE Actions SET Active=@ACTIVE WHERE ProviderID=@PROVIDERID







View 2 Replies View Related

SSIS And Stored Procedures Results Stored In #Tables

Mar 26, 2008

Hello
I'm start to work with SSIS.

We have a lot (many hundreds) of old (SQL Server2000) procedures on SQL 2005.
Most of the Stored Procedures ends with the following commands:


SET @SQLSTRING = 'SELECT * INTO ' + @OutputTableName + ' FROM #RESULTTABLE'

EXEC @RETVAL = sp_executeSQL @SQLSTRING


How can I use SSIS to move the complete #RESULTTABLE to Excel or to a Flat File? (e.g. as a *.csv -File)

I found a way but I think i'ts only a workaround:

1. Write the #Resulttable to DB (changed Prozedure)
2. create data flow task (ole DB Source - Data Conversion - Excel Destination)

Does anyone know a better way to transfer the #RESULTTABLE to Excel or Flat file?

Thanks for an early Answer
Chaepp

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved