I have a stored procedure named "processInventory" 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 processInventory
@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,
@ManufactureDate 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] = @ManufactureDate,
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(@AssetTag, @Serial, @Description, @Make, @Type, @Model,
@Status, @Networked, @LoginName, @Shared,
@Org, @RecordDate, @LastUpdate, @ManufactureDate,
@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 2170
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 procTestWildcard @Param varchar(10) = '%' AS
SET @Param = @Param + '%'
SELECT Column
FROM Table
WHERE Column Like @Param
-----
Running the following
exec procWildCardTest
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 procWildCardTest '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********@yahoo.com) writes: I have a stored procedure named "processInventory" 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****@sommarskog.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...
|
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...
|
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...
|
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...
|
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...
|
by: amazon |
last post by:
Our vender provided us a web service:
1xyztest.xsd file...
------------------------------------
postEvent PostEventRequest
-------------------------------------
authetication authentication...
|
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...
|
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...
|
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: 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...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
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...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
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...
| |