Better Way? Stored Proc - 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 2000 Forums

SQL Server Development (2000)

Better Way? Stored Proc

Author

Topic

rme8494
Yak Posting Veteran

98 Posts

Posted - 2004-06-11 : 10:40:52

Hi Everyone! I was wondering if there was a better way of codeing the Stored Proc below. The only difference in the two queries is the where statement. I'm using this proc as a search so depending on what the user chooses to search on a different query will run.

Thanks!

IF @SearchField = 'DocPostDate'
BEGIN
SELECT TC.MAC,TS.AccountNum,C.CertificateType,
FROM TE_DCTaxCertState TS INNER JOIN
TE_DCTaxCert TC ON TS.Doc_ID = TC.Doc_ID INNER JOIN
TECertificate C ON TC.Type_ID = C.Type_ID
WHERE TS.PostDate >= @StartDate AND
TS.PostDate <= @EndDate

SET @resultset = 3
END

ELSE IF @SearchField = 'ReceivedDate'
BEGIN
SELECT TC.MAC,TS.AccountNum,C.CertificateType,
FROM TE_DCTaxCertState TS INNER JOIN
TE_DCTaxCert TC ON TS.Doc_ID = TC.Doc_ID INNER JOIN
TECertificate C ON TC.Type_ID = C.Type_ID
WHERE TS.ReceivedDate >= @StartDate AND
TS.ReceivedDate <= @EndDate

SET @resultset = 3
END
GO

Ryan Everhart
SBC

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-06-11 : 10:44:09

1 Change just a matter of style

WHERE TS.PostDate >= @StartDate AND
TS.PostDate <= @EndDate

WHERE TS.PostDate Between @StartDate AND @EndDate

Jim
Users <> Logic

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-06-11 : 10:46:52

The only other way I can think of is to have the comparison in the WHERE clause, thus..
SELECT....
FROM....
WHERE (@SearchField = 'DocPostDate' AND TS.PostDate >= @StartDate AND TS.PostDate <= @EndDate)
OR (@SearchField = 'ReceivedDate' AND TS.ReceivedDate >= @StartDate AND TS.ReceivedDate <= @EndDate)

rme8494
Yak Posting Veteran

98 Posts

Posted - 2004-06-11 : 10:47:20

Jim,
Thanks for that! Is there a way to put an IF or CASE statement around the where statement so I don't have to repeate the bulk of the query?

I will make the BETWEEN change I had forgotten about that.

Ryan

Ryan Everhart
SBC

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-06-11 : 11:04:06

Use a CASE like this in the WHERE clause

...
WHERE CASE @SearchField WHEN 'DocPostDate' THEN TS.PostDate ELSE TS.ReceivedDate END BETWEEN @StartDate AND @EndDate
...

Raymond

rme8494
Yak Posting Veteran

98 Posts

Posted - 2004-06-11 : 12:04:25

Thank you Raymond.. that is exactly what I was looking for!

Rayn

Ryan Everhart
SBC

rme8494
Yak Posting Veteran

98 Posts

Posted - 2004-06-11 : 13:26:07

Raymond,
I'm trying to work you solution into another query of mine and am getting an error. The query and error are below. Any thoughts? The query works for MAC but not for CustomerName. I'm trying to search for 'pete%'.

SELECT C.MAC,C.CustomerName,C.NoteInformation,C.PostBy,C.PostDate
FROM TE_DCCustomer C
-- dynamic where statement
WHERE CASE @SearchField
WHEN 'MAC' THEN C.MAC
WHEN 'CustomerName' THEN C.CustomerName
END LIKE @SearchString
-- end dynamic where statement
ORDER BY C.MAC

Error
Server: Msg 245, Level 16, State 1, Line 10
Syntax error converting the varchar value 'PETERBUILT' to a column of data type int.

Ryan Everhart
SBC

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-11 : 13:28:43

Could you show us the table structure for TE_DCCustomer?

Tara

