[dba-SQLServer]Passing params to SProc

John W. Colby jcolby at colbyconsulting.com
Wed Feb 26 21:48:15 CST 2003


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





More information about the dba-SQLServer mailing list