passing 2 parameters in a stored procedure and using them one at a time

Question

I have a stored procedure which gives results what I need.

Create Procedure [dbo].[usp_MTACardStatus]
(
@UID Varchar(50)
)
As
SELECT     mta.OrderNbr,mta.SerialNbr,mta.BatchNbr,sch.schoolnumber as  SchNbr,sch.schoolname as SchName,mti.shipmentdate as ShipmentDt,mti.ReceivedDate as ReceivedDt,s.studentid as StuID, i.IssueDT,mtst.MTACardStatusDesc as CardStatus,mtt.MTACardTypeDesc as CardType,p.firstname as FName,p.lastname as LName
FROM       MTACardInventory mta
      LEFT JOIN MTACardShipmentBlock mts on mts.BatchNbr = mta.BatchNbr and mts.OrderNbr = mta.OrderNbr
      LEFT JOIN MTACardShipment mti on mti.MTACardShipmentID = mts.MTACardShipmentID
      LEFT JOIN MTACardStatus mtst on mtst.MTACardStatusID = mta.MTACardStatusID
      LEFT JOIN MTACardType mtt on mtt.MTACardTypeID = mta.MTACardTypeID
      LEFT JOIN IDCard i on i.IDCardID = mta.IssuedTo_IDCardID
      LEFT JOIN Person p on p.PersonID = i.PersonID
      LEFT JOIN student s on s.PersonID = p.PersonID
      LEFT JOIN School sch on sch.schoolid = p.SchoolID
WHERE UID=@UID

Can I declare another parameter 'serialno' to the same stored procedure which can allow users to pass either uid or serial number and get the same result.

Is there a way to do that?

The [IF ... ELSE](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/if-else-transact-sql) statement is available — Steve, Jun 16 '17 at 12:25
Why are you expecting same result with two different parameters ? — Pரதீப், Jun 16 '17 at 12:27
@steve:Not sure how to use if..else for my SP.Any example might help..Thanks.. — Pal Makk, Jun 16 '17 at 12:27
@Prdp:In the front end if the user enters either UID or Serial,user should see the same result — Pal Makk, Jun 16 '17 at 12:29
@PalMakk - So both should be mapped to same column (ie) `UID` — Pரதீப், Jun 16 '17 at 12:29
The link posted contains examples for IF ... ELSE. However you need have some guard values for the two parameter and decide which one to use. For example, if the parameter `@SerialNO` is -1 then you execute the current block of code with `@UID` otherwise a similar block of code but with `@SerialNO` in the where clause — Steve, Jun 16 '17 at 12:30
Use a Declare statement at beginning of script. Then test if serialno is empty. If not empty then @UID=@SerialNo. — jdweng, Jun 16 '17 at 12:35

score 2 · Accepted Answer · answered Jun 16 '17 at 14:02

2

As Lews indicated, you need to pass in another optional parameter for the Serial Number. Then in your WHERE clause you need to reference the additional Serial Number parameter. Your WHERE clause must check whether either parameter is null in order to return ANY Serial number when a UID is supplied, and return ANY UID when a Serial Number is supplied.

Create Procedure [dbo].[usp_MTACardStatus] ( @UID Varchar(50) = NULL, @SerialNbr varchar(50) = NULL ) As SELECT mta.OrderNbr,mta.SerialNbr,mta.BatchNbr,sch.schoolnumber as SchNbr,sch.schoolname as SchName,mti.shipmentdate as ShipmentDt,mti.ReceivedDate as ReceivedDt,s.studentid as StuID, i.IssueDT,mtst.MTACardStatusDesc as CardStatus,mtt.MTACardTypeDesc as CardType,p.firstname as FName,p.lastname as LName FROM MTACardInventory mta LEFT JOIN MTACardShipmentBlock mts on mts.BatchNbr = mta.BatchNbr and mts.OrderNbr = mta.OrderNbr LEFT JOIN MTACardShipment mti on mti.MTACardShipmentID = mts.MTACardShipmentID LEFT JOIN MTACardStatus mtst on mtst.MTACardStatusID = mta.MTACardStatusID LEFT JOIN MTACardType mtt on mtt.MTACardTypeID = mta.MTACardTypeID LEFT JOIN IDCard i on i.IDCardID = mta.IssuedTo_IDCardID LEFT JOIN Person p on p.PersonID = i.PersonID LEFT JOIN student s on s.PersonID = p.PersonID LEFT JOIN School sch on sch.schoolid = p.SchoolID WHERE (UID=@UID OR @UID IS NULL) AND (mta.SerialNbr=@SerialNbr OR @SerialNbr IS NULL)

answered Jun 16 '17 at 14:02

Mr Slim

1,458
3
17
28

Ahh yes, your WHERE clause is much more elegant, I'll update my answer. +1 – Lews Therin Jun 16 '17 at 14:14
even then i donot get result for serialNbr – Pal Makk Jun 16 '17 at 14:20
Then the problem is with your serial number Pal. Perform a test with your query with a single where clause parameter WHERE mta.SerialNbr='enter A Valid Serial Number here' – Mr Slim Jun 16 '17 at 14:26
1

