Link to home
Start Free TrialLog in
Avatar of Scott Kricho
Scott KrichoFlag for United States of America

asked on

SQL Stored Procedure Question: How can I combine two stored procedures into one

I have two stored procedures running on Microsoft SQL Server 2000 and 2005. Both of them work fine and I am able to call them from my application.

I would like to combine the two SP into one. My app calls SP1 which returns a recordset. I eveluate the data in the recordset and then, if necessary, call SP2.

I am trying to figure out how to evaluate the output from the query in SP1 and if needed execute the query in SP2...but all from within one SP.

Here are the stored procedures:

SP!:

CREATE PROCEDURE [dbo].[CheckEditVersionStamp]

@strTableName nvarchar(255),
@strIDField nvarchar(255),
@strIDValue nvarchar(255)

AS


DECLARE @SQLStatement varchar(255)

SELECT @SQLStatement = 'SELECT EditVersionStamp, EditVersionTime' +
                                             ' FROM ' + @strTableName +
                                             ' WHERE ' + @strIDField + ' = "' + @strIDValue + '"'

EXEC(@SQLStatement)
GO

**********************************************************
SP2:

CREATE PROCEDURE [dbo].[SaveEditVersionStamp]

@strTableName nvarchar(255),
@UserID nvarchar(255),
@strUserIDField nvarchar(255),
@strUserIDValue nvarchar(255)


AS


DECLARE @Time nvarchar(255)

set @Time = CONVERT(nvarchar, GetDate())

DECLARE @SQLStatement varchar(255)


SELECT @SQLStatement = 'UPDATE ' + @strTableName +
                                             ' SET EditVersionStamp = "' + @UserID + '", EditVersionTime = "' + @Time + '"' +
                                             '  WHERE ' + @strUserIDField + ' = ' + '"' + @strUserIDValue + '"'

EXEC(@SQLStatement)
GO

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

Thanks.
Avatar of Aneesh
Aneesh
Flag of Canada image

how will you do your evaluation



CREATE PROCEDURE [dbo].[SaveEditVersionStamp]

@strTableName nvarchar(255)  = null ,
@UserID nvarchar(255),
@strUserIDField nvarchar(255),
@strUserIDValue nvarchar(255)


AS


DECLARE @Time nvarchar(255)

set @Time = CONVERT(nvarchar, GetDate())

DECLARE @SQLStatement varchar(255)

IF @strTableName IS NULL

SELECT @SQLStatement = 'SELECT EditVersionStamp, EditVersionTime' +
                                             ' FROM ' + @strTableName +
                                             ' WHERE ' + @strIDField + ' = "' + @strIDValue + '"'

ELSE
SELECT @SQLStatement = 'UPDATE ' + @strTableName +
                                             ' SET EditVersionStamp = "' + @UserID + '", EditVersionTime = "' + @Time + '"' +
                                             '  WHERE ' + @strUserIDField + ' = ' + '"' + @strUserIDValue + '"'

EXEC(@SQLStatement)
GO
Avatar of Scott Kricho

ASKER

The first query returns an EditVersionStamp and an EditVersionTime.

If EditVersionStamp is NULL execute the second query.

If EditVersionStamp is NOT NULL and is different than @UserID, compare EditVersionTime to @Time.
     If more than 15 minutes, execute second query.
     If less than 15 minutes do nothing.

If EditVersionStamp is NOT NULL and equals @UserID, do nothing.
I have increased the points to 500 because this is very time-sensitive. I know how to apply the IF...THEN statements to determine if the second query needs to be executed. What I do not know how to do is 'read in' the results of the first query.

The first query is designed to return a single record with two fields: EditVersionStamp and EditVersionTime. I need to know how to place these values in variables so I can evaluate them in the IF...THEN statements.

Thanks.
ASKER CERTIFIED SOLUTION
Avatar of Aneesh
Aneesh
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you aneeshattingal for the response but I'm not sure I understand how to make this work for the logic choices I need to make. I don't know if it can be done with a single SQL statement.

I guess my real question is: is it possible within a stored procedure to execute a SQL statement and place the results into something like a recordset. I would like to execute the first query

SELECT @SQLStatement = 'SELECT EditVersionStamp, EditVersionTime' +
                                             ' FROM ' + @strTableName +
                                             ' WHERE ' + @strIDField + ' = "' + @strIDValue + '"'

and then do something like this:

IF EditVersionStamp IS NULL
     Do this
ELSEIF EditVersionStamp = @UserID
     Do this
ELSE
     Do this
END

Is this possible?

Thanks.