John W. Colby
jcolby at colbyconsulting.com
Thu Feb 27 05:55:27 CST 2003
Ahhhhh.... (slaps head with hand) John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: dba-sqlserver-admin at databaseadvisors.com [mailto:dba-sqlserver-admin at databaseadvisors.com] Sent: Thursday, February 27, 2003 12:11 AM To: dba-sqlserver at databaseadvisors.com Subject: RE: [dba-SQLServer]Passing params to SProc OK, you have: WHERE tblTitle.TTL_ID =IsNull(@TI_ID,tblTitle.TTL_ID) So... if the parameter is NULL then effectively the query is: WHERE tblTitle.TTL_ID =tblTitle.TTL_ID And this where clause is always true of course. Actually I think the SQL Server engine optimizes this away so effectively there is no where clause if the parameter is NULL (but don't quote me on that ;-) ) HTH Mark -----Original Message----- From: dba-sqlserver-admin at databaseadvisors.com [mailto:dba-sqlserver-admin at databaseadvisors.com] Sent: Thursday, 27 February 2003 5:53 p.m. To: dba-sqlserver at databaseadvisors.com Subject: RE: [dba-SQLServer]Passing params to SProc 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? ---------------------------------------------------- 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: 3096 bytes Desc: not available URL: <http://databaseadvisors.com/pipermail/dba-sqlserver/attachments/20030227/4b38266f/attachment.bin>