Parameterized 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)

Parameterized Procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-04 : 14:02:05

Subhas writes "My problem goes like this..I have doing a timesheet application. There is a feature where the administrator will generate reports based on certain categories.
The page looks like this:

1. The user has to select the First Date and Last Day, the period which he wants the report.
2. There is a list box, containing list of employees and other 2 list boxes, which contains Project names and Client names.
3. If the administrator choose employee name, then he gets report for that employee only, and if he choose project, then the report should contain all employees working in the project.
If however he choose client, then he can view list of employees working for diff projects under that client.

I am thinking of writing a parameterized stored procedure, by calling which I can dynamically change the report.
IS THAT FEASIBLE? Pls suggest.
also give me tips of how to write parameterized procedure..

thanks..
pls help."

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-04 : 17:57:12

You really only have two basic reports - time and employee list - and my suggestion would be to keep your SQL pretty simple.

I do this all the time, and I highly recommend that you keep your SQL to simple SELECT statements where possible (rather than stored procedures) - unless you have a need. This is so there is only one place to maintain the code.

Get your page (Access, ASP, VB whatever) to do the choosing, and to build the where clause of the SQL statement: ie

dim strwhere as string

if frm.cboStartDate.Text <> "" then
strwhere = "WHERE ts_date >=CONVERT(datetime,'" & frm.cboStartDate.Text & "'"
end if

if frm.cboEndDate.Text <> "" then
if strwhere <> "" then
strwhere = strwhere & " AND "
else
strwhere = "WHERE "
end if
strwhere = strwhere & " ts_date <=CONVERT(datetime,'" & frm.dboEndDate.Text & "'"
end if

if frm.cboProject.Text <> "" then

end if

etc etc etc.

I often also have option inclusions like - "breakdown by weekending" etc in which case I'd have something like:

dim strcolumnlist as string

if chkWeekending.Value= True then
strcolumnlist = "employee, weekending"
else
strcolumnlist = "employee"
end if

then your SQL (for the time report) can be something like:

sSQL = "select " & strcolumnlist
sSQL = sSQL & ", sum(hours) as total from ts " & strwhere
sSQL = sSQL & " group by " & strcolumnlist
sSQL = sSQL & " order by " & strcolumnlist

Hope that helps. If not - ask more.

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-03-04 : 20:30:48

quote:
I do this all the time, and I highly recommend that you keep your SQL to simple SELECT statements where possible (rather than stored procedures) - unless you have a need. This is so there is only one place to maintain the code.

Not to be confrontational or spark The Great Justification for Stored Procedures Debate, but I beg to differ. I would suggest that using a stored procedure is exactly the way that you ensure there is only one place to maintain the code, and that stored procedure can be used from multiple pages. Yes, you can argue that if you have multiple pages calling one stored procedure, and we change a parameter, then you have to change every page that calls the sproc (unless we make the parameter optional). But I would argue that is better than having to update multiple pages when all you want to do is change the ORDER BY or column list returned.

What you are doing in your solution is to build Dynamic SQL on the client rather than Dynamic SQL in a stored procedure, so the basic technique is the same.

Subhas, you can search this site for articles on Dynamic SQL and also Stored Procedure Parameters for more information.

------------------------
GENERAL-ly speaking...

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-04 : 20:37:18

General-ly I would agree - TWICE !! - but in the situation of Subhas - he is obviously someone not often working with storedprocedures - or he wouldn't have been asking the question.

For VB/ASP code monkeys (no slight intended), I'm just suggesting they stick to what they're good at and comfortable with...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources