Stored Procedure and parameters

Question

Stored Procedure and parameters

new2sql1

SSC Rookie

Points: 43
More actions
July 19, 2003 at 12:57 pm

#161836

I am new to parameterized Stored Procedures and am having trouble. I am using VB code on the client side to provide the parameters and passing them to the SP using ADO.
What is the best way to exclude the variables that do not have data in them.
Here is a sample of the SP I am working with:
CREATE PROCEDURE sp_TEST
(
@param1 int,
@param2 int = '%',
@param3 datetime
)
AS
SELECT *
FROM TRANS
WHERE Transid = @param1 and
Custid = @param2 and
Trandate Between @param3 and '7/1/2003'
Would an IF statement be a viable option?
Thanks in advance for any input(

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

NPeeters SSChampion Points: 12535 More actions · Answer 1

You have two possibilities. The first one is using IF statements, each with their own query. This is OK if you only have a few parameters, but the number of combinations grows very quickly.

Another possibility is to change the query to take left out parameters into account.

In your example, the query would look like :



SELECT *

FROM TRANS

WHERE Transid = IsNull(@param1, Transid)

  AND Custid = IsNull(@param2, Custid)

  AND Trandate Between IsNull(@param3, Trandate) AND '7/1/2003'

new2sql1 SSC Rookie Points: 43 More actions · Answer 2

Npeeters,

Thanks for your response. I will try this and let you know if that works for me.

Thanks again.

new2sql1 SSC Rookie Points: 43 More actions · Answer 3

The isnull worked well for the int datatype, now what happens if I have a parameter that is nvarchar?

ISnull doesn't return anything. It says 0 Rows affected.

Thanks in Advance for your help.

NPeeters SSChampion Points: 12535 More actions · Answer 4

ISNULL works exactly the same for a nvarchar data type. Maybe you can post some more info to help us troubleshoot your problem.

Post the query you use and some sample data.

new2sql1 SSC Rookie Points: 43 More actions · Answer 5

I really do appreciate your help. I have included the sample data and SP.

Sample Data Table

ID - Int
PDT, Proj, WrkProd - nvarchar

ID PDT Proj WrkProd

1 ABCD BL III 0123V1

2 ABCD BL III 0123V2

3 ABCD BL VII 0851AA

4 WXYZ AP AP1234

Stored Procedure:

CREATE PROCEDURE sp_proc_test

@PDT char

@PROJ char,

@WRKPROD char

AS

SELECT *

FROM BLAH

WHERE PDT = @PDT AND

PROJ = isnull(@PROJ, PROJ) and

WRKPROD = isnull(@WRKPROD, WRKPROD)

GO

I am executing this in the QA to test if the SP functions.

Exec sp_Proc_Test 'ABCD', ' ', ' '

I have also tried

Exec sp_Proc_Test 'ABCD', NULL, NULL

The intent of this stored procedure is to have the PDT selected and all values that correspond to that PDT. If the Proj is selected, the return data is narrowed. Finally if WrkProd is selected, the return data is narrowed even more.

Thanks again

Edited by - new2sql1 on 07/19/2003 3:08:10 PM

Kiimie Kim Grasshopper Points: 21 More actions · Answer 6

Hi,

I believe you can have optional parameters in SP. Please try:

CREATE PROCEDURE sp_proc_test

@PDT char(10) = NULL

AS

.

Goce Smilevski SSCommitted Points: 1704 More actions · Answer 7

quote:
Stored Procedure:
CREATE PROCEDURE sp_proc_test
@PDT char
@PROJ char,
@WRKPROD char

If parameter types are exactly specified (char with no length, which means char(1)), I think that is the problem. Try with nvarchar parameters, each having the same length as its corresponding column.

new2sql1 SSC Rookie Points: 43 More actions · Answer 8

Thanks for the input from Everyone. I fixed my problem by using this script.

http://www.sqlservercentral.com/scripts/contributions/624.asp

Again thanks to everyone.

padmakumar_pga Right there with Babe Points: 766 More actions · Answer 9

if u have too many params and u want to bulid a sql statment according to the data in it u can try sp_sqlexcute or exec()

by using this u can exec string and get output .

elenakhmanson Valued Member Points: 60 More actions · Answer 10

Much simpler solution to what you are trying to do would be something like that:

SELECT *

FROM BLAH

WHERE (@PDT IS NULL OR PDT = @PDT) AND

(@PROJ IS NULL OR PROJ = @PROJ) AND

(@WRKPROD IS NULL OR @WRKPROD = WRKPROD)

You will get all records back if none of the parameters have values assigned.