[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?
> 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: winmail.dat
Type: application/ms-tnef
Size: 3112 bytes
Desc: not available
URL: <http://databaseadvisors.com/pipermail/dba-sqlserver/attachments/20030227/20f9738b/attachment.bin>