Stored procedure ! - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Stored procedure !

Author

Topic

pacha1
Starting Member

4 Posts

Posted - 2004-10-04 : 16:26:18

Alright guys, I am new around here and need some help !

I need to conver all off my "Dynamic SQL" from a transactional web site to Stored procedure in ASP 3.0.

I need to create a stored procedure that could handle more than one "option". Here is my ASP Code, it will give you the whole picture.


<%
if not request("sql") = "true" then
 
 if request.form("b2") = "Go!" then
  MySql = "select * from orders where orderid like '" & request.form("NoOrder") & "%'" & " ORDER BY orderid desc"
 end if
 
 if request.form("b3") = "Submit" then
  MySql = "SELECT * from Orders where userid like '" & request.form("userid") & "%'"
 
  if not request("type") = "all" then
   MySql = MySql & " AND description like '%" & request.form("Type") & "%'"
  end if
 
  if not request.form("date1") = "" then
  if not request.form("date2") = "" then
   MySql = Mysql & " AND date Between #" & request.form("date1") & "# And #" & request.form("date2") & "#"
  end if
  end if
 
  if not request.form("aprob") = "all" then
   MySql = Mysql & " AND aprob='" & request.form("aprob") & "'"
  end if
 
 end if
 
else
MySql = session("mysql")
end if
%>

You have to add sentence with each others if they are allowed.

So in my sql i shoud be able to say :


If not my criteria3 is empty then
    mysql = mysql + "..."
end if


Then ill go with EXEC(mysql) i supose ??

So, how do I do the trick ??

Thanks !

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-04 : 16:30:31

You've posted code but haven't explained what you want it to do. Please explain in words what you are trying to do. Based upon your explanation, we might be able to come up with a better solution that does not use dynamic SQL.

BTW, is the MySql or MS SQL Server?

Tara

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-10-05 : 01:51:29

something on these lines....

create procedure my_proc
( @flag varchar(15)
,@No_order varchar(15) = null
,@userid varchar(15) = null
,@type varchar(15) = 'all'
,@date1 datetime = null
,@date2 datetime = null
,@aprob varchar(15) = null
)
as
declare @MySql varchar(2000)

If @flag 'Go!'
Select @MySql = 'select * from orders where orderid like ''' + @no_order + '%'' ORDER BY orderid desc'
end if

If @flag 'Submit'
Begin
Select @MySql = 'SELECT * from Orders where userid like ''' + @userid + '%'' '
If @type <> 'all'
Select @MySql = @MySql + ' AND description like ''%' + @type + '%'' '

If not (isnull(@date1,'1900-01-01') = '1900-01-01' or isnull(@date2,'1900-01-01') = '1900-01-01' )
Select @MySql = @MySql + ' AND date Between ''' + convert(varchar(15), @date1) + ''' and ''' + convert(varchar(15), @date2) + ''' '

If @aprob <> 'all'
Select @MySql = @MySql + ' AND aprob = ''' + @aprob + ''' '
End

exec(@MySql)

N.B : Note that procs like these do not have any performance gain over dynamic sql.

Hemanth Gorijala
BI Architect / DBA...

pacha1
Starting Member

4 Posts

Posted - 2004-10-06 : 08:57:45

wow !!! thanks alot,

For the performance, it is not an issue, i was only for security reason (sql injection).

Nice code!

Thanks again!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-06 : 09:02:36

looks like my ESP rubbed off on someone...