parameter with LIKE - 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)

parameter with LIKE

Author

Topic

chriztoph
Posting Yak Master

184 Posts

Posted - 2009-06-22 : 02:54:17

Hi to all,

i have this stored proc and i want to
pass a parameter like this:


ALTER PROCEDURE [dbo].[prcRptInvoicePerCurrency]
	@AcctgPeriod INT,
	@ReportCurrencyID  INT,
	@OfficeID    INT,
	@DateFrom	 DATETIME,
	@DateTo		 DATETIME
AS
BEGIN
	SELECT * FROM vwGetRptInvoiceRegister
	WHERE InvoiceDate BETWEEN @DateFrom AND @DateTo
	AND ReportCurrencyID = @ReportCurrencyID
	AND AcctgPeriodShortDesc = @AcctgPeriod
	AND OfficeID LIKE '%@OfficeID%'
	ORDER BY CustomerName, InvoiceDate
END

the line with RED color that i want to apply..

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-22 : 03:01:53

change @officeid as varchar field and cast officeid as varchar field

ex :-
declare @id varchar(32)
select @id = '1,2,5'
select empid,empname from emptable where '%,' + @id + ',%' LIKE '%,' + CAST( empid AS VARCHAR(255)) +',%'

or use patindex also...

chriztoph
Posting Yak Master

184 Posts

Posted - 2009-06-22 : 03:10:06

i have got an error

Convertion failed when converting the '%,' to datatype int.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-22 : 03:12:34

use convert or cast function to convert int value to varchar field
see example given to u empid is integer field in given query

chriztoph
Posting Yak Master

184 Posts

Posted - 2009-06-22 : 03:21:06

i tried it but there is no output..


ALTER PROCEDURE [dbo].[prcRptInvoicePerCurrency]
	@AcctgPeriod INT,
	@ReportCurrencyID  INT,
	@OfficeID    INT,
	@DateFrom	 DATETIME,
	@DateTo		 DATETIME
AS
BEGIN
	SELECT * FROM vwGetRptInvoiceRegister
	WHERE InvoiceDate BETWEEN @DateFrom AND @DateTo
	AND ReportCurrencyID = @ReportCurrencyID
	AND AcctgPeriodShortDesc = @AcctgPeriod
	AND OfficeID LIKE CAST(@OfficeID AS VARCHAR(255) + '%'
	ORDER BY CustomerName, InvoiceDate
END

what i want is even the '@Office' is null it should be show all Office

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-06-22 : 03:22:46

Hi try like this

ALTER PROCEDURE [dbo].[prcRptInvoicePerCurrency]
@AcctgPeriod INT,
@ReportCurrencyID INT,
@OfficeID INT,
@DateFrom DATETIME,
@DateTo DATETIME
AS
BEGIN
SELECT * FROM vwGetRptInvoiceRegister
WHERE InvoiceDate BETWEEN @DateFrom AND @DateTo
AND ReportCurrencyID = @ReportCurrencyID
AND AcctgPeriodShortDesc = @AcctgPeriod
AND patindex('%,'+cast(OfficeID as varchar(10))+',%' , ','+@OfficeID+',') > 0
ORDER BY CustomerName, InvoiceDate
END

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-22 : 03:26:06

[code]
ALTER PROCEDURE [dbo].[prcRptInvoicePerCurrency]
@AcctgPeriod INT,
@ReportCurrencyID INT,
@OfficeID INT = NULL,
@DateFrom DATETIME,
@DateTo DATETIME
AS
BEGIN
SELECT * FROM vwGetRptInvoiceRegister
WHERE InvoiceDate BETWEEN @DateFrom AND @DateTo
AND ReportCurrencyID = @ReportCurrencyID
AND AcctgPeriodShortDesc = @AcctgPeriod
AND (@OfficeID IS NULL OR OfficeID LIKE CAST(@OfficeID AS VARCHAR(255) + '%')
ORDER BY CustomerName, InvoiceDate
END
[/code]

if officeid in table structure is varchar above will work
if it is int then use this
(@OfficeID IS NULL OR '%,' + CAST(@OfficeID AS VARCHAR(255) + ',%' LIKE '%,' + CAST( officeid AS VARCHAR(255)) +',%'
[/code]

chriztoph
Posting Yak Master

184 Posts

Posted - 2009-06-22 : 03:26:36

got an error:

Msg 8116, Level 16, State 1, Procedure prcRptInvoicePerCurrency, Line 20
Argument data type int is invalid for argument 2 of patindex function.

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-06-22 : 03:29:02

quote:
Originally posted by chriztoph

got an error:

Msg 8116, Level 16, State 1, Procedure prcRptInvoicePerCurrency, Line 20
Argument data type int is invalid for argument 2 of patindex function.

ALTER PROCEDURE [dbo].[prcRptInvoicePerCurrency]
@AcctgPeriod INT,
@ReportCurrencyID INT,
@OfficeID INT,
@DateFrom DATETIME,
@DateTo DATETIME
AS
BEGIN
SELECT * FROM vwGetRptInvoiceRegister
WHERE InvoiceDate BETWEEN @DateFrom AND @DateTo
AND ReportCurrencyID = @ReportCurrencyID
AND AcctgPeriodShortDesc = @AcctgPeriod
AND patindex('%,'+cast(OfficeID as varchar(10))+',%' ,','+ cast(@OfficeID as varchar(10))+',') > 0
ORDER BY CustomerName, InvoiceDate
END

chriztoph
Posting Yak Master

184 Posts

Posted - 2009-06-22 : 03:39:22

Thanks for helping Guys..it works in my Report..i think my back end code(C#) is the problem..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-22 : 04:22:48

Until you pass csv you need only

OfficeID LIKE '%'+cast(@OfficeID as varchar(10))+'%'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources