Search option in Stored Procedure

[ironhide1975]

Greetings,
I'm still working through learning Stored Procedures and SQL language. I'm trying to set up a Stored Procedure that if a Search Variable is passed to the procedure that it uses that to search the fields, and if not, just return all results normally. If someone can help me possibly work through what I'm missing here, I would appreciate it.

[sp_GetTXZipCodesListing](
@OrderByClause varchar(10) = NULL,
@Search varchar(50) = NULL,
@ID varchar(10) = NULL

)

AS

Select ID, Zipcode, County, City, [State], GSA_Name, GSA_Designation, Certified
From   [ZIP-GSALookup] 
WHERE Active = 1
AND (
    (@Search IS NOT NULL AND ZipCode = @Search)
    )
  ORDER BY
	CASE 
	 WHEN @OrderByClause='ZipCode' THEN ZipCode + County + City
     WHEN @OrderByClause='County' THEN County + ZipCode + City
       
  END

 

[Crowley16]

there's 2 approaches to this, either using dynamic sql or with an if check.

dynamic sql:
declare @sql as varchar(5000)
set @sql = 'select * from table'
if @search is not null
@sql = @sql + 'where col = ' + @search
sp_executesql @sql

if check:
if @search is not null begin
select * from table where col = @search
end
else begin
select * from table
end

or something like that...

--------------------
Procrastinate Now!

 

[ca8msm]

You should avoid the dynamic sql route. Instead, you can make parameters optional like this:

[COLOR=blue]create[/color] [COLOR=blue]procedure[/color] test
(
  @id [COLOR=blue]int[/color] = null
)
[COLOR=blue]as[/color]
[COLOR=blue]select[/color] id
[COLOR=blue]from[/color] mytable
[COLOR=blue]where[/color] (@id [COLOR=blue]is[/color] null or id = @id)

-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[SQLSister]

Don't use dynamic SQl, it is a bad practice (very insecure and not very efficient and a pain to properly test) and as Crowly said there is an alternative.

Another solution, assuming the variable is defined in the input variable section of the sp

select top 10 * from table where  @test is null or 
[filename] = @test

Also it is not a good idea to name stored procs starting with sp. This is the prefix Microsoft uses for system stored procs and it will loook for them first before looking for user procs. This takes more time and if one ever has the same name, then your proc will not run. Use usp_ (for userstored proc) instead or some other variant.

"NOTHING is more important in a database than integrity." ESquared

 

[ironhide1975]

cool that helps a lot, now is there a way to put a count record in here somewhere to let me know how many items are in my table?

here's my revised code

CREATE PROCEDURE [dbo].[usp_GetTXZipCodesListing](
@OrderByClause varchar(10) = NULL,
@Search varchar(50) = NULL,
@ID varchar(10) = NULL
)

AS

IF @ID IS NOT NULL BEGIN
	
	Select ID, Zipcode, County, City, [State], GSA_Name, GSA_Designation, Certified
	From   [ZIP-GSALookup] 
	WHERE Active = 1 AND ID = @ID

END
ELSE BEGIN

	Select ID, Zipcode, County, City, [State], GSA_Name, GSA_Designation, Certified
	From   [ZIP-GSALookup] 
	WHERE Active = 1
	AND 
		(@Search IS NULL OR ZipCode = @Search)

	  ORDER BY
		CASE 
		 WHEN @OrderByClause='ZipCode' THEN ZipCode + County + City
		 WHEN @OrderByClause='County' THEN County + ZipCode + City
	       
		END

END

is there a way to stick this in here somewhere?

sSQL = "SELECT COUNT(*) as 'CountID' FROM [ZIP-GSALookup] WHERE Active = 1"

 

[SQLSister]

look up @@rowcount in BOL

BTW you are aware that order by zipcode+county+city would give different results than the more common zipcode, county, city? and it would probably be slower

"NOTHING is more important in a database than integrity." ESquared

 

[SQLSister]

to go aliong with my last comment, here is an example to run to show how the sorts give differnt results when using the + vice the commas

create table #temp (city varchar (20), state varchar(2))
insert into #temp
select 'detroit', 'mi'
union
select 'detroite', 'wv'
union
select 'hanover', 'md'
union
select 'han', 'tn'

select * from #temp order by city+state

select * from #temp order by city,state

as you can see concatentating is very differnt from sorting on first one column and then the next. I just wanted to be sure you were intending the result you get.

"NOTHING is more important in a database than integrity." ESquared

 

[ironhide1975]

where is this BOL i keep hearing about?

 

[ca8msm]

And a quick google produces...

http://msdn2.microsoft.com/en-us/library/ms130214.aspx

-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[bborissov]

Hmmm,
Just press F1
That is BOL

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[ironhide1975]

Okay that may be a little more complicated than I need (or that I understand). I need to know the total count BEFORE I execute the one procedure to bring back all the rows. Is it possible to just write a seperate (simple) stored procedure, you could submit the table name to it, and it could tell you the total amount of records in the table?

 

[SQLSister]

In the first place, the only way to do what you suggest is to use dynamic SQL which is a very bad idea and you should not use it just to save yourself a little work. Dynamic SQL can get you into a world of trouble if you get a SQL injection attack. It's a fast track to getting fired.
Anytime you think I just want to be able to just put in whatever table name you want in a proc and have it run against differnt tables, stop right there and don't even consider doing it unless you are doing a database administration type of task. If the user will have access to the proc, do not use dynamic SQL.

You could add an if statement and only execute the select statement if less than a set number of rows were returned.

if (select count(*) from table1 where active = 1) < 10000
begin
select field1, field2, from table1 where active = 1
end
[code]
Or you could always only return the top 1000 or whatever the max number of rows you want is (always use an order by if you do this)





"NOTHING is more important in a database than integrity." ESquared

 

[ironhide1975]

Well whats a good way then to get a total number of records from a table, and be able to create a page through for a web user interface. For example, if the table has 32153 records, I need to be able to allow the user to page through 50 or 100 at a time, and be able to adjust how many show up on a page?

Am I makin sense or no?

And thank you for your help, I really do appreciate it.