SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Proc Returning all records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pssclient3
Starting Member

1 Posts

Posted - 11/17/2006 :  23:19:30  Show Profile  Reply with Quote
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

Sweden
30240 Posts

Posted - 11/17/2006 :  23:43:21  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Improve your understanding about Dynamic SQL here http://www.sommarskog.se/dynamic_sql.html


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 11/17/2006 :  23:45:16  Show Profile  Visit jezemine's Homepage  Reply with Quote
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

Edited by - jezemine on 11/17/2006 23:47:08
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000