When using the above code containing 2 x parameters and searching for a serial number, are you passing the @UID parameter in and if so is it null? exec Procedure [dbo].[usp_MTACardStatus](null, 'enter some valid serial number here') – Mr Slim Jun 16 '17 at 14:47
It worked..When i entered null, ' serial number'...Does this work on my webpage having only one text box for entering uid or serialnbr? – Pal Makk Jun 16 '17 at 14:57
So what you are actually wanting is for a user to enter either serial number of a UID into a text box and passing that 1 parameter to your stored procedure, which will filter by UID OR Serial Number. In your stored procedure use 1 parameter not 2 @Criteria Varchar(50); In your WHERE clause, WHERE (UID=@Criteria OR mta.SerialNbr=@Criteria) – Mr Slim Jun 16 '17 at 15:05
Thank you so much..@Criteria worked like a charm..My vote doesnt show to public since i am new to this blog and hass less than 15 reputations..I would like to give you more than 1 vote..Thank you so much once again.. – Pal Makk Jun 16 '17 at 16:59
I know it's been awhile on this but the answer from Mr Slim assumes that there is no overlap between UID and Serial number. – billpennock Nov 16 '18 at 18:13
@billpennock, thanks for your comment. Are you able to elaborate on the overlap you mentioned with an example please. – Mr Slim Nov 21 '18 at 22:13
1

I was commenting on the answer in the comments about using @criteria as the single entry. I was also making, what might be an erroneous assumption, that SerialNbr at least was a unique number and, from the name, that UID was as well. This would make the expected result a single row. But if any SerialNBR field had the same value as any UID field you'd get both rows which could cause errors. Let me know if that explains my thought well enough without code. – billpennock Dec 06 '18 at 16:46
@billpennock thanks for expanding on your comment. I agree that if the same value is in UID and SerialNBR then 2 x records would be returned (assuming those values are not on the same record). – Mr Slim Dec 06 '18 at 23:54

Lews Therin · Answer 2 · 2017-06-16T14:15:47.607

1

Yes you can. You can make both @UID and @serialno optional parameters.

Something like:

Create Procedure [dbo].[usp_MTACardStatus]
(
    @UID Varchar(50) = NULL,
    @serialno Varchar(50) = NULL
)
As
SELECT     mta.OrderNbr,mta.SerialNbr,mta.BatchNbr,sch.schoolnumber as  SchNbr,sch.schoolname as SchName,mti.shipmentdate as ShipmentDt,mti.ReceivedDate as ReceivedDt,s.studentid as StuID, i.IssueDT,mtst.MTACardStatusDesc as CardStatus,mtt.MTACardTypeDesc as CardType,p.firstname as FName,p.lastname as LName
FROM       MTACardInventory mta
      LEFT JOIN MTACardShipmentBlock mts on mts.BatchNbr = mta.BatchNbr and mts.OrderNbr = mta.OrderNbr
      LEFT JOIN MTACardShipment mti on mti.MTACardShipmentID = mts.MTACardShipmentID
      LEFT JOIN MTACardStatus mtst on mtst.MTACardStatusID = mta.MTACardStatusID
      LEFT JOIN MTACardType mtt on mtt.MTACardTypeID = mta.MTACardTypeID
      LEFT JOIN IDCard i on i.IDCardID = mta.IssuedTo_IDCardID
      LEFT JOIN Person p on p.PersonID = i.PersonID
      LEFT JOIN student s on s.PersonID = p.PersonID
      LEFT JOIN School sch on sch.schoolid = p.SchoolID
WHERE (UID = @UID OR @UID IS NULL) AND (mta.SerialNbr = @SerialNbr OR @SerialNbr IS NULL)

edited Jun 16 '17 at 14:15

answered Jun 16 '17 at 13:47

Lews Therin

3,707
2
27
53

This solution looks great..However when i pass the serialNbr i dont get the result..Yes..there is a serial number coloumn in my table – Pal Makk Jun 16 '17 at 13:59
What table is the `serialno` column in? Is it named `serialno` or `serialNbr`? Is the column in the table an `INT` or a `VARCHAR`? My answer assumed it was a `VACHAR` like the `@UID` parameter was in your question. – Lews Therin Jun 16 '17 at 14:07
I re-read your original query and saw the `mta` table had a `SerialNbr` column so I edited my answer. It still may not work though depending on what datatype `SerialNbr` is. – Lews Therin Jun 16 '17 at 14:09
serialNbr is in same table mta, and ur right its varchar..I took it correct as SerialNbr only..but did not work – Pal Makk Jun 16 '17 at 14:11
I updated my answer once again to incorporate @MrSlim's better WHERE clause (which eliminates a lot of un-needed code in my answer). – Lews Therin Jun 16 '17 at 14:16
yes looks good but no luck..It doesnt return anything for serialnbr – Pal Makk Jun 16 '17 at 14:24

score -1 · Answer 3 · answered Jun 16 '17 at 13:34

No way. The number of values you are passing to the stored procedure must match the number of parameters declared in stored procedure. You cannot pass single value for a stored procedure receiving two different parameters.

If you want to achieve your requirement, the only way is to use two different stored procedures.one with accepting 1st parameter and other accepting 2nd parameter in your case. And to execute this, you can enclose within a try-catch block.i.e., If the values you're passing matches with the parameter in the try block stored procedure then it will be executed.If it doesn't match, exception is thrown then catch block stored procedure gets executed.

create procedure [dbo].[sp1]
    @UID varchar(50)
as
begin
    'your code'
end

create procedure [dbo].[sp2] 
    @SNo int
as
begin
    'your code'
end

begin try
    exec sp1 10
end try
begin catch
    exec sp2 10
end catch

Sorry but this is just WRONG. – billpennock Nov 16 '18 at 18:15 — billpennock, Nov 16 '18 at 18:15

passing 2 parameters in a stored procedure and using them one at a time

3 Answers3