[thelist] Conditionals with a SQL Server 2000 Stored Procedure

[Darren Neimke]

Scott,

In SQL Server programming you can create ad-hoc sql inside your sprocs
and execute them.  Try running the following statements from within
Query Analyzer:

----------------------------------

USE Northwind

DECLARE @SQLStrng VARCHAR( 2000 )
DECLARE @whereItem VARCHAR( 40 )
DECLARE @whereValue INT



SELECT @SQLStrng = 'SELECT * FROM Categories'

IF LEN( @whereItem ) > 0 AND LEN( @whereValue ) > 0
BEGIN
	SELECT @SQLStrng = @SQLStrng + ' WHERE ' + @whereItem + ' = ' +
CAST( @whereValue AS VARCHAR )
END

-- run the generic select statement

PRINT @SQLStrng
EXEC ( @SQLStrng )


-- now populate the local variables

SET @whereItem = 'CategoryID'
SET @whereValue = 3


-- rebuild the sql statement

SELECT @SQLStrng = 'SELECT * FROM Categories'

IF LEN( @whereItem ) > 0 AND LEN( @whereValue ) > 0
BEGIN
	SELECT @SQLStrng = @SQLStrng + ' WHERE ' + @whereItem + ' = ' +
CAST( @whereValue AS VARCHAR )
END

-- re-run it

PRINT @SQLStrng
EXEC ( @SQLStrng )

----------------------------------


It means that you can write sprocs like this:

----------------------------------

CREATE PROCEDURE TestSproc

	@whereItem VARCHAR( 40 ) = null,
	@whereValue INT = null

AS

DECLARE @SQLStrng VARCHAR( 2000 )

SELECT @SQLStrng = 'SELECT * FROM Categories'

IF @whereItem IS NOT NULL AND @whereValue IS NOT NULL
BEGIN
	SELECT @SQLStrng = @SQLStrng + ' WHERE ' + @whereItem + ' = ' +
CAST( @whereValue AS VARCHAR )
END

EXEC ( @SQLStrng )
GO

----------------------------------

And call them like so:


	exec TestSproc 'CategoryID', 3
	exec TestSproc