I have an existing query from MS Access that I want to convert it to
SQL Server Stored Proc. My problem is on how to convert the WHERE
clause.
This is the query from MS Access:
SELECT SchYrSemCourseJ oin.SchYrSemCou rseID, Students.IDNo, [LastName]
& ", " & [FirstName] & " " & [MiddleName] AS Name,
Program.Program Title, Program.Program Desc, SchYrSem.SchYr,
SchYrSem.Sem, SchYrSem.Year, SchYrSem.Sectio n AS Section1,
Major.Major, Course.CourseCo de, Course.CourseTi tle, Course.Unit,
SchYrSemCourseJ oin.Final, SchYrSem.SchYrS emID
FROM (Program INNER JOIN Students ON Program.Program ID =
Students.Progra mID) INNER JOIN ((Major INNER JOIN SchYrSem ON
Major.MajorID = SchYrSem.MajorI D) INNER JOIN (Course INNER JOIN
SchYrSemCourseJ oin ON Course.CourseID = SchYrSemCourseJ oin.CourseID)
ON SchYrSem.SchYrS emID = SchYrSemCourseJ oin.SchYrSemID) ON
Students.IDNo = SchYrSem.IDNo
WHERE ((([LastName] & ", " & [FirstName] & " " &
[MiddleName])=[Forms]![Rating Report Dialog]![SubName]) AND
((SchYrSem.Year ) Like IIf(IsNull([Enter Value]),"*",[Enter Value])));
This is a stored proc that I have currently created:
CREATE PROCEDURE dbo.Rating
@LastName nvarchar(50)
AS SELECT SchYrSemCourseJ oin.SchYrSemCou rseID, Students.IDNo,
[LastName] + ', ' + [FirstName] + ' ' + [MiddleName] AS Name,
Program.Program Title, Program.Program Desc, SchYrSem.SchYr,
SchYrSem.Sem, SchYrSem.Year, SchYrSem.Sectio n AS Section1,
Major.Major, Course.CourseCo de, Course.CourseTi tle, Course.Unit,
SchYrSemCourseJ oin.Final, SchYrSem.SchYrS emID
FROM (Program INNER JOIN Students ON Program.Program ID =
Students.Progra mID) INNER JOIN ((Major INNER JOIN SchYrSem ON
Major.MajorID = SchYrSem.MajorI D) INNER JOIN (Course INNER JOIN
SchYrSemCourseJ oin ON Course.CourseID = SchYrSemCourseJ oin.CourseID)
ON SchYrSem.SchYrS emID = SchYrSemCourseJ oin.SchYrSemID) ON
Students.IDNo = SchYrSem.IDNo
WHERE ((([LastName] + ', ' + [FirstName] + ' ' +
[MiddleName])=@LastName)) Return
GO
My problem is on how can I add the second criteria which is the Field
Year on my stored proc. The query above (MS Access) returns all the
records if the Parameter Enter Value is null.
Anyone know how to do this in stored proc? I want to create a stored
proc that will have the same results as the query above.
Thanks in advance. 2 2334
Try this:
And (SchYrSem.Year) Like IsNull(@SchYr, SchYrSem.Year) + '%'
On Thu, 20 Jan 2005 10:49:50 +0800, jaYPee wrote:
(snip) AND ((SchYrSem.Yea r) Like IIf(IsNull([Enter Value]),"*",[Enter Value])));
(snip)Anyone know how to do this in stored proc? I want to create a stored proc that will have the same results as the query above.
Hi jaYPee,
if year is a character column, you could do a fairly straight translation:
WHERE SchYrSem.Year LIKE COALESCE(@SchYr , '%')
This is not a good solution if Year is a numeric datatype (which it should
be). The following will work for all datatypes:
WHERE SchYrSem.Year = COALESCE (@SchYr, SchYrSem.Year)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Thomasb |
last post by:
With a background in MS SQL Server programming I'm used to temporary
tables. Have just started to work with DB2 ver 7 on z/OS and stumbled
into the concept of GLOBAL TEMPORARY TABLE.
I have created a temporary database with a tables space. Verified that
DECLARE GLOBAL TEMPORARY TABLE TEMP (A INTEGER);
INSERT INTO SESSION.TEMP VALUES(10);
SELECT A FROM SESSION.TEMP;
works from a query tool.
|
by: Timppa |
last post by:
Hi,
Could anyone help me with my problem ?
Environment: Access 2000 and Sql Server 2000.
I have a stored procedure as follows:
DROP table1
SELECT alias1.field1,alias2.field2,table2.field6
INTO table1
|
by: Wojciech Wendrychowicz |
last post by:
Hello to All,
I'm trying to retrieve records from AS/400 in an VBA application.
So, I've made an RPG program, then a stored procedure wchich calls that RPG
program, and finally some VBA code to call the stored procedure and retrieve
data from AS/400.
The problem is, that when I finally run my VB code, it just hangs.
But when I call the same stored procedure from "pure" SQL - it works
perfect. (I evaluate Aqua Data Studio 3.7)
What I...
|
by: Amber |
last post by:
Stored procedures are faster and more efficient than in-line SQL
statements. In this article we will look at two SQL Server stored
procedures; one using an input parameter and one not, and see how to
call them from an ASP.Net page
Every modern database system has a stored procedure language. SQL
Server is no different and has a relatively sophisticated and easy to
use system. This article will not attempt to go into depth in
explaining...
|
by: deepdata |
last post by:
Hi,
I am trying to fetch data from db2 (express version) database by
calling stored procedure. I have tried to use both cursor and for loop
but still i am getting error.
--======Start procedure=============
Create PROCEDURE get_timedout_scripts
(
| |
by: peaceburn |
last post by:
Hi,
I'm gonna pull my hair in the coming days with these DB2 stored
procedures.
So the issue, let's assume a simple stored procedure like this :
CREATE PROCEDURE MYSCHEMA.PROCEDURE1 ( )
DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
|
by: brianlanning |
last post by:
We have a stored procedure that we've tried with two slightly
different designs. It needs to take a 30 day date range and return a
result set.
Design 1 takes one date as a parameter. The other date is calculated
in a local variable to be 30 days before the one that was passed in.
Both data types are datetime and are in the where clause.
Design 2 takes two dates as parameters with the 30 days being
calculated outside the stored...
|
by: william.david.anderson |
last post by:
Hi there,
I have a newbie question regarding stored procedures and locking.
I'm trying to use a stored
procedure to perform a 'select for update' and return a cursor.
Below is a stripped down version
of the procedure:
CREATE PROCEDURE SELBTFLFORUPDATE()
LANGUAGE SQL
|
by: billmaclean1 |
last post by:
I need to write a stored procedure that selects from a table and
returns the result set. I don't always know the TableSchema that I
need to use when qualifying the table at run-time
Example: The correct table could either be dbo.MyTable or
zzz.MyTable.
I want the user to enter the name of the schema as a parameter of the
procedure at run-time.
|
by: priyamtheone |
last post by:
I'm trying to create a stored procedure in MSSQL Server 2005 that'll perform the following jobs:
1) Create a login.
2) Create an user in TestDB database for the login created in step 1.
3) Assign the role 'db_generaluser' to the user created in step 2.
The login name and password for the login to be created will be supplied from externally through input parameters. If this procedure executes successfully it returns 0 else 1 to the caller...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
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.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
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 most users, this new feature is actually very convenient. If you want to control the update process,...
|
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |