Complex Stored Procedure
-
Dear all
I 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.
-
Take a look at this article, http://www.sqlservercentral.com/columnists/rmarda/dynamicvsstatic2.asp
It describes how to use "Case" in the order by clause.
Kathi
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
ok thanks i will take a look at this article
-
Hello
I came up with this stored proc :-
----------------------------------------------------------------------------------------
CREATE PROC GetImageByCriteria
(@inputDate char(1),
@inputFilesize char(1),
@inputAlpha char(1),
@inputUsed char(10),
@inputSearchTextvarchar(200))
AS
DECLARE @strSQL VARCHAR(8000), @flag bit
SET @flag = 0
IF @inputUsed " "
IF @inputUsed = "InUse"
BEGIN
SET @strSQL = 'SELECT DISTINCT updImages.fk_im_id
FROM images
INNER JOIN updImages ON images.im_id = updImages.fk_im_id'
SET @flag = 1
END
ELSE
BEGIN
SET @strSQL = 'SELECT DISTINCT images.im_id
FROM images
WHERE images.im_id NOT IN (SELECT updImages.fk_im_id FROM updImages)'
SET @flag = 1
END
IF @inputSearchText " "
BEGIN
IF @flag = 1
SET @strSQL = @strSQL + 'WHERE im_id LIKE %'+@inputSearchText+'%
im_name LIKE %'+@inputSearchText+'%
OR im_desc LIKE %'+@inputSearchText+'%'
ELSE
SET @strSQL = 'SELECT Im_id, im_name, im_thumb, im_filesize,
im_dateuploaded, im_desc, im_height, im_Width,
im_thumb_Height, im_thumb_width
FROM Images WHERE im_name LIKE %'+@inputSearchText+'%
OR im_desc LIKE %'+@inputSearchText+'%'
END
IF @flag 1
SET @strSQL = 'SELECT Im_id, im_name, im_thumb, im_filesize,
im_dateuploaded, im_desc, im_height, im_Width,
im_thumb_Height, im_thumb_width
FROM Images'
IF @inputDate " "
IF @inputDate = 'A'
BEGIN
SET @strSQL = @strSQL + 'ORDER BY im_dateuploaded ASC'
SET @flag = 1
END
ELSE
BEGIN
SET @strSQL = @strSQL + 'ORDER BY im_dateuploaded DESC'
SET @flag = 1
END
IF @inputFileSize " "
IF @inputFileSize = 'A'
BEGIN
IF @flag = 1
SET @strSQL = @strSQL + 'im_filesize ASC'
ELSE
SET @strSQL = @strSQL + 'ORDER BY im_filesize ASC'
SET @flag = 1
END
ELSE
IF @flag = 1
SET @strSQL = @strSQL + 'im_filesize DESC'
ELSE
SET @strSQL = @strSQL + 'ORDER BY im_filesize DESC'
SET @flag = 1
IF @inputAlpha " "
IF @inputAlpha = 'A'
BEGIN
IF @flag = 1
SET @strSQL = @strSQL + 'im_name ASC'
ELSE
SET @strSQL = @strSQL + 'ORDER BY im_name ASC'
SET @flag = 1
END
ELSE
IF @flag = 1
SET @strSQL = @strSQL + 'im_name DESC'
ELSE
SET @strSQL = @strSQL + 'ORDER BY im_name DESC'
SET @flag = 1
EXEC (@strSQL)
RETURN
GO
----------------------------------------------------------------------------------------
Let me tell you what I am trying to do here :-
1)First I am checking if the user clicked on the inputUsed button. If he did then I am determining whether he clicked on the “InUse” button or “NotInUse” button and I am doing the appropriate SQL string.
2)Then I am checking if the user entered any text in the txtSearch.Text field. If he did, I am doing the appropriate SQL string.
3)Then I am checking for the flag. If it is set to 1, then it means that the user already pressed one of the above buttons, and so there is no need to create the SQL string. However, if he did not press any of the above buttons, then I need to create the SQL String.
4)Finally, I am checking which of the 3 sort buttons (@inputDate, @inputFilesize, and @inputAlpha) and doing the appropriate ORDER BY sorting.
Can you please tell me if I am correct?
Thanks for your help and time.
Johann
-
I'm not going to review your code for correctness, but you missed the point of the article. The article is about avoiding dynamic SQL by using Case. I'm not saying you can't solve the problem this way, but it is better to avoid dynamic SQL when you can.
Also, if this proc will be called many, many times by your application, it will probably have to recompile each time it is run, thus the performance will be poor. Take a look at the article again and review the information about using CASE more closely.
Kathi
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
Hi Kathi
I tried coming up with something like the article you suggested, however, for my present knowledge, I found it quite hard to follow. So i decided to go this way.
Maybe when I get some more knowledge on SQL Server and Stored Procs (going to do the MCP), I will be able to start making more performance related stored procedures.
However thanks for your article, i will need it later.
-
OK, you need to test your proc and this is how I would do it. Comment out your execute line and add a print line:
- --EXEC (@strSQL)
- Print @strSQL
- Then run your proc in Query Analyzer several times, each time altering the parameters. The results can then be pasted and run in a separate QA window to see if they work.
- I know that your proc has problems with quote marks and spaces. And, maybe the logic is not exactly right. You'll be able to "debug" your proc with this technique.
Hope this helps,
Kathi
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
oh thanks very much Kathi. I appreciate your help a lot! Learned something new
-
Cool! Be sure to continue to post your questions, this is a great site with lots of helpful members.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
Yeah sure!
This forum seems to be really cool, and I will have more questions I can assure you
Thanks again Kathi
-
I have modified my stored proc like this to cater for nulls :-
CREATE PROC GetImageByCriteria
(@inputDate char(1),
@inputFilesize char(1),
@inputAlpha char(1),
@inputUsed char(10),
@inputSearchTextvarchar(200))
AS
DECLARE @strSQL VARCHAR(8000), @flag bit
SET @flag = 0
IF @inputUsed " " or @inputUsed null
IF @inputUsed = "InUse"
BEGIN
SET @strSQL = 'SELECT DISTINCT updImages.fk_im_id
FROM images
INNER JOIN updImages ON images.im_id = updImages.fk_im_id'
SET @flag = 1
END
ELSE
BEGIN
SET @strSQL = 'SELECT DISTINCT images.im_id
FROM images
WHERE images.im_id NOT IN (SELECT updImages.fk_im_id FROM updImages)'
SET @flag = 1
END
IF @inputSearchText " "
BEGIN
IF @flag = 1
SET @strSQL = @strSQL + 'WHERE im_id LIKE %'+@inputSearchText+'%
im_name LIKE %'+@inputSearchText+'%
OR im_desc LIKE %'+@inputSearchText+'%'
ELSE
SET @strSQL = 'SELECT Im_id, im_name, im_thumb, im_filesize,
im_dateuploaded, im_desc, im_height, im_Width,
im_thumb_Height, im_thumb_width
FROM Images WHERE im_name LIKE %'+@inputSearchText+'%
OR im_desc LIKE %'+@inputSearchText+'%'
END
IF @flag 1
SET @strSQL = 'SELECT Im_id, im_name, im_thumb, im_filesize,
im_dateuploaded, im_desc, im_height, im_Width,
im_thumb_Height, im_thumb_width
FROM Images'
IF @inputDate " " OR @inputDate null
IF @inputDate = 'A'
BEGIN
SET @strSQL = @strSQL + 'ORDER BY im_dateuploaded ASC'
SET @flag = 1
END
ELSE
BEGIN
SET @strSQL = @strSQL + 'ORDER BY im_dateuploaded DESC'
SET @flag = 1
END
IF @inputFileSize " " OR @inputFileSize null
IF @inputFileSize = 'A'
BEGIN
IF @flag = 1
SET @strSQL = @strSQL + 'im_filesize ASC'
ELSE
SET @strSQL = @strSQL + 'ORDER BY im_filesize ASC'
SET @flag = 1
END
ELSE
IF @flag = 1
SET @strSQL = @strSQL + 'im_filesize DESC'
ELSE
SET @strSQL = @strSQL + 'ORDER BY im_filesize DESC'
SET @flag = 1
IF @inputAlpha " " OR @inputAlpha null
IF @inputAlpha = 'A'
BEGIN
IF @flag = 1
SET @strSQL = @strSQL + 'im_name ASC'
ELSE
SET @strSQL = @strSQL + 'ORDER BY im_name ASC'
SET @flag = 1
END
ELSE
IF @flag = 1
SET @strSQL = @strSQL + 'im_name DESC'
ELSE
SET @strSQL = @strSQL + 'ORDER BY im_name DESC'
SET @flag = 1
EXEC (@strSQL)
PRINT (@strSQL)
RETURN
GO
However I am getting an error in it which I cannot track. How can I debug a stored proc?
Thanks for all your help
-
You are using " when you should be using ' .
IF @inputUsed <> ' ' or @inputUsed <> null
IF @inputUsed = 'InUse'
BEGIN
I have changed all of the " to ', and ran the proc with this result:
SELECT DISTINCT updImages.fk_im_id
FROM images
INNER JOIN updImages ON images.im_id = updImages.fk_im_idWHERE im_id LIKE %abc%
im_name LIKE %abc%
OR im_desc LIKE %abc%ORDER BY im_dateuploaded ASC
There are still problems with missing ticks (') missing spaces. After you get the proc to compile, you can use the debugger in query analyzer to step through the code. In the object browser, right click on the stored procedure name and select "Debug"
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
Hello Kathi again,
I changed all instances of "" to '' and when i ran the stored proc, all i got was
Stored Procedure: johanndb.dbo.GetImageByCriteria
Return Code = 0
How come I am not getting any sql statement so that I can see what is happening?
I set all the values to null since in the beginning, that is how they will be.
Am I doing something wrong?
Thanks for your help
-
No that you have your stored proc compiled, you can use the Debugger in Query Analizer to step through your code and follow the logic.
1. Open the Object Browser. Tools -> Object Browser -> Show/Hide
2. Find you proc by expanding the database, then expand stored procedures.
3. Right Click on the proc name and select "Debug"
4. Fill in the parameters and Execute.
You can step through the code and see where the logic problems are. I would also comment out the exec statement while you are working on this so you see the final SQL statement, but it doesn't run yet.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
Thanks Kathi for your reply
I will try to get accustomed with the Debugger then and try to figure out what the problem is
Thanks again
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply