Procedure with variables Null should be all

• douglascfast

  Right there with Babe

  Points: 776

  More actions

  January 13, 2010 at 9:33 am

  #140709

  Hey all,

  Here is some code which I got from a news group. Hari N Sharma posted or is given credit for it.

  Thanks Hari. Anyways I changed it a bit to meet my needs, and that code is posted below:

  So I want to make a procedure, and want to be able to say what schema I want (ie DF) or leave it null or someother code and then it will return all the rows not just my schema.

  I don't seem to get it work. Have tried several combinations from other bits of logic I have found on the web but no go.

  Can someone see the issue?

  Thanks in advance.

  Doug

  Declare @var Varchar(10)

  SET @Var = 'DF'

  SELECT

  LEFT(USER_NAME(o.uid),10) AS Schema_Owner

  , LEFT(OBJECT_NAME(i.id),50) AS TableName

  , i.rowcnt Row_Count

  , CONVERT(numeric(15,2),(((CONVERT(numeric(15,2), i.reserved)) * 8192 / 1024.0))) AS Reserved_Size_KB

  , CONVERT(numeric(15,2),(((CONVERT(numeric(15,2), i.used)) * 8192 / 1024.0))) AS Used_Size_KB

  , i.dpages

  , o.refdate

  FROM sysindexes i

  INNER JOIN sysobjects o (NOLOCK)

  ON i.id = o.id

  WHERE (o.uid like CASE

  WHEN @Var is not null THEN @VAR

  ELSE '%' End)

  AND indid IN (0, 1, 255)

  and o.type = 'U'

• R Michael

  SSCarpal Tunnel

  Points: 4211

  More actions

  January 13, 2010 at 11:14 am

  #1103212

  This seems to work:

  Declare @var Varchar(10)

  SET @Var = null

  SELECT

  LEFT(USER_NAME(o.uid),10) AS Schema_Owner

  , LEFT(OBJECT_NAME(i.id),50) AS TableName

  , i.rowcnt Row_Count

  , CONVERT(numeric(15,2),(((CONVERT(numeric(15,2), i.reserved)) * 8192 / 1024.0))) AS Reserved_Size_KB

  , CONVERT(numeric(15,2),(((CONVERT(numeric(15,2), i.used)) * 8192 / 1024.0))) AS Used_Size_KB

  , i.dpages

  , o.refdate

  FROM sysindexes i

  INNER JOIN sysobjects o (NOLOCK)

  ON i.id = o.id

  WHERE o.uid LIKE COALESCE(@var,'%')

  AND indid IN (0, 1, 255)

  and o.type = 'U'

  Although it is not advisable to query against the systables. I would research the alternatives (information_schema, sys. etc)

  hope this helps

  SQL guy and Houston Magician

• douglascfast

  Right there with Babe

  Points: 776

  More actions

  January 13, 2010 at 1:16 pm

  #1103272

  Thanks for the help.

  🙂

• ChrisM@home

  SSC-Insane

  Points: 24260

  More actions

  January 13, 2010 at 2:28 pm

  #1103331

  The LIKE is not necessary.

  WHERE o.uid = ISNULL(@Var, o.uid)

  AND indid IN (0, 1, 255)

  AND o.type = 'U'

  ---

  [font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

  For better assistance in answering your questions, please read this[/url].

  ---

  Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

  Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]