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-06-24 : 09:07:25
|
| Kathy writes "Here is my procedure and it is still not working. I am wanting to use a dynamic procedure which the user selects any combination for the search and also searches with a dynamic order by and sort. Alter Procedure sp_DynamicSearchOrderBy ( @RecordNumber int=null, @Last_Name [varchar] (30)=null, @First_Name [varchar](30)=null, @Gender [varchar](10)=null, @Race [varchar](10)=null, @orderby [varchar](30)=null, @sort [varchar](10)=null, @maxrows int=0) as declare @sql varchar(1000) set Rowcount @maxrows set @sql='SELECT juvSysID AS [Record Number], juvNameLast AS [Last Name], juvNameFirst AS [First Name], juvPrimaryRace AS Race, juvGender AS Gender FROM dbo.dbo_tblJuvenile where juvSysID =coalesce(@RecordNumber,juvSysID) and juvNameLast =coalesce(@Last_Name,juvNameLast) and juvNamefirst=coalesce(@First_Name,juvNameFirst) and juvGender=coalesce(@Gender,juvGender) and juvPrimaryRace=coalesce(@Race,juvPrimaryRace) order by' + @orderby + @sort exec(@sql) " |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-06-24 : 09:16:34
|
| comment out the exec(@sql) and replace it w/ a print @sql statement. See what you're executing and the problem will likely present itself right away.Jonathan Boott, MCDBA |
 |
|
|
|
|
|
|
|
Dynamic Search Procedure with Dynamic Oder by and Sort