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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-01-06 : 08:31:44
|
| Johann writes "Dear allI have an images table whereby the user can insert images in it through a console. The fields for the table are:-Im_id, im_name, im_thumb, im_filesize, im_dateuploaded, im_desc, im_height, im-Width, im_thumb_Height, im_thumb_width.Now I have a search/sort toolbar whereby the user can sort these images accordingly. In it I have 8 button:-Date Asc, Date Desc, Filesize Asc, Filesize Desc, Alphabet Asc, Alphabet Desc, InUse, Not In Use.I also have a search textbox (txtSearch.text) and also 4 other buttons to display the images either 15 per page, 30pp, 45pp, 60pp.With normal SQL what I used to do was build the sql string and commit it at the end. For example, if the user entered text in the txtSearch.text, I would concatenate it like this:-strSql = strSql & "WHERE im_name Like " & "'" & "%" & searchText & "%" & "'" & _ " OR im_desc like " & "'" & "%" & searchText & "%" & "'"or else if the user clicked on the Date Asc button, then I would add the following:-strSql = strSql & " ORDER BY im_dateUpl ASC"However I am trying to re-do it in Stored Procedures and I am quite lost at the moment. What I am doing at the moment is for example if the user clicks on the Date Asc button, I am creating a Stored Procedure for that purpose and bind the images datagrid. So basically at the moment I have a lot of Stored Procedures and I am sure that there is a better way to go round this problem.Can anyone help me?Thanks for your help and time." |
|
|
AndyB13
Aged Yak Warrior
583 Posts |
Posted - 2005-01-06 : 08:34:14
|
| Do you want to have the data sorted on the 1st request or getting the data and then giving them the option to re-order once in the grid.If its the latter i would do this in the front end and not do another round trip to the server |
 |
|
|
SqlStar
Posting Yak Master
121 Posts |
Posted - 2005-01-06 : 08:37:54
|
| Hi,Dont create stored procedures, when click "Date Asc" button. Just pass the required values (which has to be concatenated) to an stored procedure. The stored procedure will execute that dynamic SQL statement using "Exec (SQL Statement)" syntax. If you want more idea about this, see SQL BOL.:) While we stop to think, we often miss our opportunity :) |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
|
|
|
|
|
|
|
Complex Stored Procedure 