Proc Returning all records - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Proc Returning all records

Author

Topic

pssclient3
Starting Member

1 Post

Posted - 2006-11-17 : 23:19:30

Following Proc is returning all records.I have passed any value to parameters but no luck.

CREATE PROCEDURE GetAllUsers(
@persontype varchar(100)="",
@name varchar(100)="",
@adddatetime datetime="",
@activeaccount int =-1,
@country varchar (20)=""
)
AS
declare @cond varchar(1000) ;

if @persontype<>""
begin
set @cond= @cond+"and persontype="+@persontype
end
if @name<>""
begin
set @cond= @cond+"and (charindex("+@name+",x_firstname)>0 or charindex("+@name+",x_lastname)>0 or charindex("+@name+",x_email)>0) "
end
if @activeaccount<>-1
begin
set @cond= @cond+'and activeaccount='+@activeaccount
end
if @adddatetime<>""
begin
set @cond= @cond+'and adddatetime='+@adddatetime
end
if @country<>""
begin
set @cond= @cond+'and x_country='+@country
end
print @cond
exec( " select * from users where 1=1 "+@cond)
GO

zx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-17 : 23:43:21

[code]CREATE PROCEDURE dbo.uspGetAllUsers
(
@persontype varchar(100) = null,
@name varchar(100) = null,
@adddatetime datetime = null,
@activeaccount int = null,
@country varchar(20)= null
)
AS

set nocount on

declare @cond varchar(8000)

select @cond = 'select * from users where 1 = 1'

if @persontype is not null
set @cond = @cond + ' and persontype = ''' + @persontype + ''''

if @name is not null
set @cond = @cond + ' and x_firstname like ''%' + @name + '%'' or x_lastname like ''%' + @name + '%'' or x_email like ''%' + @name + '%'''

if @activeaccount is not null
set @cond = @cond + ' and activeaccount = ' + convert(varchar, @activeaccount)

if @adddatetime is not null
set @cond = @cond + ' and adddatetime = ''' + convert(varchar, @adddatetime, 120) + ''''

if @country is not null
set @cond = @cond + ' and x_country = ''' + @country + ''''

print @cond
exec(@cond)
GO[/code]Improve your understanding about Dynamic SQL here http://www.sommarskog.se/dynamic_sql.html

Peter Larsson
Helsingborg, Sweden

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-11-17 : 23:45:16

Please have a look here before putting this code into production, there are better ways to do this:

http://sommarskog.se/dyn-search.html

and make sure you read and understand the article below before using dynamic sql. Your sproc is a great way to let someone take control of your server thru sql injection as it is written:

http://sommarskog.se/dynamic_sql.html (EDIT: looks like Peso beat me to it!)

Finally, you shouldn't use "select *" in any production code. You will break clients as soon as you add a new column to the table.

SqlSpec - a fast, cheap, and comprehensive data dictionary generator for
SQL Server 2000/2005 and Analysis Server 2005 - http://www.elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources