John W. Colby
jcolby at colbyconsulting.com
Wed Feb 26 22:53:15 CST 2003
Yes, that worked. It also works passing in a value from the form (view one record) or passing null (view all records). What is it doing? I looked up IsNull so I understand the concept but why does tblTitle.TTL_ID act as a wildcard so to speak. Pretty cool anyway. This definitely gets me a long way. Thanks, John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: dba-sqlserver-admin at databaseadvisors.com [mailto:dba-sqlserver-admin at databaseadvisors.com]On Behalf Of Mark Walker Sent: Wednesday, February 26, 2003 11:07 PM To: dba-sqlserver at databaseadvisors.com Subject: RE: [dba-SQLServer]Passing params to SProc Will this work for you? Alter Procedure qlfrmTitle (@TI_ID Int = null) As SELECT * FROM dbo.tblTitle WHERE (((tblTitle.TTL_ID) = IsNull(@TI_ID,tblTitle.TTL_ID))) ORDER BY tblTitle.TTL_Title return -----Original Message----- From: dba-sqlserver-admin at databaseadvisors.com [mailto:dba-sqlserver-admin at databaseadvisors.com]On Behalf Of John W. Colby Sent: Thursday, 27 February 2003 4:48 p.m. 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? _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com ---------------------------------------------------- Is email taking over your day? Manage your time with eMailBoss. Try it free! http://www.eMailBoss.com -------------- next part -------------- A non-text attachment was scrubbed... Name: winmail.dat Type: application/ms-tnef Size: 2804 bytes Desc: not available URL: <http://databaseadvisors.com/pipermail/dba-sqlserver/attachments/20030226/10d580ff/attachment.bin>