Declaring Variables in stored proc

gvamsimba
gvamsimba used Ask the Experts™
on
HI ,i m a newbie to stored procedures..below is my test stored procedure which is giving me an error saying
"must declare table variable".. basically i just want those two tables to be dynamic as they keep chaniging
everymonth..so my procedure should change them dynamically..can anybody re-write this procedure for me..
Many Thanks..

CREATE PROCEDURE test
	
	(@mstable  varchar(50),
     @cellstable              varchar(50),
     @startdate               datetime ,
     @enddate                 datetime
                                         )
	
AS
 
 
 
BEGIN
 
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
set nocount on
 
	select cell,count(*) mailed
	from @mailingselectionstable
	where final_selection = 1
    and cell in (select cell from @cellstable)
	group by celll

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
hope this helps you
CREATE PROCEDURE test 
     @startdate               datetime ,
     @enddate                 datetime
AS
 
 
 
BEGIN
 
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
set nocount on
 
	-- declare your temp table or table variable..
	CREATE TABLE #mstable (
		cell	int,
		mailed	int,
		final_selection	int
		-- , .....
	);
	
	-- or
	
	DECLARE @cellstable TABLE (cell	int, mailed	int);
	
	-- if your table will hold too many data its better that you define a temp table instead of table variable.
	-- after declaring or creating your table or table variable you should reference them as any database objects.
	-- here are some examples...
	
	INSERT INTO #mstable
	SELECT cell, mailed
	FROM anyTable
	WHERE anyCondition
	
	
	INSERT INTO @cellstable
	SELECT cell,count(*) mailed
	FROM #mstable
	WHERE final_selection = 1
	GROUP BY cell
 
	SELECT * FROM @cellstable
	

Open in new window

hi dear,
you need to use the dynamic sql  to use dynamic table names in query
below is you corrected sp

Create PROCEDURE test
     
     (@mstable              varchar(50),
     @cellstable              varchar(50),
     @startdate               char(1) ,
     @enddate                 char(1)
                                         )
       
AS
--declare a string variable to store query
declare @str varchar(100)

 
BEGIN
 
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
set nocount on
 
SET @str = 'select cell,count(*) mailed from ' + @mstable + 'where final_selection = 1 and cell in (select cell from '+ @cellstable + ')
        group by celll'

Execute(@str)

END
If I understand your original post correctly, you are wanting to be able to have your stored proc select from different tables that already exist rather than from temporary tables you are creating.  If that is true, then you are going to have to take a slightly different approach.
You cannot use a variable to provide the name of the table from which you are making the SELECTion, that you want to UPDATE, or that you want to DELTE from.  Instead, what you have to do is DECLARE a variable (I usually use SQL_Statement ;-) of type VarChar and create the SQL statement in that variable as a string (which means you can use the tablename variables as you are putting the string together) and then ExECUTE the string from that variable.
Of course, that means that your application will have to pass in the names of the tables as well as any other values that might be needed.

DECLARE @SQL_Statement VarChar(4000);
 
SET @SQL_Statement = 'SELECT whatever ' +
                    'FROM ' + @Table1 +
                    ' WHERE whatever;';
 
EXECUTE @SQL_Statement;

Open in new window

gvamsimbaIT Consultant

Author

Commented:
this format gave me the solution...i think there is no need to put '' for from before tablename..


set @str=select cell,count(*) mailed
      from '+@table1+'
      where final_selection = 1
    and cell in (select cell from  '+@table2+')
      group by cell
The reason that there may have appeared to be extra apostrophes (single-quotes) is that I had formatted the statement across multiple lines (thus the "+" at the end of each line) even though it would have wound up as one long single line. ;-)
 
Your posted code will fail, though.
set @str=select cell,count(*) mailed
from '+@table1+'
where final_selection = 1
and cell in (select cell from '+@table2+')
group by cell
You need the single quote to stat and end the string, as shown below:
set @str='select cell,count(*) mailed
from '+@table1+'
where final_selection = 1
and cell in (select cell from '+@table2+')
group by cell'
Also, you may wind up with either a bunch of spaces or a CRLF in the middle of your string between "mailed" and "from".  I recommend putting the closing single quote on each line before the line break, a "+" at the end of that line and then starting the next line with a single quote just befor the first character of the rest of the string.  (And, be sure to include a space at the end of each piece of the over all string so that the SQL statment comes out correctly).
Also, your SQL can be slightly improvedas shown in the code snippet:

set @str='select T1.cell, count(*) mailed ' +
         'from ' + @table1 + ' T1 ' +
         'INNER JOIN ' + +@table2 + ' T2 ' +
         '   ON T1.Cell = T2.Cell ' +
         'where final_selection = 1 ' +
         'group by cell';

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial