[AccessD] [dba-SQLServer]Passing params to SProc

John,

This should solve you task assuming that you never have Ids equal to 0
(template was posted by Arthur recently AFAIKR):

Alter Procedure qlfrmTitle (@TI_ID Int = 0)
 As
 SELECT * FROM dbo.tblTitle
 WHERE
(tblTitle.TTL_ID = @TI_ID or @TI_ID = 0 )
ORDER BY  tblTitle.TTL_Title;
 return

HTH,
Shamil

----- Original Message -----
From: "John W. Colby" <jcolby at colbyconsulting.com>
To: <accessd at databaseadvisors.com>
Sent: Thursday, February 27, 2003 7:07 AM
Subject: RE: [AccessD] [dba-SQLServer]Passing params to SProc


> Let me guess.  I have to use two sprocs, one unfiltered (no param) and one
> with a param?
>
> 8-(
>
> John W. Colby
> Colby Consulting
> www.ColbyConsulting.com
>
> -----Original Message-----
> From: accessd-admin at databaseadvisors.com
> [mailto:accessd-admin at databaseadvisors.com]On Behalf Of John W. Colby
> Sent: Wednesday, February 26, 2003 10:48 PM
> To: AccessD-SQLServer
> Cc: AccessD
> Subject: [AccessD] [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
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>
>
> ----------------------------------------------------
> Is email taking over your day?  Manage your time with eMailBoss.
> Try it free!  http://www.eMailBoss.com
>