Empty parameters in SProc - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Empty parameters in SProc

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-08-07 : 09:30:05

Stan Sainte-Rose writes "First, sorry for my ignorance but I m very new in stored procedures.
I have several parameters to send to a sproc. Some of them can be empty.
When I trace the query, I have something like sp_listproduits 1,5,,, and I
get an error

So, I add '' between the ,
But now, I don't know how to convert the '' into Null when they are sent to
the sproc.
Some of them are int, date and varchar

The second problem, is to know how to use a Like statement in a sproc with
the %
Example :
produits.libelle = IsNull(@motsclefs,produits.libelle)
How to do this
produits.libelle Like IsNull(@motsclefs,produits.libelle)
In my current script in the ASP, I used
... where libelle like '%" & myVar & "%'"

Here's my stored proc..

Thanks for your help...

CREATE PROCEDURE sp_listeproduit
     (
       @Page int,
       @RecsParPage int,
       @Categorie int =null ,
       @Commune int =null,
       @MotsClefs varchar(50)  = null
     )
AS
SET NOCOUNT ON
Create Table #TempProduits
(
  ID int IDENTITY,
  Idprod Int,
  idcat int,
  libelle varchar(50),
)

Insert Into #TempProduits (idprod,idcat,,libelle)
Select produits.idprod,produits.idcat,produits.libelle From Produits Inner
Join Societes On societes.idsoc=produits.idshop
Where produits.idcat = IsNull(@categorie,produits.idcat) And
           produits.libelle =  IsNull(@motsclefs,produits.libelle)  And
           societes.commune=IsNull(@commune,societes.commune) and
          idprod=idprod
Declare @FirstRec int, @LastRec int
Select @FirstRec = (@Page-1) * @RecsParPage
Select @LastRec = (@Page * @RecsParPage +1)

Select *,
   MoreRecords =
    (
      Select Count(*)
      From #TempProduits TI
      Where TI.ID>=@LastRec
   )
From #TempProduits
Where ID>@FirstRec and ID<@LastRec

Set NoCount  OFF"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources