[thelist] Conditionals with a SQL Server 2000 Stored Procedure
Darren Neimke
Darren.Neimke at sdm.com.au
Tue Jun 4 18:39:05 CDT 2002
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
More information about the thelist
mailing list