Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-30 : 08:30:22
|
| Annie writes "WIN2K/SQL 7.0QUESTION=========Trying to build an SP to search based on several criteria, any of which can be NULL. This works fine until I put it in dynamic SQL (had to due to using a dynamic IN clause), which returns no results if any of the values passed in are NULL. e.g exec usp_SearchFilm "C%" worksbut exec usp_SearchFilm NULL does not, and the PRINT statement does not show despite no error being thrown.Below is an example with just one param. The default to NULL for the params works if it's not dynamic SQL. Am I doing something wrong, or have I encountered a limitation of dynamic SQL?Even the SQL gurus (supposedly!) in my company cannot solve this one! Help!Thanks,AnnieCREATE PROCEDURE usp_SearchFilm @FilmDescr varchar(100) = NULL, @Debug tinyint = nullASDECLARE @SQL varchar(2000) SET @SQL = 'SELECT F.FilmID, F.Title, F.State' SET @SQL = @SQL + ' FROM Film F' SET @SQL = @SQL + ' WHERE F.Descr LIKE COALESCE(''' + @FilmDescr + ''', F.Descr) ' IF @Debug IS NOT NULL PRINT @SQL ELSE EXEC(@SQL)GO" |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-05-30 : 09:39:13
|
| Annie,When you concatenate a NULL with a string, the result is NULL if you have ANSI NULLS set on (the default). That's why your PRINT statement doesn't display anything - the value of @SQL is null. You can test that by modifying the PRINT statement to be: print isnull(@sql,'@sql is null').setBasedIsTheTruepath<O> |
 |
|
|
|
|
|
Can't use COALESCE with Dynamic SQL in SP?