i need help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

i need help

Author

Topic

zakeer
Starting Member

20 Posts

Posted - 2008-03-28 : 07:09:02

I HAVE A SP. WHERE SP DOESN'T KNOW WHICH PARAMETER IT IS GOING TO FACE.

CREATE PROC PRCCUSTOMER
(
@FIRSTNAME VARCHAR(256)=NULL,
@LASTNAME VARCHAR(256)=NULL,
@CITY VARCHAR(256)=NULL,
@ID
@ADDRESS1
@ADDRESS2
@PINCODE
@MOBILE
____
_____
__
)

THIS IS THE SP. THE USER WILL PASS ANY ONE OF THE PARAMETER .

HOW CAN I GET THE RESULT BASED ON THE PARAMETER.

Thanks & Regards
Zakeer

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-28 : 07:12:40

[code]Where (FIRSTNAME = @FIRSTNAME or @FIRSTNAME Is Null)
and (LASTNAME = @LASTNAME or @LASTNAME Is Null)
and ...[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

zakeer
Starting Member

20 Posts

Posted - 2008-03-31 : 13:25:04

using this i m only getting records for the first name only ....try to solve my prob

Thanks & Regards
Zakeer

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-31 : 13:28:59

Nope it will give you desired result. Can you post full query used?

zakeer
Starting Member

20 Posts

Posted - 2008-03-31 : 13:31:06

select *from customer where (firstname=@firstname or @firstname is null) and
(lastname=@lastname or @lastname is null) and
.................

Thanks & Regards
Zakeer

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-31 : 13:34:09

and why do you think this wont work? It looks for each parameter if its present else move to next filter condition, which is what you're looking for i guess.

zakeer
Starting Member

20 Posts

Posted - 2008-03-31 : 13:37:31

ya ur right... its going to next condition but its not displayin anything...

i have already tried this ......

Thanks & Regards
Zakeer

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-31 : 13:40:38

can you provide some sample data and o/p you got?

zakeer
Starting Member

20 Posts

Posted - 2008-03-31 : 14:02:14

CREATE TABLE CUSTOMER
(
cCNO CHAR(4),
vCFNAME VARCHAR(20),
vCLNAME VARCHAR(20),
vCADD VARCHAR(20),
vCCITY VARCHAR(15)
)

INSERT INTO CUSTOMER
SELECT 'A001','ZAKEER','BASHA','HSR LAYOUT','CHENNAI'
UNION ALL
SELECT 'A002','JAKEER','BASHA','AGARA','BANGALORE'
UNION ALL
SELECT 'A003','PRAVEEN','KUMAR','BELLANDUR','PUNE'
UNION ALL
SELECT 'A004','ZAKEER','BASHA','HSR LAYOUT','BANGALORE'
UNION ALL
SELECT 'A005','JAKEER','BASHA','AGARA','CHENNAI'

SELECT *fROM CUSTOMER

CREATE PROC PRCCUSTOMER
(
@CNO CHAR(4)=NULL,
@CFNAME VARCHAR(20)=NULL,
@CLNAME VARCHAR(20)=NULL,
@CADD VARCHAR(20)=NULL,
@CCITY VARCHAR(20)=NULL
)
AS
BEGIN
SELECT *FROM CUSTOMER
WHERE
(cCNO=@CNO OR @CNO IS NULL)
AND
(vCFNAME=@CFNAME OR @CFNAME IS NULL)
AND
(vCLNAME=@CLNAME OR @CLNAME IS NULL)
AND
(vCADD=@CADD OR @CADD IS NULL)
AND
(vCCITY=@CCITY OR @CCITY IS NULL)

END

--------------------------------

IF ILL EXECUTE LIKE THIS

EXEC PRCCUSTOMER 'A001'

(1 row(s) affected)

A001 ZAKEER BASHA HSR LAYOUT CHENNAI

IF ILL TRY TO GET FIRSTNAME

EXEC PRCCUSTOMER 'ZAKEER'

(0 row(s) affected)

SAME FOR THE REST OF ALL

Thanks & Regards
Zakeer

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-31 : 14:06:25

That is a paramter issue, here is an article about how to pass parameters to a stored procedure: http://www.informit.com/articles/article.aspx?p=25288&seqNum=9

zakeer
Starting Member

20 Posts

Posted - 2008-03-31 : 14:18:40

BUT THE USER HOW DID HE KNOWS THE PARAMETER NAMES...

HE WILL SIMPLY GIVES THE CITY NAME OR FIRSTNAME...

Thanks & Regards
Zakeer

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-01 : 05:41:11

And how SQL Server is supposed to understand that 'A001' is CustomerID and 'ZAKEER' is firstname?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-04-01 : 05:50:01

You could give them a template,something like,

EXEC PRCCUSTOMER @CNO =NULL,@CFNAME VARCHAR(20)=NULL,@CLNAME VARCHAR(20)=NULL,@CADDVARCHAR(20)=NULL,
@CCITY VARCHAR(20)=NULL

Ask them to fill for the values they want to instead of null themselves

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-01 : 05:55:14

CREATE PROC PRCCUSTOMER
(
@search VARCHAR(20)=NULL
)
AS
BEGIN
SELECT *
FROM CUSTOMER
WHERE
@search in (cCNO,vCFNAME,vCLNAME,vCADD,vCCITY)

END

Em

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-01 : 06:02:39

What about all Zakeers in Stoke City?

EXEC PRCCUSTOMER NULL, 'ZAKEER'
EXEC PRCCUSTOMER DEFAULT, 'ZAKEER'

E 12°55'05.25"
N 56°04'39.16"

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-01 : 06:05:51