Home > SQL Development > Stored procedure help

Stored procedure help

How can i write the below in a stored procedure. I have added the procedure where i want to add it and where i want it adding but not sure of teh correct way to write it.

If SERVICEBOOKEDDATE date IS NULL

Then get todays date

Code:

ALTER PROCEDURE [dbo].[rsp_ServicedWithinLastYear]



@CONTRACT VARCHAR(3)



AS



	DECLARE @SQLSELECT VARCHAR(1000)

	DECLARE @SQLWHERE VARCHAR(1000)

	

	

SELECT @SQLSELECT = 'SELECT     TBL_PROPERTY.PROPREF, TBL_PROPERTY.CONTRACT, TBL_PROPERTY.FULLADDRESS, TBL_PROPERTY.LASTSERVICEDATE, TBL_PROPERTY.SERVICEBOOKEDDATE FROM TBL_PROPERTY '

SELECT @SQLWHERE = 'WHERE '



-- CONTRACT

	

	IF @CONTRACT <> 'ALL'

		SELECT @SQLWHERE = @SQLWHERE +  '(CONTRACT = ''' + @CONTRACT + ''') '



--GET SERVICEBOOKEDDATE IF NULL

	IF TBL_PROPERTY.SERVICEBOOKEDDATE IS NULL

		Then TBL_PROPERTY.SERVICEBOOKEDDATE = GetDate() 



EXEC (@SQLSELECT + @SQLWHERE)

Guest

I made a couple of changes here. One, instead of separating the SQL statement into a Select and Where, I just combined it into one variable. Otherwise you'll need to add an additional check to avoid using the WHERE if the Contract variable were equal to All. And second, the check for the null is a built-in function from SQL Server called ISNULL. It replaces any null values with the default you provide. Try it out and see if it works okay.

Code:

ALTER PROCEDURE [dbo].[rsp_ServicedWithinLastYear]



@CONTRACT VARCHAR(3)



AS



DECLARE @SQL VARCHAR(1000)



SELECT @SQL = 'SELECT PROPREF, CONTRACT, FULLADDRESS, LASTSERVICEDATE, ISNULL(SERVICEBOOKEDDATE, getdate()) FROM TBL_PROPERTY '



-- CONTRACT

	IF @CONTRACT <> 'ALL'

		SELECT @SQL = @SQL + 'WHERE (CONTRACT = ''' + @CONTRACT + ''') '



EXEC @SQL

Was this answer helpful ? Yes No

Guest

ok will let you know how i get on, thanks

Was this answer helpful ? Yes No

Guest

Answer it

Ask Question About SQL Development

Stored procedure help

Programming

Related Pages