Optional values in a Stored Proc

[Bill Willyerd]

The following SP gives an error of:
Server: Msg 245, Level 16, State 1, Procedure spSelectSEICData, Line
26
Syntax error converting the varchar value '@' to a column of data type
int.

In the Procedure I am using the Select * for testing purposes.
Here is the proc.
CREATE PROCEDURE spSelectSEICData
(
@IndivNo int,
@CommType SmallInt,
@BeginDate as SmallDateTime
)
AS
Declare @SqlStr as char(1)
Set @SqlStr = ''
If ((@BeginDate <> ' ') and (@CommType <> ' '))
Begin
Set @SqlStr = '@IndivNo AND [SEIC-COMMENT-TYPE] = @CommType'
End
If ((@BeginDate <> ' ') and (@CommType = ' '))
Begin
Set @SqlStr = '@IndivNo AND [SEIC-COMMENT-DATE-MCYMD] =
@BeginDate'
End
If ((@BeginDate = ' ') and (@CommType <> ' '))
Begin
Set @SqlStr = '@IndivNo AND [SEIC-COMMENT-DATE-MCYMD] = @BeginDate
AND[SEIC-COMMENT-TYPE] = @CommType '
End
If ((@BeginDate = ' ') and (@CommType = ' '))
Begin
Set @SQlStr = '@IndivNo '
End

SELECT *
FROM SEIC
WHERE [SEIC-INDIVIDUAL-NO] = @SqlStr

GO

The optional values are the @CommType and the @BeginDate. Where did I
go wrong or is there a better way of doing this?
Thanks in advance
Bill

[Erland Sommarskog]

Bill Willyerd (bw*******@dshs.wa.gov) writes:

The following SP gives an error of:
Server: Msg 245, Level 16, State 1, Procedure spSelectSEICData, Line
26
Syntax error converting the varchar value '@' to a column of data type
int.

In the Procedure I am using the Select * for testing purposes.
Here is the proc.
CREATE PROCEDURE spSelectSEICData
(
@IndivNo int,
@CommType SmallInt,
@BeginDate as SmallDateTime
)
AS
Declare @SqlStr as char(1)
To be char(1), you are cramming a lot of characters into it.
SELECT *
FROM SEIC
WHERE [SEIC-INDIVIDUAL-NO] = @SqlStr

So at this point @SqlStr has the value '@', and apparently the column
you are comparing it to is an integer column, whence the error.

Judging from your code, you seem to be building parts of an SQL
statement, but while it may be obvious to you, SQL Server is
completely without chance to understand what's going on.

See here for tips on how to implemnt these kind of searches. Since
this procedure is fairly simple with only three different conditions,
I would probably go for a static solution.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Bill Willyerd]

Thanks for pointing out that char(1). I had tried a CASE using single
characters for each possibility then switched to putting the SQL string
into the var (with out changing the datatype or size).
I did get it to work using If Else If conditional statments.

Thanks again
Bill

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!