[thelist] SProcs - SELECT queries

[Brian Cummiskey]

Chris at globet.com wrote:

> 1) How would I go about so structuring a stored procedure with optional
> parameters that I could then use in different places for different
> queries?
> 

you can pass parameters into a SP no problem.  SQL supports if statements.

> 2) Is this possible?

sure is.

> 
> 3) If so, is it a good idea?
> 

yup.  SP's are more efficient most of the time, and are way more secure 
as well.



in your asp/html:

<%
	dim rst, comm, connstr
	connstr = ConnStr = 
"Provider=SQLOLEDB.1;UID=USERNAME;Password=PASSWORD;Persist Security 
Info=False;Initial Catalog=DBNAME;Data Source=SERVERNAME"

	server.ScriptTimeout = 3600
	set comm = Server.CreateObject ("ADODB.Command")
	set rst = Server.CreateObject("ADODB.Recordset")
	comm.CommandTimeout = 0
	comm.ActiveConnection = connstr
	comm.CommandText = "usp_DO_SELECT"	' or, whatever you call your SP
	comm.CommandType = adCmdStoredProc
	comm.Parameters("@bCondition1") = request("bCondition1")
	comm.Parameters("@bCondition2") = request("bCondition2")
	comm.Parameters("@bCondition3") = request("bCondition3")
	comm.Parameters("@bCondition4") = request("bCondition4")
	comm.Parameters("@aValue") = request("aValue")
	comm.Parameters("@bValue") = request("bValue")
	comm.Parameters("@cValue") = request("cValue")
	comm.Parameters("@dValue") = request("dValue")
	
	set rst = comm.Execute
	
	set comm = Nothing
	
	if not rst.EOF then
		' do your html/results html table stuff here
	else
		response.write "No results"
	end if
%>

	
and for the procedure...
-------------------------------------

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO




CREATE proc usp_DO_SELECT
(
@bcondition1 varchar(20),
@bcondition2 varchar(20),
@bcondition3 varchar(20),
@bcondition4 varchar(20),
@aValue varchar(20),
@bValue varchar(20),
@cValue varchar(20),
@dValue varchar(20)
)
as
begin
set nocount on

declare @queryString varchar(8000)

set @querystring = 'SELECT aField, bField, cField, dField FROM aTable 
WHERE 1=1 '

	if(@bcondition1 is not null)
	begin
		set @querystring = @querystring + 'AND aField = @aValue '
	end
	if(@bcondition2 is not null)
	begin
		set @querystring = @querystring + 'AND bField = @bValue '
	end
	if(@bcondition3 is not null)
	begin
		set @querystring = @querystring + 'AND cField = @cValue '
	end
	if(@bcondition4 is not null)
	begin
		set @querystring = @querystring + 'AND dField = @dValue'
	end
	
exec (@queryString)

end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO	

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

of course, change the varchar(20) to fit your needs.

this is untested, so there may be a few parse errors... but its the 
general idea.