Dyanmic Condition in Stored Procedure? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

SQL Server Development (2000)

Dyanmic Condition in Stored Procedure?

Author

Topic

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-06 : 04:57:55

What i want to do is use a parameter that takes in a condition like "NOT LIKE" or "LIKE". Because i have 2 applications that uses the same kind of data and the only difference between the 2 sql statements is "LIKE" and "NOT LIKE".

Is there way around this? Below is my stored procedure for "NOT LIKE":

CREATE PROCEDURE dbo.SEARCHSALEITEM
(
    @BRAND VARCHAR(100),
    @ITEMID VARCHAR(100),
    @ITEMNAME VARCHAR(100)
)
AS
SELECT *
FROM MSTRITEM AS M JOIN ITEMIMAG AS I
ON I.ITEMID = M.ITEMID
WHERE (M.ADDTOSALEITEM=1) AND 
((M.BRAND LIKE '%' + @BRAND + '%') OR (M.ITEMNAME LIKE '%' + @ITEMNAME + '%'))
AND ',' + @ITEMID + ',' NOT LIKE '%,' + CONVERT(VARCHAR(20), M.ITEMID) + ',%'

GO

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-06 : 05:12:40

If you want to use the same procedure, add an input parameter which accept value as Like or Not like.


CREATE PROCEDURE dbo.SEARCHSALEITEM_TEST
(
    @BRAND VARCHAR(100),
    @ITEMID VARCHAR(100),
    @ITEMNAME VARCHAR(100),
    @LIKE VARCHAR(10)

)
AS
If @LIKE='LIKE'
	SELECT *
	FROM MSTRITEM AS M JOIN ITEMIMAG AS I
	ON I.ITEMID = M.ITEMID
	WHERE (M.ADDTOSALEITEM=1) AND 
	((M.BRAND LIKE '%' + @BRAND + '%') OR (M.ITEMNAME LIKE '%' + @ITEMNAME + '%'))
	AND ',' + @ITEMID + ',' LIKE '%,' + CONVERT(VARCHAR(20), M.ITEMID) + ',%'

else If @LIKE='NOT LIKE'
	SELECT *
	FROM MSTRITEM AS M JOIN ITEMIMAG AS I
	ON I.ITEMID = M.ITEMID
	WHERE (M.ADDTOSALEITEM=1) AND 
	((M.BRAND LIKE '%' + @BRAND + '%') OR (M.ITEMNAME LIKE '%' + @ITEMNAME + '%'))
	AND ',' + @ITEMID + ',' NOT LIKE '%,' + CONVERT(VARCHAR(20), M.ITEMID) + ',%'
GO

Madhivanan

Failing to plan is Planning to fail

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-06 : 06:33:07

It works fine! Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources