StrataFrame Forum » StrataFrame Database Deployment Toolkit » Database Deployment Toolkit (How do I?) » Add optional parameter to a store procedure

Add optional parameter to a store procedure

Edhy Rijo — Thu, 23 Oct 2008 09:00:20 GMT

Hi,

I have the following store procedure in the DDT

[codesnippet][code]

@pEmployeePK INT,

@pServiceCallsScheduleDate DATETIME

SELECT DISTINCT dbo.ServiceCalls.*

FROM dbo.ServiceCalls

RIGHT OUTER JOIN dbo.SC_Appliances

ON (dbo.ServiceCalls.PK_ServiceCalls = dbo.SC_Appliances.FK_ServiceCalls)

WHERE (dbo.ServiceCalls.Status NOT IN (2,3))

AND (dbo.SC_Appliances.IncludeInSCRoute = 1)

AND (dbo.SC_Appliances.FK_Employees = @pEmployeePK)

AND (dbo.SC_Appliances.ServicedDate = @pServiceCallsScheduleDate);

SELECT DISTINCT dbo.SC_Appliances.*

FROM dbo.SC_Appliances

RIGHT OUTER JOIN dbo.ServiceCalls

ON (dbo.SC_Appliances.FK_ServiceCalls = dbo.ServiceCalls.PK_ServiceCalls)

WHERE (dbo.ServiceCalls.Status NOT IN (2,3))

AND (dbo.SC_Appliances.IncludeInSCRoute = 1)

AND (dbo.SC_Appliances.FK_Employees = @pEmployeePK)

AND (dbo.SC_Appliances.ServicedDate = @pServiceCallsScheduleDate);

[/code][/codesnippet]

The parameter @pEmployeePK should be optional, which mean, that I can either pass a value or pass 0, in VB code I would simple do something like this:

[codesnippet][code]

If tEmployeePK > 0 Then

cmd.Parameters.AddWithValue("@pEmployeePK", tEmployeePK).SqlDbType = SqlDbType.BigInt

End If

[/code][/codesnippet]

How would I code the same condition in T-SQL for the store procedure?

RE: Add optional parameter to a store procedure

Dustin Taylor — Thu, 23 Oct 2008 09:00:20 GMT

No sweat! Yep, it takes some time to learn the intracacies of SQL and stored procedures, but it is worth it, believe me :D

RE: Add optional parameter to a store procedure

Edhy Rijo — Wed, 22 Oct 2008 09:30:30 GMT

Hi Dustin,

Thanks a lot, I made the suggested changes and the SP is now working as expected. I am very, very new to SP and now that I have my reports queries working on the VB side, I am moving them to SP to try to speed up the reporting process.

RE: Add optional parameter to a store procedure

Dustin Taylor — Wed, 22 Oct 2008 09:06:56 GMT

You always have to supply it, but you can account for it as you have already mentioned by passing over a 0 and handling the 0 within the logic of your stored procedure.

So you're VB code would look like this (take out the IF test and pass it even if the value is 0):

[codesnippet]cmd.Parameters.AddWithValue("@pEmployeePK", tEmployeePK).SqlDbType = SqlDbType.BigInt[/codesnippet]

You can do something along the lines of the following to account for it in your SQL:

[codesnippet]@pEmployeePK INT,

@pServiceCallsScheduleDate DATETIME

BEGIN

SELECT DISTINCT

dbo.ServiceCalls.*

FROM dbo.ServiceCalls AS SC

[b][i]LEFT JOIN dbo.SC_Appliances AS SCA ON SC.PK_ServiceCalls = SCA.FK_ServiceCalls[/b][/i]

WHERE dbo.ServiceCalls.Status NOT IN (2,3) AND

dbo.SC_Appliances.IncludeInSCRoute = 1 AND

[b][i](@pEmployeePK = 0 OR dbo.SC_Appliances.FK_Employees = @pEmployeePK)[/b][/i] AND

dbo.SC_Appliances.ServicedDate = @pServiceCallsScheduleDate;

...

END[/codesnippet]

I accounted for the possibility of hte employeePK being 0 through the italicized where condition with the OR in it. A

couple of things to keep in mind:

1) I used an OR above for clarities sake. ORs tend to slow things down in SQL, though, so if this is a query that

is used a lot or that needs high performance, I would look at a different solution to handle this problem, even down to

having two seperate SQL queries on either side of an IF test.

2) I also have italicized your join and changed it to a LEFT JOIN, though that may not be what you need here. I'm not sure what exactly

your desired results are, but another thing to keep in mind is that OUTER JOINS are slow as all get out in SQL, and RIGHT JOINS aren't

typically used very much either (they are somewhat slow as well, though there are rare circumstances where they are neaded.) Depending

on what you are going for here, you can almost always get the records you need with a LEFT or an INNER join.