rme8494
Yak Posting Veteran

98 Posts

Posted - 2004-06-11 : 13:30:18

Thanks Tara

CREATE TABLE [TE_DCCustomer] (
[MAC] [int] IDENTITY (550000, 1) NOT NULL ,
[Category_ID] [int] NULL ,
[CustomerName] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NoteInformation] [varchar] (5000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PostBy] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PostDate] [datetime] NULL CONSTRAINT [DF_TE_DCCustomer_PostDate] DEFAULT (getdate()),
CONSTRAINT [PK_TE_DCCustomer] PRIMARY KEY CLUSTERED
(
[MAC]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

Ryan Everhart
SBC

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-11 : 13:34:44

Since MAC is an INT, you have to CONVERT it:

SELECT C.MAC,C.CustomerName,C.NoteInformation,C.PostBy,C.PostDate
FROM TE_DCCustomer C
-- dynamic where statement
WHERE CASE @SearchField
WHEN 'MAC' THEN CONVERT(VARCHAR(50), C.MAC)
WHEN 'CustomerName' THEN C.CustomerName
END LIKE @SearchString
-- end dynamic where statement
ORDER BY C.MAC

It's easiest to use VARCHAR for the data types.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-11 : 13:35:18

Here's the code I used:



SET NOCOUNT ON

CREATE TABLE [TE_DCCustomer] (
[MAC] [int] IDENTITY (550000, 1) NOT NULL ,
[Category_ID] [int] NULL ,
[CustomerName] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NoteInformation] [varchar] (5000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PostBy] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PostDate] [datetime] NULL CONSTRAINT [DF_TE_DCCustomer_PostDate] DEFAULT (getdate()),
CONSTRAINT [PK_TE_DCCustomer] PRIMARY KEY CLUSTERED 
(
[MAC]
) WITH FILLFACTOR = 90 ON [PRIMARY] 
) ON [PRIMARY]
GO

DECLARE @SearchField SYSNAME
DECLARE @SearchString VARCHAR(50)

SET @SearchField = 'MAC'
SET @SearchString = '550000'

INSERT INTO TE_DCCustomer (Category_ID, CustomerName, NoteInformation, PostBy, PostDate)
SELECT 1, 'Tara', 'Test1', 'Tara', GETDATE()
UNION ALL
SELECT 2, 'Tara', 'Test2', 'Tara', GETDATE()
UNION ALL
SELECT 1, 'Tara', 'Test3', 'Tara', GETDATE()
UNION ALL
SELECT 3, 'Tara', 'Test4', 'Tara', GETDATE()
UNION ALL
SELECT 4, 'Tara', 'Test5', 'Tara', GETDATE()
UNION ALL
SELECT 66, 'Tara', 'Test6', 'Tara', GETDATE()
UNION ALL
SELECT 9, 'Tara', 'Test7', 'Tara', GETDATE()
UNION ALL
SELECT 1, 'Tara', 'Test8', 'Tara', GETDATE()


SELECT C.MAC,C.CustomerName,C.NoteInformation,C.PostBy,C.PostDate
FROM TE_DCCustomer C
-- dynamic where statement
WHERE CASE @SearchField 
WHEN 'MAC' THEN CONVERT(VARCHAR(50), C.MAC)
WHEN 'CustomerName' THEN C.CustomerName
END LIKE @SearchString
-- end dynamic where statement
ORDER BY C.MAC

DROP TABLE TE_DCCustomer

Tara

rme8494
Yak Posting Veteran

98 Posts

Posted - 2004-06-11 : 14:24:54

Tara,
Thanks for the help, that worked. Can you tell me why putting a CONVERT function on the MAC where statement fixed this issue?

Ryan

Ryan Everhart
SBC

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-11 : 15:54:41

It's because the data types for the columns must be the same (or I believe implicitly converted) in a CASE statement. You had INT and VARCHAR, so we had to convert the INT to VARCHAR since you can't convert a customer name to INT.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources