[dba-SQLServer]Passing params to SProc

Forget about LIKE, it doesn't apply to INT datatype.  Instead retain your version #3 CREATE part with parameter declaration, but have the body like this:

SELECT * FROM dbo.tblTitle WHERE tblTitle.TTL_ID = isnull(@TI_ID, tblTitle.TTL_ID) ORDER BY tblTitle.TTL_Title

______________________________________________________
Robert Djabarov
Certified MS SQL Server DBA
Certified MS SQL Server Programmer
Certified MS VB Programmer
? (210) 913-3148 - phone
( (210) 753-3148 - pager

 -----Original Message-----
From: 	John W. Colby [mailto:jcolby at colbyconsulting.com] 
Sent:	Wednesday, February 26, 2003 9:48 PM
To:	AccessD-SQLServer
Cc:	AccessD
Subject:	[dba-SQLServer]Passing params to SProc

I use autonumber PKs or whatever that was turned into in SQL during the
upsize.  So now I want to have an sproc with a param of type int, but have
the default value be the wildcard so that all records are passed back if no
param is provided.  Further I want to be able to pass in the wildcard in the
Input Parameters property of the form to load all the records if I have
previously narrowed it down.

Alter Procedure qlfrmTitle (@TI_ID Int = 0)

As

SELECT * FROM dbo.tblTitle WHERE (((tblTitle.TTL_ID) like @TI_ID)) ORDER BY
tblTitle.TTL_Title;

	return

Works to return no records.  Good so far.

Alter Procedure qlfrmTitle (@TI_ID Int = 1)

As

SELECT * FROM dbo.tblTitle WHERE (((tblTitle.TTL_ID) like @TI_ID)) ORDER BY
tblTitle.TTL_Title;

	return

works to return one record with the pk of 1 if no param is provided by the
form.  Good so far.

Alter Procedure qlfrmTitle (@TI_ID Int = %)

As

SELECT * FROM dbo.tblTitle WHERE (((tblTitle.TTL_ID) like @TI_ID)) ORDER BY
tblTitle.TTL_Title;

	return

refuses to save.  Doesn't like the %.  Likewise with *, likewise with '%'
etc.  I think perhaps the wildcard character is a text wildcard.  Is there a
numeric wildcard symbol or something?

John W. Colby
Colby Consulting
www.ColbyConsulting.com

----------------------------------------------------
Is email taking over your day?  Manage your time with eMailBoss.
Try it free!  http://www.eMailBoss.com


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com