Need help with Stored Procedure syntax
-
Hello, (my first post here)...
I have created plenty of stored procedures, including dynamic stored procedures, but I can't figure out what I'm doing wrong here. Any help will be greatly appreciated! Basically, what follows is a much simplified version of a stored procedure, but it will get the point across. I'm attempting to use an IF statement to examine the input variable (@ItemType), and then create a string (varchar) variable to complete the SQL. If the input variable is 0 (zero), I want to change the where clause to:
===================================
WHERE (fk_intInvItemTypeID > 0)
===================================
If the input variable is greater than O (zero), I want to use that input variable in the where clause, like this:
===================================
WHERE (fk_intInvItemTypeID = @ItemType )
===================================
I have tried several ways:
Attempt #1:
========================================
Create Procedure "InventoryByType"
@ItemType tinyint
As
DECLARE @strTypeScopeX varchar(15)
SET @strTypeScopeX = ''
IF (@ItemType = 0)
@strTypeScopeX = @strTypeScopeX + ' > 0 '
ELSE
@strTypeScopeX = @strTypeScopeX + ' = @ItemType'
SELECT * from dbo.tblInventory
WHERE (fk_intInvItemTypeID @strTypeScopeX)
RETURN
=========================================
Attempt #2:
========================================
Create Procedure "InventoryByType"
@ItemType tinyint
As
DECLARE @strTypeScopeX varchar(80)
SET @strTypeScopeX = ''
IF (@ItemType = 0)
@strTypeScopeX = @strTypeScopeX + 'WHERE (fk_intInvItemTypeID > 0 )'
ELSE
@strTypeScopeX = @strTypeScopeX + 'WHERE (fk_intInvItemTypeID = @ItemType )'
SELECT * FROM dbo.tblInventory @strTypeScopeX
RETURN
===============================================
Thanks in advance for any help!
-Dennis
-
SELECT * from dbo.tblInventory
WHERE intInvItemTypeID = ISNULL(NULLIF( @strTypeScopeX,0),fk_intInvItemTypeID )
IF @strTypeScopeX is zero the the NULLIF function returns NULL
The ISNULL statement therefore returns the value of fk_intInvItemTypeID
In effect you have WHERE fk_intInvItemTypeID = fk_intInvItemTypeID
IF @strTypeScopeX is other than zero the the NULLIF function returns @strTypeScopeX
The ISNULL statement therefore returns the value of @strTypeScopeX
In effect you have WHERE fk_intInvItemTypeID = @strTypeScopeX
-
Hi David,
Thanks for the reply.
Well, you took me to school on that one! There is plenty to study there, with possible future uses.
However, my actual SQL in my stored procedure is much larger, there are 4 parameters passed in, and I will need to declare at least 3 more parameters within the SP. Of those input parameters, 2 are used in a BETWEEN operation, and the other 2 are used similarly to my original description. In other words, by the time I used your ideas for this stored procedure, I suspect it would be very difficult to read and tough for anyone else to ever maintain or modify.
Worse, after I get that part working satisfactorily, I will modify the SP again to include the capability of taking a full WHERE clause as a parameter, so I can fire the SP as a dynamic SP when needed. Why am I doing it this way? This is for a moderately complicated search results page on an ASP-based website, and I would prefer that the ASP page only has a single recordset to deal with (regardless of what I had to do to the SP to get that recordset.)
I could really use a hand at figuring out what is wrong with the SP the way I wrote it. I suspect I'm missing something minor, with string manipulation within a stored procedure.
Thanks again for your input. Again, thought I don't think it is the solution I need for this particular SP, it will open other doors for me!
Dennis
-
If you are going to do searches within an ASP page then my approach would be to :
- create a VIEW for the SELECT and FROM part of your query
- Build up your query on the view with your WHERE as a string
- Use sp_executesql to run your query
So if you have reams of SELECT fields and loads of tables and joins then in effect the view lets you build a string with an initial value of "SELECT * FROM myview"
sp_executesql is good at caching your query so if the full signature of your query is similar to one in the cache it will run much faster. Behind the scenes .NET runs dynamic SQL through sp_executessql which is why it is so much faster.
Of course depending on what you are trying to do you could try Thunderstone's Webinator as an internet search engine. It is free up to 10,000 pages and not expensive once you go past that.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply