Using variables in SQL Server stored procedure

Question

I created a stored procedure in the master database because I want to be able to run on various databases.

I created a variable for database so when I execute the stored procedure it will run on the one I want. I keep getting this syntax error:

Msg 102, Level 15, State 1, Procedure Stuck_Docs_WF_Rpt, Line 12
Incorrect syntax near '.'.

Here is my code:

ALTER PROCEDURE [dbo].[Stuck_Docs_WF_Rpt] 
   @Database char(25)
AS
BEGIN
   select 
      count(@Database.hsi.itemdata.itemnum) as 'Doc(s) Stuck',
      @Database.hsi.lcstate.statename as 'Queue', 
      @Database.hsi.lifecycle.lifecyclename as 'Lifecycle'
   from 
      @Database.hsi.itemdata
   join 
      @Database.hsi.itemlc on @Database.hsi.itemdata.itemnum = @Database.hsi.itemlc.itemnum
   join 
      @Database.hsi.lcstate on @Database.hsi.itemlc.statenum = 
@Database.hsi.lcstate.statenum
   join 
      @Database.hsi.lifecycle on @Database.hsi.itemlc.lcnum = @Database.hsi.lifecycle.lcnum
   where
       @Database.hsi.itemdata.itemnum = @Database.hsi.itemlc.itemnum 
       and @Database.hsi.lcstate.statenum = @Database.hsi.itemlc.statenum
       and @Database.hsi.lcstate.statename Like '%Route' or @Database.hsi.lcstate.statename like '%Initial'
       and @Database.hsi.itemlc.status = '0'
       and DateDiff([Day], @Database.hsi.itemlc.transdate, getDate()) >=1
    group by @Database.hsi.lifecycle.lifecyclename, @Database.hsi.lcstate.statename
END

You cannot use a variable for an object name. You'll need to use dynamic sql to pull this off. — Joe Stefanelli
– Joe Stefanelli, Commented Sep 27, 2012 at 15:08
Depending on how many databases you can use. If its only 2 or 3 I would think about using a case. For many different databases you would have to use dynamic SQL. — Fred
– Fred, Commented Sep 27, 2012 at 15:13
Specifically I would guess that your syntax error is occurring at the very first period in the line "count(@Database.hsi.itemdata.itemnum) as 'Doc(s) Stuck'" and you'll get the same syntax error at every period following @Database... in the rest of the script as well. — user74754
– user74754, Commented Sep 27, 2012 at 15:30

Vinnie · Accepted Answer · 2012-09-27 15:53:17Z

1

You were already heading towards Dynamic Sql. I used your same proc and made it dynamic.

Also, I replaced the @Database variable with varchar instead of char in case you used a database name that was less than 25 characters.

alter PROCEDURE [dbo].[Stuck_Docs_WF_Rpt] 
   @Database varchar(25)
AS
DECLARE @sql varchar(max)

   SELECT @sql = 'select 
      count('+@Database+'.hsi.itemdata.itemnum) as ''Doc(s) Stuck'',
      '+@Database+'.hsi.lcstate.statename as ''Queue'', 
      '+@Database+'.hsi.lifecycle.lifecyclename as ''Lifecycle''
   from 
      '+@Database+'.hsi.itemdata
   join 
      '+@Database+'.hsi.itemlc on '+@Database+'.hsi.itemdata.itemnum = '+@Database+'.hsi.itemlc.itemnum
   join 
      '+@Database+'.hsi.lcstate on '+@Database+'.hsi.itemlc.statenum = 
'+@Database+'.hsi.lcstate.statenum
   join 
      '+@Database+'.hsi.lifecycle on '+@Database+'.hsi.itemlc.lcnum = '+@Database+'.hsi.lifecycle.lcnum
   where
       '+@Database+'.hsi.itemdata.itemnum = '+@Database+'.hsi.itemlc.itemnum 
       and '+@Database+'.hsi.lcstate.statenum = '+@Database+'.hsi.itemlc.statenum
       and '+@Database+'.hsi.lcstate.statename Like ''%Route'' or '+@Database+'.hsi.lcstate.statename like ''%Initial''
       and '+@Database+'.hsi.itemlc.status = ''0''
       and DateDiff([Day], '+@Database+'.hsi.itemlc.transdate, getDate()) >=1
    group by '+@Database+'.hsi.lifecycle.lifecyclename, '+@Database+'.hsi.lcstate.statename'

    print(@sql)
    --exec(@sql)

edited Sep 27, 2012 at 15:53

answered Sep 27, 2012 at 15:48

Vinnie

3,9471 gold badge28 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vinnie Over a year ago

I missed a period after the @database variable in the original post. Fixed it.

Scott Over a year ago

Thanks! got it to work just had to change the last line from print(sql) to execute(@sql)

Collectives™ on Stack Overflow

Using variables in SQL Server stored procedure

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related