I have a stored procedure named "processInvento ry" like the following.
Depending on the passed in parameters, I would like to add a WHERE
clause for "select" action. For example, if any varchar type of
parameter is passed in, the where clause would use "LIKE" operator. For
example, "Select * from Main where [s/n] like @Serial. All other types
will use "=" operator. For example, "Select * from Main where MAKE =
@Make and Type = @type".
How could this be achieved? Thanks.
CREATE PROCEDURE processInventor y
@Action varchar(7),
@ControlNumber int = null,
@AssetTag int = null,
@Serial varchar(50) = null,
@Description varchar(50) = null,
@Make int = null,
@Type int = null,
@Model int = null,
@Status int = null,
@Networked bit = null,
@LoginName varchar(50) = null,
@Shared bit = null,
@Org varchar(15) = null,
@RecordDate datetime = null,
@LastUpdate datetime = null,
@ManufactureDat e datetime = null,
@Comment ntext = null
AS
declare @processError int
set @processError = 0
if @Action = 'Select' goto selectInventory
else
If @Action = 'Update'
begin
if @ControlNumber = null return(1) --Required parameter value not
specified
else
goto updateInventory
end
else
if @Action = 'Insert'
begin
if @Serial = null return(1) --Required parameter value not
specified
else
goto InsertInventory
end
else
if @Action = 'Delete'
begin
if @ControlNumber = null return(1) --Required parameter value
not specified
else goto deleteInventory
end
selectInventory :
if @Serial <> null
begin
select * from Main where [S/N] like @Serial
if @@Error<>0
begin
set @processError = @@Error
return @processError
end
end
else
if @ControlNumber <> null
begin
select * from Main where ControlNumber = @ControlNumber
if @@Error <>0
begin
set @processError = @@Error
return @processError
end
end
else
select top 100* from Main
updateInventory :
update MAIN
set [Org Asset Tag] = @AssetTag, [S/N] = @Serial, [Description]
= @Description, Make = @Make, Type = @Type,
Model = @Model, Status = @Status, Networked = @Networked,
LoginName = @LoginName, Shared = @Shared,
Org = @Org, [Date Of Record] = @RecordDate, [Date Last
Updated] = @LastUpdate, [Manuf Date] = @ManufactureDat e,
Comments = @Comment
where ControlNumber = @ControlNumber
if @@ERROR <> 0
begin
set @processError = @@ERROR
return @processError
end
else
return(0) -- successful update
insertInventory :
insert MAIN([Org Asset Tag], [S/N], [Description], Make, Type,
Model, Status, Networked, LoginName, Shared,
Org, [Date Of Record], [Date Last Updated], [Manuf
Date],Comments)
values(@AssetTa g, @Serial, @Description, @Make, @Type, @Model,
@Status, @Networked, @LoginName, @Shared,
@Org, @RecordDate, @LastUpdate, @ManufactureDat e,
@Comment)
if @@ERROR <> 0
begin
set @processError = @@ERROR
return @processError
end
else return(0) -- successful insert
deleteInventory :
delete MAIN where ControlNumber = @ControlNumber
if @@ERROR <> 0
begin
set @processError = @@ERROR
return @processError
end
else return(0) -- successful delete
GO 4 2185
First, I would suggest that you not lump all of your actions together
in one stored procedure; you will suffer from a performance impact,
because SQL Server will be forced to recompile your procedure every
time it runs (for SELECT, UPDATE, or DELETE). This is never a good
idea.
That being said, you could set the default value of the parameter you
wish to use wildcards on as a wildcard ('%'); later, in the body of the
stored procedure, add a wildcard character to the value before you use
it in the query. A simple example is below:
CREATE PROC procTestWildcar d @Param varchar(10) = '%' AS
SET @Param = @Param + '%'
SELECT Column
FROM Table
WHERE Column Like @Param
-----
Running the following
exec procWildCardTes t
will return all of the data in your table since you've essentially run
the statement
SELECT Column
FROM Table
WHERE Column Like '%%'
The statement
exec procWildCardTes t 'S'
will return all of the data in your table that starts with the letter
'S', since the SQL statement is now interpreted as
SELECT Column
FROM Table
WHERE Column Like 'S%'
HTH,
Stu
Thanks for your suggestion. As you can see, I have more than one
parameter that might be passed into the proc. How do I dermine which
one is passed in? If I use IF..ELSE, there would be many combination
of parameters. I don't think SQL2000 allow concation of partitial
statments, so each combination need to be dealt with like
IF @Make <> NULL
SELECT COL1, COL2 FROM TABLE WHERE MAKE LIKE @Make
ELSE
IF @Make <> NULL AND @Model <> NULL
SELECT COL1, COL2 FROM TABLE WHERE MAKE LIKE @Make and MODEL LIKE
@Model
ELSE
other paramter combination
The suggestion I gave above will work for any number of paramater
combinations. Not the most effecient way, but it will work.
CREATE PROC procTestParams (@Make varchar(10) = '%', @Model varchar(10)
= '%') AS
SET @Make = @Make+'%'
SET @Model = @Model+'%'
SELECT COL1, COL2
FROM TABLE
WHERE MAKE LIKE @Make
and MODEL LIKE @Model
Another way to do this is to build your SQL string dynamically and use
sp_executeSQL
CREATE PROC procTestParams (@Make varchar(10) =NULL, @Model
varchar(10) = NULL) AS
DECLARE @SQL nvarchar(4000)
/*Return all records by default; need a basic true WHERE condition so
that you can
append AND's to it as needed*/
SET @SQL = 'SELECT COL1, COL2 FROM TABLE WHERE 1=1 '
IF @Make IS NOT NULL
SET @SQL =@SQL + ' AND Make LIKE @Make ' --make sure that you are
passing wildcards if needed
IF @Model IS NOT NULL
SET @SQL =@SQL + ' AND Model LIKE @Model '
exec sp_executeSQL @SQL, N'@Make varchar(10), @Model varchar(10)',
@Make, @Model
You'll just have to play around with it to see which is more effecient;
the first version will basically run a search against all parameters,
looking for wildcards (any data) on the columns you don't specify a
value for, whereas the second version will dynamically build a SQL
statement to be executed against only those columns you supply a
parameter for. The effeciency of either approach is going to be
affected by the number and atype of indexes on your table, and the
amount of data to be returned.
Hope that clarifies.
Stu
[posted and mailed, please reply in news]
js (an********@yah oo.com) writes: I have a stored procedure named "processInvento ry" like the following. Depending on the passed in parameters, I would like to add a WHERE clause for "select" action. For example, if any varchar type of parameter is passed in, the where clause would use "LIKE" operator. For example, "Select * from Main where [s/n] like @Serial. All other types will use "=" operator. For example, "Select * from Main where MAKE = @Make and Type = @type". How could this be achieved? Thanks.
I have a longer article on the topic on http://www.sommarskog.se/dyn-search.html.
Since you are into UPDATE, I would careful with using dynamic SQL
because of the permissions issues.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: WGW |
last post by:
Though I am a novice to MS SQL server (2000 I believe), I can do almost!
everything I need. Maybe not efficiently, but usefully. However, I have
a problem -- a complex query problem...
I can create a parameter query in a stored procedure, but how do I use
the result set of a parameter query in a select query (in the same or
another sp)? In...
|
by: Andy |
last post by:
Hi
Could someone clarify for me the method parameter passing
concept?
As I understand it, if you pass a variable without
the "ref" syntax then it gets passed as a copy.
If you pass a variable with the "ref" syntax then it gets
passed as a reference to the object and any changes to
|
by: wiredless |
last post by:
What is the advantage of passing an interface type?
according to UML (visio) when i reverse engineer existing code to a UML
diagram I get the error
"UMLE00046: sample : - An interface cannot be
used as the type of a parameter."
for this code:
|
by: John Pass |
last post by:
Hi,
In the attached example, I do understand that the references are not changed
if an array is passed by Val. What I do not understand is the result of line
99 (If one can find this by line number) which is the last line of the
following sub routine:
' procedure modifies elements of array and assigns
' new reference (note ByVal)
Sub...
|
by: diffuser78 |
last post by:
I wrote a small app in wxPython using wxGlade as designer tool. wxGlade
brings and writes a lot of code by itself and thats where my confusion
started. Its about parameter passing. Here is my little confusion.
***************CODE BEGINS************************************
class MyFrame1(wx.Frame):
def __init__(self, *args, **kwds):
........
| |
by: amazon |
last post by:
Our vender provided us a web service:
1xyztest.xsd file...
------------------------------------
postEvent PostEventRequest
-------------------------------------
authetication authentication
eventname string
source string
ID string
|
by: amazon |
last post by:
I have web service that acceping following parameters..
postev.PostEvent(authentication as ws.authentication, name as string,id as
string, exdate as date, parameter() as ws.nameparametervaluepair (I need help
in passing this))
Postev.postEvent(auth(), "Test", "1234567", tdt, parameter())
For the first parameter authentication I have:
...
|
by: TS |
last post by:
I was under the assumption that if you pass an object as a param to a method
and inside that method this object is changed, the object will stay changed
when returned from the method because the object is a reference type?
my code is not proving that. I have a web project i created from a web
service that is my object:
public class...
|
by: dave_dp |
last post by:
Hi, I have just started learning C++ language..
I've read much even tried to understand the way standard says but still
can't get the grasp of that concept.
When parameters are passed/returned by value temporaries are
created?(I'm not touching yet the cases where standard allows
optimizations from the side of implementations to avoid...
|
by: Deckarep |
last post by:
Hello fellow C# programmers,
This question is more about general practice and convention so here
goes:
I got into a discussion with a co-worker who insisted that as a general
practice all objects should be passed by reference using the ref
keyword generally speaking because as the writer of code you are
conveying your intentions that an...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |